SQL Question

[eveCalypso]

Hello All,

I would like my SQL statement to do the following:
...
where (@givenID = 0 or (@givenID<>0 and myColumn = @givenID)

That means that if my user selected "All", it must not concern itself with the value for myColumn, otherwise, it must set myColumn = to the ID selected by the user.

I have 4 such inputs, how would you do it in SQL - is it possible? The statement will live in a stored procedure. The statement above is not quite correct yet, but I am sure there must be a way!

Regards,
EvE

 

[sweth]

you can use dynamic stored procedure
create proc sp_example(
@param1 datatype // pass the condition as param
@param2 datatype
---
)
as
declare @strSql varchar(2000)
select @strSql = 'Select col1,col2.. from
tablenme
where'
if @param1 is not null
@strsql = @strsql + ' where fieldname like %' + @param1 + %'

if ......
.........
@strsql = left(@strsql,3) // to remove last and
exec (@strSql)

based on condition build sql dynamically
and execute is

Regrs
siva

 

[eveCalypso]

Dear sweth,

Thank you for that. I suppose I can create a dynamic stored procedure! Maybe SQL doesn't have to be all that clever afterall

Thank you.
It will solve my problem for the moment.
Regards,
EvE