Dynamic SQL in SQL Server

Dynamic SQL in SQL Server

Using Exec:

declare @sqlcmd varchar(2000)

declare @collist varchar(1000)

declare @pname varchar(1000)

set @pname='''Chai'''

set @collist='Productid,Productname,categoryid'

set @sqlcmd='SELECT '+@collist+' FROM northwind.dbo.Products WHERE productname='+@pname

PRINT(@sqlcmd)

EXEC(@sqlcmd)

When you convert into SP you have to give value as dynamic so you have to

put @pname parameter inside Quote like ’’’+Parameter+ ’’’

ALTER proc dynamicspex(@pname varchar(1000)=NULL)

as

begin

declare @sqlcmd varchar(2000)

declare @collist varchar(1000)

set @collist='Productid,Productname,categoryid'

set @sqlcmd='SELECT '+@collist+' FROM northwind.dbo.Products WHERE productname='''+@pname+''''

PRINT(@sqlcmd)

EXEC(@sqlcmd)

End

EXEC dynamicspex 'Chai'

Using sp_executesql:

declare @sqlcmd nvarchar(2000)

declare @collist varchar(1000)

declare @pname varchar(1000)

set @pname='chai'

set @collist='Productid,Productname,categoryid'

set @sqlcmd='SELECT '+@collist+' FROM northwind.dbo.Products WHERE productname=@pname'

Execute SP_EXECUTESQL @sqlcmd,N'@pname varchar(1000)',@pname=@pname

Dynamic SQL in Stored Procedures

Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures so they execute well and resist SQL injection attacks.

A simple example of a stored procedure with dynamic SQL is:

use AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
   AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
 @CustomerID INT = NULL,
 @ContactID INT = NULL,
 @debug bit = 0 )
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT @ParameterDefinition = '
 @CustomerParameter INT,
 @ContactParameter INT
';

SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status], 
 [CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND CustomerID = @CustomerParameter ';
 
IF @ContactID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND ContactID = @ContactParameter ';
 
IF @debug = 1
 PRINT @SQL
 
EXEC sp_executeSQL 
 @SQL,
 @ParameterDefinition,
 @CustomerParameter = @CustomerID,
 @ContactParameter = @ContactID; 
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724