Getting field value in Select Statement

I have problem getting the value of a field in a select statement to be stored in
a variable when the select statement is stored in variable and executed thru exec sp_executeSQL.

ex.

Declare @.Count as int, @.SQL as varchar(500),
@.DynamicWhereClause as varchar(100)

/*
here i set the value of my dynamic WHERE clause.

*/

set @.SQL = 'Select count(*) from table1 ' + @.DynamicWhereClause

exec sp_executesql @.SQL

I want to store the the value for Count(*) to @.Count.

Thanks,
Lou BelaroYou need to parameterize the SELECT statement and use it in the sp_executesql call. Here is the modified code:

set @.SQL = 'Select @.cnt = count(*) from table1 ' + @.DynamicWhereClause

exec sp_executesql @.SQL, N'@.cnt int output', @.cnt = @.count OUTPUT

Note that you have to be careful while generating SQL statement. You need to protect the code against SQL injection attacks. In your example, you need to make sure that @.DynamicWhereClause does not use input from outside directly without any validation.|||Thanks for the solution. It works.

Lou Belaro