{"id":18312,"date":"2022-11-04T07:05:00","date_gmt":"2022-11-04T06:05:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=18312"},"modified":"2022-11-21T12:25:50","modified_gmt":"2022-11-21T11:25:50","slug":"t-sql-procedure-with-hard-coded-variables","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/t-sql-procedure-with-hard-coded-variables\/","title":{"rendered":"T-SQL procedure with hard coded variables and dynamic execution"},"content":{"rendered":"\n

How to execute a T-SQL procedure from a text with a hard coded variable?<\/em><\/strong><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

1. How to execute a T-SQL procedure with hard coded variables ?<\/h2>\n\n\n\n

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:<\/p>\n\n\n\n

DECLARE @VARIABLE AS NVARCHAR(100);\nDECLARE @SQL AS NVARCHAR(400); \n\nSET @VARIABLE = 'Test'\nSET @SQL = 'SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE';\n\nPRINT @SQL;\n\nEXEC @SQL;<\/pre>\n\n\n\n
Then the following error occurs:<\/p>\n\n\n\n
Could not find stored procedure ‘SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE’.<\/em><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
DECLARE @SQL_To_Execute AS NVARCHAR(800); \n\nSET @SQL_To_Execute = '\nDECLARE @VARIABLE AS NVARCHAR(100) = ''Test'';\nDECLARE @SQL AS NVARCHAR(400) = \n'' SELECT FIELD1, FIELD2 FROM TABLE_1 WHERE FIELD1 = @VARIABLE '';\n';\n\nEXEC  @SQL_To_Execute; <\/pre>\n\n\n\n
The following error message is displayed:<\/p>\n\n\n\n
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.<\/em><\/p>\n\n\n\n
Or in other cases, this error can also be encountered: <\/p>\n\n\n\n
Must declare the scalar variable ” @SQL” .<\/em><\/p>\n\n\n\n
2. Create the sample table used in the T-SQL procedure<\/h2>\n\n\n\n
The first step is to create a customer’s table, used in the stored procedure script.<\/p>\n\n\n\n
-- Test if the table CUSTOMERS already exists\n-- And delete it if necessary\nIF EXISTS(\n   SELECT 1 FROM sys.objects\n   WHERE  object_id = object_id(N'[dbo].[CUSTOMERS]')\n      AND type in (N'U')\n)\nDROP TABLE [dbo].[CUSTOMERS]\nGO\n\n-- Create the CUSTOMERS table with the column NAME declared as UNIQUE\n-- The UNIQUE keyword defines the column with a unique value\n-- Inserting two customers with the same name is therefore impossible\nCREATE TABLE [dbo].[CUSTOMERS] (\n   [CLIENTID] int IDENTITY(1,1),\n   [NAME] nvarchar(20) UNIQUE,\n   [CITY] nvarchar(20)\n)\nGO\n\n-- Insert data for manipulation examples\nINSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon');\nINSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('SERGEI', 'Lyon');\nINSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris');\n\n-- Check inserted rows\nSELECT    *\nFROM    dbo.CUSTOMERS;<\/pre>\n\n\n\n
3. Avoid SQL Server errors with dynamic T-SQL code and variables<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n