Stored Procedure No return after adding a filter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure No return after adding a filter

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-12-08 : 20:54:32

Hello,

I have a stored procedure which contains a temp table.
Everything looks good except I add a filter.

If I return the entire dataset, it's ok.

if @EmpID = 'ALL'
begin
if @Register='ALL'
select @qry1='select * from #TmpJnlData'
else
select @qry1='select * from #TmpJnlData where Register= '''+@Register+''''
end

else 
begin
if @Register='ALL'
select @qry1 ='select * from #TmpJnlData where Operator= '''+@EmpID+''''
else
select @qry1 ='select * from #TmpJnlData where Operator= '''+@EmpID +''''+' and Register= '''+@Register+''''
end

 exec(@qry1)

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 02:28:13

no need of dynamic sql. you want only this

select * from #TmpJnlData 
where (Register=@Register or @Register='ALL')
and (Operator=@EmpID or @EmpID ='ALL')

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-12-09 : 08:10:10

The question is there is no @Register='ALL' or @EmpID='ALL'in the temp table.

I only use them to control output as inputing parameters of the stored procedure.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-09 : 09:04:30

We see that. Doesn't it miraculously work if you replace your code by Visakh's?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-12-09 : 09:18:33

No, his code is not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:37:56