Use sql variable as where clause in sql

Question

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.

Take a look at this question as to some of the pitfalls highlighted in the answers : http://stackoverflow.com/questions/548090/dynamic-sql-execsql-versus-exec-sp-executesqlsql — Neil Knight, Mar 12 '14 at 14:21

Matt Chepeleff · Accepted Answer · 2014-03-12T14:29:34.780

2

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

edited Mar 12 '14 at 14:29

answered Mar 12 '14 at 14:18

Matt Chepeleff

419
2
7

getting this error:Incorrect syntax near the keyword 'EXEC'. for your line – user3410589 Mar 12 '14 at 14:25

score 1 · Answer 2 · answered Mar 12 '14 at 14:37

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;

score 0 · Answer 3 · answered Mar 12 '14 at 14:21

0

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

Hope this will solve your problem

answered Mar 12 '14 at 14:21

Rahul Patil

159
5

Use sql variable as where clause in sql

3 Answers3