[thelist] MSSQL: dynamic SQL

Jason Handby jasonh at corestar.co.uk
Tue Jul 26 15:56:12 CDT 2005


Hi Brian,
 

> I just wrote a huge query with temp tables, updates and all.
> 
> I go to run it, and i'm getting syntax errors.  odd...  
> everything looks right on that line.
> 
> so, instead of exec(@query), I select @query
> 
> Turns out, its getting chopped.
> 
> @query is declared as varchar(8000).  it doesn't go any 
> higher than that.
> 
> What can i do?
> 

You can do the concatenation in the EXEC command, and then you should be
OK.


According to Books Online:

"Use the string concatenation operator (+) to create large strings for
dynamic execution. Each string expression can be a mixture of Unicode
and non-Unicode data types."

"Although each [N] 'tsql_string' or @string_variable must be less than
8,000 bytes, the concatenation is performed logically in the SQL Server
parser and never materializes in memory."


So you can build your long command in separate varchar variables, each
of which is <= 8000 bytes, and then do something like this:

	EXEC (@MyBigVarChar1 + @MyBigVarChar2 + @MyBigVarChar3)



HTH




Jason
 


More information about the thelist mailing list