CODEWITHSUNDEEP
sql-servert-sqlwhere-clause
user3410589
user3410589

Reputation: 15

Use sql variable as where clause in sql

i want to do this:

DECLARE @str varchar(max) 
DECLARE @cnt bigint    
set @str= 'where column=value'
set @cnt= (select count(*) from user+@str)

but the where clause is not working. getting no error but it will just ignore the where condition.

Upvotes: 1

Views: 3729

Answers (3)

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Check below code, in your case condition is dynamic so you need to dynamic sql.

DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @str nvarchar(500);
set @str= 'where column=value'  
SELECT @sSQL = N'SELECT @retvalOUT = COUNT(*) FROM user '+ @str +' '  ;  
SET @ParmDefinition = N'@retvalOUT int OUTPUT';
EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
SELECT @retval;

Upvotes: 1

Matt Chepeleff
Matt Chepeleff

Reputation: 419

I previously suggested wrapping your last line in EXEC(), but you can't do this and return results to a variable. To do that, use the following in place of your last line:

create table #temp (theCount int)
insert into #temp EXEC('select count(*) from Photos '+@str)
set @cnt= (select top 1 theCount from #temp)
drop table #temp

Upvotes: 2

Rahul Patil
Rahul Patil

Reputation: 169

use the execute sql syntax http://technet.microsoft.com/en-us/library/ms188001.aspx

Hope this will solve your problem

Upvotes: 0

Related Questions