How to execute a T-SQL procedure from a text with a hard coded variable?
Tutorial to manage and execute a T-SQL procedure with hard coded variables, i.e. stored in a string. Indeed, a normal execution does not work with the EXEC command alone. So how do you get around this error? The solution to the problem is to use the T-SQL EXEC function sp_executesql.
1. How to execute a T-SQL procedure with hard coded variables ?
n fact, here is an example of code to execute a string in T-SQL with the command in a variable that itself contains a hard-coded variable in the text. First, create the customer table to insert the data as in the example below. The goal is to build and execute a dynamic query of this type:
DECLARE @VARIABLE AS NVARCHAR(100); DECLARE @SQL AS NVARCHAR(400); SET @VARIABLE = 'Test' SET @SQL = 'SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE'; PRINT @SQL; EXEC @SQL;
Then the following error occurs:
Could not find stored procedure ‘SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE’.
And with another variant using a different script. This script includes the declaration, the instanciation and the usage of the variable in the same variable, to execute.
DECLARE @SQL_To_Execute AS NVARCHAR(800); SET @SQL_To_Execute = ' DECLARE @VARIABLE AS NVARCHAR(100) = ''Test''; DECLARE @SQL AS NVARCHAR(400) = '' SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE ''; '; EXEC @SQL_To_Execute;
The following error message is displayed:
The name ‘
DECLARE @VARIABLE AS NVARCHAR(100) = ‘Test’;
DECLARE @SQL AS NVARCHAR(400) =
‘ SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE ‘;
‘ is not a valid identifier.
Or in other cases, this error can also be encountered:
Must declare the scalar variable ” @SQL” .
2. Create the sample table used in the T-SQL procedure
The first step is to create a customer’s table, used in the stored procedure script.
-- Test if the table CUSTOMERS already exists -- And delete it if necessary IF EXISTS( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[dbo].[CUSTOMERS]') AND type in (N'U') ) DROP TABLE [dbo].[CUSTOMERS] GO -- Create the CUSTOMERS table with the column NAME declared as UNIQUE -- The UNIQUE keyword defines the column with a unique value -- Inserting two customers with the same name is therefore impossible CREATE TABLE [dbo].[CUSTOMERS] ( [CLIENTID] int IDENTITY(1,1), [NAME] nvarchar(20) UNIQUE, [CITY] nvarchar(20) ) GO -- Insert data for manipulation examples INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon'); INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('SERGEI', 'Lyon'); INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris'); -- Check inserted rows SELECT * FROM dbo.CUSTOMERS;
3. Avoid SQL Server errors with dynamic T-SQL code and variables
Here is the solution in T-SQL to execute a SQL Server query with a variable. Build in several steps, if necessary, include them in a stored procedure. Now let’s build a fully dynamic stored procedure, that passes the hard coded variable into the text to run:
- Declaration of the variables used, such as the counter for the loop, and the variable with a string, parameters, etc.
- Build the loop for each of the counter values.
- Execute the query to insert data inside the loop
- Display the counter value for each iteration
-- Declare the variables used in the stored procedure. -- The counter -- The variable for the name of the customer -- The variable for the city name -- The parameter string is used to declare the variables -- The SQL string is in this case a query to insert rows DECLARE @Counter INT; DECLARE @CustomerName NVARCHAR(20); DECLARE @CustomerCity NVARCHAR(20); DECLARE @Parameters NVARCHAR(100); DECLARE @SQL_To_Execute NVARCHAR(200); -- Declare a hard-coded string in the @Parameters variable SET @Parameters = '@CustomerName NVARCHAR(20),@CustomerCity NVARCHAR(20)'; -- Build a basic T-SQL Insert query with a dynamic member SET @SQL_To_Execute = 'INSERT INTO [dbo].[CUSTOMERS] VALUES (@CustomerName,@CustomerCity)'; -- Beginning of the loop SET @Counter = 0 WHILE @Counter < 8 BEGIN SET @Counter = @Counter + 1 SET @CustomerName = 'Name-' + CAST(@Counter AS CHAR); SET @CustomerCity = 'City-' + CAST(@Counter AS CHAR); -- Run the query with the parameters EXEC sp_executesql @SQL_To_Execute,@Parameters, @CustomerName,@CustomerCity; PRINT 'Counter current value is : ' + CAST(@Counter AS CHAR) END -- End of the loop
Finally display the results of the data insertion with a simple SQL Server SELECT query.
SELECT * FROM [dbo].[CUSTOMERS];
4. Benefits of sp_executesql function with hard-coded variables
Finally, use the EXEC sp_executesql function instead of EXECUTE. This procedure generates reusable execution plans during loop executions. This way, performance is improved. Using this function is very useful for a complex query that is executed a significant number of times.
This method allows you to generate particularly dynamic code and thus execute a query with a variable in T-SQL that is replaced on the fly during code execution. After this article with an example to execute T-SQL procedure with hard coded variables. Below are more tutorials, like how to manage SQL Server fields of more than 8000 characters. Or an article to split a string with semicolon separators.