case statement TSQL

I'm trying to do something like the following:
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
ORDER BY
lead_id
This causes an error at the >. How do a dynamic where clause like this?
Thanks for any help"gl" <gl@.discussions.microsoft.com> wrote in message
news:C9CE7143-7D55-4786-B059-1A18B1256360@.microsoft.com...
> I'm trying to do something like the following:
> SELECT
> *
> FROM
> leads WITH (NOLOCK)
> WHERE
> CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
> WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
> ORDER BY
> lead_id
> This causes an error at the >. How do a dynamic where clause like this?
>
Well, before showing you how to do this, I'm obliged to warn you against it.
Multiple different queries should be presented to SQL Server seperately so
the server can make intelligent choices about how to execute the query.
Such a compound query usually requires a full scan to execute. But if you
break it up, one or more of the resulting queries may be very cheap:
if @.lead_id_lookup = 0 then
begin
SELECT *
FROM
leads WITH (NOLOCK)
WHERE
CASE lead_id >= @.lead_id
ORDER BY
lead_id
end
else if @.lead_id_lookup > 0
begin
SELECT *
FROM
leads WITH (NOLOCK)
WHERE
CASE lead_id = @.lead_id
ORDER BY
lead_id
end
Now, finally, to answer your question:
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
CASE WHEN @.lead_id_lookup = 0 AND lead_id >= @.lead_id THEN 1
WHEN @.lead_id_lookup > 0 AND lead_id = @.lead_id
THEN 1
ELSE 0 END = 1
ORDER BY
lead_id
David|||gl
untested
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
lead_id = CASE WHEN @.lead_id_lookup = 0 THEN
lead_id >= @.lead_id
WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
END
ORDER BY
lead_id
"gl" <gl@.discussi
ons.microsoft.com> wrote in message
news:C9CE7143-7D55-4786-B059-1A18B1256360@.microsoft.com...
> I'm trying to do something like the following:
> SELECT
> *
> FROM
> leads WITH (NOLOCK)
> WHERE
> CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
> WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
> ORDER BY
> lead_id
> This causes an error at the >. How do a dynamic where clause like this?
> Thanks for any help|||SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
(@.lead_id_lookup = 0 AND lead_id >= @.lead_id)
OR (@.lead_id_lookup > 0 AND lead_id = @.lead_id )
ORDER BY
lead_id