TSQL - Coding - Dynamic query

Background: sp_executesql is used to execute dynamic query in SQL Server. Below few examples are given to demonstrate the use.

Table structure and data script:

DROP TABLE test;
GO


CREATE TABLE test (group_id INT, name VARCHAR(100));
GO
INSERT INTO test VALUES (1, 'First');
INSERT INTO test VALUES (1, 'Second');
INSERT INTO test VALUES (2, 'Third');
INSERT INTO test VALUES (2, 'Fourth');
INSERT INTO test VALUES (2, 'Fifth');
INSERT INTO test VALUES (3, 'Sixth');
GO

SELECT * FROM test;


Example-01: Use sp_executesql to execute a SELECT query

DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value INT;

SET @query = N'SELECT * FROM wk_be_customer WHERE web_reference_id=@in';
SET @paramDifinition = N'@in INT';
SET @value = 233;

EXECUTE sp_executesql @query, @paramDifinition, @value;

Example-02: Use sp_executesql to execute a UPDATE query


-- Dynamic query - UPDATE
DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value1 INT, @value2 INT;

SET @query = N'UPDATE test SET group_id=@in1 WHERE group_id=@in2';
SET @paramDifinition = N'@in1 INT, @in2 INT';
SET @value1 = 1;
SET @value2=2;

EXECUTE sp_executesql @query, @paramDifinition, @value1, @value2;


Example-03: Use sp_executesql to execute a UPDATE query with IN clause (string concatenation)


DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value1 NVARCHAR(100), @value2 INT;

SET @value1 = '1,2';
SET @query = N'UPDATE test SET group_id=@in1 WHERE group_id IN ('+@value1+')';
SET @paramDifinition = N'@in1 INT';
SET @value2=3;

EXECUTE sp_executesql @query, @paramDifinition, @value2;


Notes:
1. sp_executesql - input parameters have to be NVARCHAR type

Resources:
1. http://msdn.microsoft.com/en-us/library/ms188001.aspx