Building Dynamic Statements at Run Time - SQL Server

Pandians
Posted by Pandians under Sql Server category on | Views : 3379
Many of Us using dynamically build SQL statements at run time in SQL Server. We can build dynamic queries and execute in two methods. SP_EXECUTESQL and EXECute.

SP_EXECUTESQL - System stored procedure to execute a UNICODE string. We can use Parameters similar to RAISERROR Statement.

EXECute - To execute a character string. But It does not support parameter substitution.

Sample Table with Data :
CREATE TABLE SAMPLE1
(ID INT IDENTITY(1,1),
Code VARCHAR(5),
Description VARCHAR(MAX))
GO
INSERT SAMPLE1 VALUES('A','Assembly')
INSERT SAMPLE1 VALUES('B','Binary')
INSERT SAMPLE1 VALUES('C','Coalesce')
INSERT SAMPLE1 VALUES('D','Dynamic')
INSERT SAMPLE1 VALUES('E','Execute')
GO

Building Dynamic Query at Run-Time:
DECLARE   @SQLStmt	NVARCHAR(MAX),
@ColumnName NVARCHAR(MAX),
@ColumnValue NVARCHAR(MAX)

SELECT @ColumnName = 'Code'
SELECT @ColumnValue = 'C'
SELECT @SQLStmt = N'SELECT * FROM SAMPLE1 WHERE ' + @ColumnName + ' = @ColValue'

EXEC SP_EXECUTESQL @SQLStmt,
N'@ColValue VARCHAR(MAX)',
@ColValue = @ColumnValue

Result:

ID Code Description
3 C Coalesce

Cheers

Comments or Responses

Login to post response