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.

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

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;

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

Hope this will solve your problem


Need Your Help

mysql field indexing

mysql indexing

I have a table products, and I have columns like product_name, product_id AND product_code.

Source nat in netfilter prerouting hooks

linux-kernel nat kernel-module netfilter

We are developing our own kernel module that does advanced source nat in netfilter prerouting hook. After upgrading from Linux kernel 2.6.32 (Ubuntu 10.04) to 2.6.35 (Ubuntu 10.04 lucid-back-ports)...