SQL
Dynamic Query:
Sometime hard coded queries are not suitable to achieve the
result. In that case we create dynamic query. SQL provide us different ways to create
dynamic queries:
1.
Query
With parameter
2.
Use
Exec
3.
Use
sp_executesql
1.
Query
With Parameter:
This first approach is to pass parameters into WHERE clause of
your SQL statement. Suppose we need to find all records from the “EmDetails” table where
City = 'Chandigarh'. This can be
done as:
DECLARE @city
varchar(75)
SET @city = 'Chandigarh'
SELECT * FROM EmDetails WHERE
City = @city
2.
Use
Exec:
Second option is to use EXEC, with this approach we built the SQL statement on the runtime (dynamically).
Suppose we want to get columns EmpID, Name and City where City =
'Chandigarh'.
DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'EmpID, Name, City'
SET @city = '''Chandigarh'''
SET @sqlCommand = 'SELECT ' +
@columnList + ' FROM
EmDetails WHERE City = ' + @city
EXEC
(@sqlCommand)
As we can see from above example handling the @city value is not
at straight forward, because you also need to define the extra quotes in order
to pass a character value into the query. These extra quotes could also
be done within the statement, but either way you need to specify the extra
single quotes in order for the query to be built correctly and therefore run.
3.
Use
Sp_ExecuteSql:
In this
approach we will use both first and second approach in one query. With this approach we have the ability to dynamically
build the query and also able to still use parameters as you could in example
1. This saves the need to have to deal with the extra quotes to get the query
to build correctly. In addition, with using this approach you can ensure
that the data values being passed into the query are the correct datatypes.
DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'EmpID,
Name, City'
SET @city = 'Chandigarh'
SET @sqlCommand = 'SELECT
' + @columnList +
' FROM EmDetails WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city
0 comments:
Post a Comment