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
