SQL Server: Stored Procedures and variables problem

Tweet

In my last post I published a step-by-step guide to create Stored Procedures in SQL Server using SQL Server Management Studio.
There are some things I would like to add, because in my experience I sometimes got stuck with errors that - when solved - were so easy to pinpoint, while little information can be found in tech forums or generally in tech sites. Specifically there is a common problem with the @query variable and its length.

Declare variables

When you declare the query variables, you have to state the data type. As we saw in my previous post, the code is:

USE [yourdb]
GO
CREATE PROCEDURE sp_name
AS

DECLARE @query VARCHAR(8000)

SET @query = 'SELECT * FROM your_table'

EXECUTE(@query)

When we declare the @query variable, we use VARCHAR(8000) as data type. While 8000 characters are quite enough for your query, I discovered that - in some cases - they are not. When running the query to create the Store Procedure, your code will be cut someway, if the variable length is not enough. There's no warning, no errors in creating the Stored Procedure. However, when running it you will get an error, stating that the query has no ending ' or some other type of warning.

PRINT @query
In order to check if your query is ok, you can print the actual statement, using this code:

PRINT @query

and insert it just before the execution of your query:

USE [yourdb]
GO
CREATE PROCEDURE sp_name
AS

DECLARE @query VARCHAR(8000)

SET @query = 'SELECT * FROM your_table'
PRINT @query
EXECUTE(@query)

When you actually execute the Stored Procedure, you can see the query statement that is being used (check the Message tab in MSSMS).
If the query is in any way not complete, it might be that your variable can't store all the query.
In that case, you can change the data type to:

DECLARE @query VARCHAR(MAX)

however it might be not enough. So, what can we do? I discovered that you can use a trick which seems stupid but it works.

How to avoid errors
Let's see the code, then I will explain it:

USE [yourdb]
GO
CREATE PROCEDURE sp_name
AS

DECLARE @query VARCHAR(MAX)

SET @query = 'SELECT * ........'
SET @query = @query + '.....FROM your_table'
PRINT @query
EXECUTE(@query)

The above code is storing the query statement in the @query variable, splitting it into parts. The dots in the example means that your query statement is going on.
How many characters can be stored into the @query variable before you need to split it, I do not know. However if you use the PRINT command you can determine where your query is actually cut.

Finally, when you are sure that all your query is correctly saved in your Stored Procedure, you can remove the PRINT command.