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.
-
1Take a look at this question as to some of the pitfalls highlighted in the answers : stackoverflow.com/questions/548090/…Neil Knight– Neil Knight2014-03-12 14:21:57 +00:00Commented Mar 12, 2014 at 14:21
Add a comment
|
3 Answers
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
1 Comment
user3410589
getting this error:Incorrect syntax near the keyword 'EXEC'. for your line
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;
Comments
use the execute sql syntax http://technet.microsoft.com/en-us/library/ms188001.aspx
Hope this will solve your problem
