T-SQL procedure with hard coded variables and dynamic execution

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

**Execute a T-SQL procedure with hard coded variables using sp_executesql**

Finally display the results of the data insertion with a simple SQL Server SELECT query.

SELECT *
FROM [dbo].[CUSTOMERS];

Select the data inserted with the dynamic T-SQL stored procedure

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.