ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决sql server - How to run a more than 8000 characters SQL statement from a variable?

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:SQL Server 2005 读取xml 文件 突破 varchar 8000 限制

SELECT * into #temp1 FROM OPENROWSET(    BULK D:\0700000015.xml ,SINGLE_CLOB  ) AS x DECLARE @hdoc int DECLARE @doc varchar(8000) select @doc=BulkColu

I can use the following code for tiny little queries:

DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT * FROM myTable'
Exec @sql

The above method is very useful in order to maintain large amounts of code, especially when we need to make changes once and have them reflected everywhere.

My problem is my query (it's only one single query) that I want to feed into the @sql variable uses more than 25 table joins, some of them on temporary table variables, incorporates complex operations and it is hence much more than 8000 characters long.

I wished to use TEXT data type to store this query, but MSDN shows a warning message that Microsoft is planning to remove Text, NText and Image data types from their next versions. I wish my code to run in future too.

I thought of storing this query in a separate file, but as it uses joins on table variables and other procedure-specific parameters, I doubt if this is possible.

Kindly tell me a method to store a large query into a variable and execute it multiple times in a procedure.

Thanks in advance!

sql sql-server sql-server-2008 tsql varchar
edited Jan 14 '14 at 9:05 asked Feb 7 '12 at 7:34 Rachcha 4,524 7 28 58      Why don't you create a Stored Procedure for that query? –  papaiatis Feb 7 '12 at 7:36      It's because that query has some local variables and temporary tables. Can't put the query in a separate procedure. –  Rachcha Feb 7 '12 at 8:27 1   I answered a similar question here: stackoverflow.com/a/17785175/555798 –  MikeTeeVee Jul 22 '13 at 10:30      Check this answer : link –  Bino Oct 27 '16 at 10:26

 | 

9 Answers
9

解决方法

If you are on SQL Server 2008 or newer you can use VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX)

answered Feb 7 '12 at 7:35 Andrea Colleoni 4,051 1 17 35      +1 Exec does allow varchar(max) –  Andomar Feb 7 '12 at 7:42      How many characters does MAX allow for VARCHAR? –  Rachcha Feb 7 '12 at 7:43      As I know it allows about 2GB. –  Andrea Colleoni Feb 7 '12 at 7:45      max indicates that the maximum storage size is 2^31-1 bytes msdn.microsoft.com/en-us/library/ms176089.aspx –  Thit Lwin Oo Feb 7 '12 at 7:46      Try to look at this answer: stackoverflow.com/questions/7392161/t-sql-varcharmax-truncat‌​ed –  Andrea Colleoni Feb 7 '12 at 8:30  |  show more comments

The problem is with implicit conversion.

If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to VarChar(8000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter!

When concatenating long strings (or strings that you feel could be long) always pre-concatenate your string building with CAST('' as nVarChar(MAX)) like so:

SET @Query = CAST('' as nVarChar(MAX))--Force implicit conversion to nVarChar(MAX)
           + 'SELECT...'-- some of the query gets set here
           + '...'-- more query gets added on, etc.

What a pain and scary to think this is just how SQL Server works. :(

I know other workarounds on the web say to break up your code into multiple SET/SELECT assignments using multiple variables, but this is unnecessary given the solution above.

For those who hit a 4000 character max, it was probably because you had Unicode so it was implicitly converted to nVarChar(4000).

Explanation:
What's happening behind the scenes is that even though the variable you are assigning to uses (MAX), SQL Server will evaluate the right-hand side of the value you are assigning first and default to nVarChar(4000) or VarChar(8000) (depending on what you're concatenating). After it is done figuring out the value (and after truncating it for you) it then converts it to (MAX) when assigning it to your variable, but by then it is too late.


answered Jul 22 '13 at 10:29 MikeTeeVee 8,284 2 42 35

 | 
DECLARE @sql VARCHAR(max)
SET @sql = 'SELECT * FROM myTable'
Exec @sql

Note:

Print(@sql)

only show the first 8000 characters!


answered May 23 '13 at 21:04 Xtian11 500 1 5 8      What's wrong with the previous answers? –  Rachcha May 24 '13 at 3:43

 | 

use

推荐:SQL Server 2008 的安装

               SQL Server 2008简体中文企业版下载(SQL2008)   SQL Server 2008分为SQL Server 2008企业版、标准版、工作组版、Web版、开发者版、Express版、Co

EXEC
(
  '
   --your sql script here
  '
)

answered Feb 7 '12 at 7:39 Thit Lwin Oo 2,562 1 13 19      Thanks for answer, Thit, but I can do this without Exec too." What I wish to do here is store this query into a variable and run it multiple times. –  Rachcha Feb 7 '12 at 7:42

 | 

Problem is because your string has limit 8000 symbols by default. To prevent this you should convert it to (N)VARCHAR(MAX)

DECLARE @sql VARCHAR(8000)
        SET @sql = CAST('SELECT * FROM myTable' AS VARCHAR(MAX))
--Check length of variable
 PRINT 'Length is: '+CAST(LEN(@sql) AS VARCHAR)+ 'symbols'
        Exec @sql

answered Feb 7 '12 at 11:42 Dalex 2,968 10 23

 | 

Well I ran to this before (in SQL 2005) and I can tell you that you have two options:

1 - Use the sys.sp_sqlexec stored procedure that can take a param of type text (IMO this is the way to go). Don't mind the warning. In SQL 2008 ntext is still supported, and if you do the varchar(max) thingy there, it will work. So basically, if you have 2008, both the text solution and the varchar(max) will work, so you will have time to change it =-). In 2012 though, only the varchar(max) will work, therefore you'll have to change it before upgrading.

2- (This is what I did at first) Check THIS post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274 and do what user "Kristen" says. Worked like a charm for me. Don't forget to pre-set them to an empty string. If you understood my post you know by now that in SQL 2008 or newer is silly to do this.


edited Feb 7 '12 at 12:49 answered Feb 7 '12 at 12:40 Gaspa79 1,485 2 14 32

 | 

There is no solution for this along the way that you are doing it. MsSql as of 2012 supports Ntext for example that allows you to go beyond 8000 characters in a variable. The way to solve this is to make multiple variables or multiple rows in a table that you can iterate through.

At best with a MsSql version the max size of a variable is 8000 characters on the latest version as of when this was typed. So if you are dealing with a string of say 80,000 characters. You can parse the data into ten variables of 8000 characters each (8000 x 10 = 80,000) or you can chop the variable into pieces and put it into a table say LongTable (Bigstring Varchar(8000)) insert 10 rows into this and use an Identity value so you can retrieve the data in the same order.

The method you are trying will not work with MsSql currently.

Another obscure option that will work but is not advisable is to store the variable in a text file by using command shell commands to read/write the file. Then you have space available to you beyond 8000 characters. This is slow and less secure than the other methods described above.


answered Feb 26 '16 at 1:28 RobPrell 11 1      missing from above Ntext can be used in a table but not in a variable, only in a table sorry I rush typed the above. –  RobPrell Feb 26 '16 at 1:29

 | 

You should read the answer of this post which explains extremely well the situation : SQL NVARCHAR and VARCHAR Limits

  1. If the length x of your string is below 4000 characters, a string will be transformed into nvarchar(x)
  2. If the length y is between 4000 and 8000, varchar(y)
  3. If the length is more than 8000 characters, nvarchar(max) which can store up to 2GB.

Problem is that nvarchar(max) + varchar(y) = nvarchar(max) + nvarchar(4000) ; SQL will convert your varchar(y) into nvarchar(y) or nvarchar(4000) if y is greater than 4000 and lesser than 8000, truncating your string !


edited Apr 5 '16 at 4:04 answered Apr 5 '16 at 3:51 BD01 96 7

 | 
ALTER PROCEDURE [dbo].[spGetEmails]
AS
BEGIN
    SET NOCOUNT ON;
    -- Insert statements for procedure here

    declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 10000000)

 insert @local values(cast(@p as varchar(max)))
 select   col from @local


END

edited Mar 23 at 10:02 Pang 5,623 14 48 81 answered Mar 23 at 8:41 Heta77 1

 | 

推荐:Sql Server 2008

创建函数: create function sector_pid(@id varchar(10)) returns @t_level table(id varchar(10)) as begin   insert into @t_level select @id   select @id 


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。