visit https://zonixsoft.com (our official Website)

 

Consider following queries

1. SELECT * FROM @tablename

2. SELECT @colname FROM tbl

3. SELECT * FROM tbl WHERE x IN (@list)

4. SELECT @Cnt=Count(*) FROM tbl WHERE x IN (@list)

 

For the first query, it will give error ‘Must declare the table variable “@tablename”‘ if @tablename is not Table variable.

For Second query it will display all rows with same value contained in @colname rows in ‘tbl’.

For Third query it will display correct result if ‘@list‘ only one value(no comma separated values).

Fourth one is same as third query but it stores value in a variable @Cnt. The actual problem will come to know after solving third one.

To get correct result, we need to create Dynamic query for all four queries.

 

The first three queries can be execute correctly by using Exec().

EXEC() takes one parameter which is an SQL statement to execute.The parameter can be a concatenation of string variables and string literals, but cannot include calls to functions or other operators.

 

The first three can be written as:

 

1. Exec (‘SELECT * FROM’ + @tablename)

2. Exec (‘SELECT ‘ + @colname +‘ FROM tbl’)

3. Exec (‘SELECT * FROM tbl WHERE x IN (‘+@list+‘)’)

 

But Fourth one can not be written simply using Exec(). Because if @Cnt is declared outside the statement being executed in Exec() like

 

Declare @Cnt int

Exec(‘SELECT @Cnt = Count(*) FROM tbl WHERE x IN (‘+@list+‘)’)

Select @Cnt

 

it will give error

Must declare the scalar variable “@Cnt”.

 

Here is need of sp_executesql instead of Exec().

sp_executesql is a system stored procedure that you can use in place of “exec” to execute your dynamic sql. It takes two pre-defined parameters and any number of user-defined parameters.

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.

The first parameter @sql is mandatory, and contains a batch of one or more SQL statements. The data type of @sql is ntext in SQL 7 and SQL 2000, and nvarchar(MAX) in SQL 2005. A varchar value won’t do.

The second parameter @params is optional, but you will use it 90% of the time. @params declares the parameters that you refer to in @sql. The syntax of @params is exactly the same as for the parameter list of a stored procedure. The parameters can have default values and they can have the OUTPUT marker.Just like @sql, the data type of @params in SQL 7/2000 is ntext and nvarchar(MAX) in SQL 2005.

The rest of the parameters are simply the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure, either positional or named. To get a value back from your output parameter, you must specify OUTPUT with the parameter, just like when you call a stored procedure.

 

Let’s write the fourth query using sp_executesql.

Declare @CntRow int

Declare @sql nvarchar(200)

Declare @params nvarchar(200)

set @sql = ‘SELECT @Cnt=Count(*) FROM tbl WHERE x IN (@list)’

set @params = ‘@list varchar(100),@Cnt int output’

exec sp_executesql @sql,@params,‘1,2,3’,@CntRow output

Select @ CntRow

‘1,2,3’ will be passed to @list and result of @Cnt will be set to @CntRow.