Home > Functions and DMVs, Insert, SQL Queries, SQL Server, Stored Procedures, System Stored Procs, T-SQL > Executing dynamic queries with more than 4000 characters using variables

Executing dynamic queries with more than 4000 characters using variables

September 4, 2009 Leave a comment Go to comments

Usually problem occurs when we are trying to build a query dynamically and if the length exceeds 4000 characters ( a variable of type nvarchar) or 8000 ( in case of varchar). The query stored in the variable gets truncated when it reaches the limit. One of the easiest way to overcome this is to split the query across few manageable number of variables. You can then execute the query/es by concatenating the variables. How ever do not use “SP_EXECUTESQL”  , as this will throw an error when you try to execute. SP_EXECUTESQL can execute a string, a variable but not when you concatenate variables.

that is Exec sp_executesql @var1+@var2 will throw an error.

You can use the EXEC( ) to execute these type of queries. All you need to do is EXEC( @var1 +@var2+@var3)

example:

Declare @var1 nvarchar(max), @var2 nvarchar(max),@var3 nvarchar(max);

Set @var1 = ‘Select  ‘;

Set @var2 = ‘ * From ‘;

Set @var3= ‘ mytable’;

exec (@var1+@var2+@var3);

Above query will return the records in the table.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: