Dynamic SQL | SQL Server Portal

Posts Tagged ‘Dynamic SQL’

SQL SERVER 2012 – Simple way to add variables in the dynamic SQL

Posted in Solutions, SQL SERVER, tagged Dynamic SQL, SQL, SQL Server, SQL Server 2012, SQL Server 2012 - CONCAT on September 7, 2013| Leave a Comment »

Sometimes, you need to construct (n) number of result set from (n) number of inputs using single query and here the dynamic SQL concept comes handy. In some solutions, I really feel that it is very hard to ignore dynamic SQL. (Never ever execute the user input directly using dynamic SQL. Always validate it before execution.) But it has its own rule to construct and you must follow it.

Given below is the list of actions that you must take care while adding variable in the dynamic SQL.

If you pass a NULL value of a variable in the dynamic SQL the whole string will be NULL, you must handle NULL variables in the dynamic SQL.
If you pass any data type variables other than varchar/ nvarchar, it will generate error. You must convert it into varchar/nvarchar before using it into dynamic SQL.
you must use concatenation sign ( + ) to concatenate the dynamic SQL with variables. You cannot place it directly inside the the dynamic SQL.
You must be very careful while opening and closing single quotes (‘) in the dynamic SQL. If you forget even a single place you cannot execute dynamic SQL.

Given below are the two approaches. The old approach we usually follow in all versions of SQL Server and the new approach, we can follow in SQL Server 2012 and above. I came across this approach recently, built some dynamic SQL on it and found it very good.

Old Approach :
Given below is the script we usually use to construct dynamic SQL.

--This script is compatible with all versions of SQL Server
DECLARE @SQL as varchar(max)
DECLARE @Datetime as datetime

SET @Datetime='2005-07-01 00:00:00.000'

SET @SQL='SELECT * FROM Sales.SalesOrderHeader
WHERE [OrderDate]=''' + CONVERT(varchar(50),@Datetime) + ''''
PRINT @SQL
EXEC (@SQL)

New Approach :
In the new approach, we need to use the CONCAT FUNCTION (shipped in SQL Server 2012) and it will resolve the above described problems.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @SQL as varchar(max)
DECLARE @Datetime as datetime

SET @Datetime='2005-07-01 00:00:00.000'

SET @SQL=CONCAT('SELECT * FROM Sales.SalesOrderHeader
WHERE [OrderDate]=','''',@Datetime,'''')
PRINT @SQL
EXEC (@SQL)

Conclusion :
The performance will be same for both approaches but the new approach will be the shortest way to write dynamic SQL because it handles the above dynamic SQL issues internally. Also, it will be easier to debug as well because it will be in parts so you can easily split at the time of debugging.

Let me know your opinion about new approach and its pros and cons.

Read Full Post »

SQL SERVER – How to pass parameters to an SP using select command (Without variables)

Posted in Solutions, SQL SERVER, tagged Dynamic SQL, raresql, SQL, SQL Server, SQL SERVER -How to pass parameters to an SP using select command (Without variables) on July 25, 2013| Leave a Comment »

In SQL scripting, it is a common scenario when you need to pass parameters to a stored procedure using select command and we have multiple solutions for this. But before proceeding with the solutions, I need to create a table and a stored procedure to demonstrate the solution.

USE AdventureWorks2012
GO
--Create table
CREATE TABLE dbo.[Department_Audit]
(
DepartmentID smallint,
Name nvarchar(50),
)

GO
--Create Stored Procedure
CREATE PROCEDURE dbo.Usp_Department_Audit
@DepartmentID int,
@Name varchar(50)
AS
BEGIN
INSERT INTO [Department_Audit] VALUES(@DepartmentID,@Name)
END

Solution 1 :
In this solution you need to use extra variable to take the values from SELECT command and insert into a STORED PROCEDURE.
Given below is the solution.

USE AdventureWorks2012
GO
--Declare variables
DECLARE @DepartmentID int
DECLARE @Name varchar(50)

--Set value to variables
SELECT @DepartmentID=[DepartmentID],@Name=[Name]
FROM HumanResources.Department
Where [DepartmentID]=1

--pass variables to stored procedure and execute it.
EXEC dbo.Usp_Department_Audit @DepartmentID,@Name

GO
USE AdventureWorks2012
GO
--Browse the table
SELECT * FROM [Department_Audit]
GO
--OUTPUT

Solution 2 :
In this solution you do not need to use extra variable to take the values from SELECT command and insert into a STORED PROCEDURE. You can make a dynamic SQL string and insert the records directly into the STORED PROCEDURE using SELECT command. Given below is the solution.

USE AdventureWorks2012
GO
DECLARE @SQL nvarchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC dbo.Usp_Department_Audit '
+ Convert(nvarchar(2),[DepartmentID]) + ', '
+ '''' + [Name] + '''' + '; '
FROM HumanResources.[Department] Where [DepartmentID] =2
EXEC (@SQL)

USE AdventureWorks2012
GO
SELECT * FROM [Department_Audit]
--OUTPUT