How to sp_executesql with Dynamic SQL String Exceeding 4000

By | October 21, 2020

Usually, it is a typical issue that you will face when you are executing the Dynamic SQL, the length of the string which is really prodigious when compared to the max length of the string. To overcome this major issue. We have a small alternative. We have part of the execution string in one variable and remaining part in the other string.

EXEC /SP_EXECUTESQL WITH DYNAMIC SQL STRING EXCEEDING 4000

The case is related to the sp_executesql and the sql statement parameter in a dynamic SQL process. We have our SQL string exceeding more than 4000 characters. The sp_executesql wishes its parameters to be declared as nvarchar or ntext. However, the ntext cannot be declared for a local variable. While nvarchar has a maximum limit of 4000 characters. For this case, we tried this original approach:

DECLARE @sql nvarchar(4000),

select @sql=@sql+’select……………..’

INSERT INTO #Temp

EXEC sp_executesql @SQL,@paramlist

Apparently, the above sql was split into two sql variables. Then, it tried in the following way.

DECLARE @sql nvarchar(4000), @sql1 nvarchar(4000),@paramlist nvarchar(4000)

select @sql=@sql+’select……………..’

select @sql1=@sql1+’from table1, table2………’

INSERT INTO #Temp

exec(‘EXEC sp_executesql ”’ + @sql + @sql1 + ”’,@paramlist’)

It compiles properly, but during the execution, the error shows as a syntax error. Finally, we ran the resulting SQL string separately, and then it runs fine without any syntax errors returning rows. Unfortunately, when we execute through sp_executesql, it shows us an error. For this case, you need to search more solutions. Many of you can suggest a workaround for this case.

As we said before, usually, the issue can occur when you are trying to make 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 receives truncated once it reaches the limit. One of the easiest methods to solve this is to split the query across a few manageable numbers of variables. After that, you are able to execute the query by concatenating the variables. But, you do not use sp_executesql as this is going to show an error when you try to execute. The sp_executesql is able to execute a string or a variable but not when you concatenate the variables.

We get information that you are able to use the EXEC to execute these types of queries. All you need to do is EXEC (@var1 +@var2+@var3)

For  example:

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

Set @var1 = ‘Select  ‘;

Set @var2 = ‘ * From ‘;

Set @var3= ‘ mytable’;

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

The query above will be able to return the records in the table.

USING SP_EXECUTESQL STORED PROCEDURE TO EXECUTE DYNAMIC SQL QUERIES

A dynamic SQL query is a query in the string format. Apparently, there are some scenarios where you have an SQL query in the form of a string. For instance, if you want to search for a product by name, you are going to enter the name of the product in a search box on the site. The product name, which is in the form of a string is going to be concatenated with a ‘Select’ query to form another string. Those types of queries require to be executed dynamically as different users are going to search for different product names so that a query is going to need to be generated dynamically depending on the product name.

Now, you understand what dynamic SQL is, let us see how the sp_executesql stored procedure is able to be used to execute dynamic SQL queries. First, you are able to make several dummy data that you are able to use to execute the examples.

Making dummy data

The script to makes a dummy database called BookStore with one table i.e. Books. The Books table has four columns. Those columns are id, name, price and category.

CREATE Database Book Store

GO

USE Book Store

CREATE TABLE Books

id INT,

name VARCHAR (50) NOT NULL

category VARCHAR (50) NOT NULL

price INT NOT NULL 

Now, let us add several dummy records in the Books table. The above script will be able to adds 10 dummy records in the Books table.

Working with the sp_executesql stored procedure

As we explained above, the sp_executesql stored procedure is used for executing dynamic SQL queries which are in the form of the string. In the script, we have declared a variable and then initialize it with a string query which returns the name, id, and price from the Books table where the price is greater than 4,000. Afterwards, you are able to execute the sp_executesql stored procedure via the EXECUTE command. You are able to execute a dynamic SQL query in the string format, easily you will be able to pass the string containing the query. Of course, it is very important to mention that the string must be in the Unicode format before the sp_executesql stored procedure executes it. This is why you have to place ‘N’ at the beginning of the string. The ‘N’ is able to convert the query string into the format of a Unicode string. Here is the output of the script.

In the real life database queries, the filter (condition) is passed by the users. For example, you may search books within a specific search limit. In this case, the SELECT query remains the same, only the ‘where’ condition is changed. It is very easy to put the ‘where’ clause in a string variable separately. Then, you need to concatenate the ‘select’ condition with the ‘where’ clause to make the final query. You are able to find out the script by yourself. In addition, we declare two variables, those are @CONDITION and @SQL_QUERY. The @CONDITION variable contains the ‘where’ clause in string format whereas the @SQL_QUERY contains the SELECT query. Those two variables are concatenated and passed to the sp_executesql stored procedure. If you need further information related to this, we suggest you give a comment in the section below.

Leave a Reply

Your email address will not be published. Required fields are marked *