Hello guys,
In past few a days, I need to make procedure which will pass as a parameter and based on that parameter value need to execute query. So I have made this procedure with dynamic query.
There is a basically two alternate way where you made dynamic query. Let see below example.
1) Using Exec.
With this approach you are building the SQL statement on the fly and can pretty much do whatever you need to in order to construct the statement.
An Example
Declare @query as varchar(1000)
Declare @Name as varchar(100)
SET @Name=’’’Andy’’’
Set @query=’SELECT * FROM CUSTOMER WHERE NAME=’+ @Name
Exec(@query)
2) USING sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) sp_executesql
SELECT @SQL = 'SELECT * FROM CUSTOMER WHERE NAME = @Name
Exec sp_executesql @SQL, N'@Name nVarChar(50)', @Name = 'Andy'
So advantages of build dynamic query using sp_executesql is that 1) No risk for SQL injection.2) Better plan reuse in the plan cache.
