possible to concatenate text to Where clause?

declare @.s varchar(300)
set @.s = ' recID = 100'
Select * from tbl1 Where + @.s
This returns a syntax error. Is it possible to concatenate text like this
to the Where clause? What does the correct syntax look like?
Thanks,
Richbetter yet - is it possible to use If/Else or select case in a where clause?
"Rich" wrote:

> declare @.s varchar(300)
> set @.s = ' recID = 100'
> Select * from tbl1 Where + @.s
> This returns a syntax error. Is it possible to concatenate text like this
> to the Where clause? What does the correct syntax look like?
> Thanks,
> Rich|||I seriously doubt this will help, but here is a small sample.
Any/every time I've tried to put a case statement in the where clause, I
fall flat.
But here is a small sample anyways.
You might want to check my article at:
http://www.sqlservercentral.com/col...lem.as
p
also.
declare @.price float
select @.price = 0.00
select @.price = 2.9900
--select @.price = null
select top 5 * , title, price
from pubs.dbo.titles
where
case
when @.price = 0 then price --here i am saying.. i didn't actually supply a
price.. so match the price with the price (aka, it gets everything because
price will always match price )
when @.price is null then price
else @.price -- aka, since i actually supplied a @.price, then match
the @.price with the price
end
= price -- the db field
----
---
-- the CASE statement above will translated into 1 of the two items below
select top 5 * , title, price
from pubs.dbo.titles where price = price -- aka, will always match, so you
get everything
select top 5 * , title, price
from pubs.dbo.titles where @.price = price --aka, only if the db value for
price matches the variable value for @.price
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> better yet - is it possible to use If/Else or select case in a where
clause?
> "Rich" wrote:
>
this|||Thanks for your reply. This does help actually. What I was trying to do
was to use an SP as a recordsource for a form, but I changed my mind and
decided to go with an inline function. This works, and I can still use the
case statement inside the function.
May I ask, how do you make arguments optional for a function?
"sloan" wrote:

> I seriously doubt this will help, but here is a small sample.
> Any/every time I've tried to put a case statement in the where clause, I
> fall flat.
> But here is a small sample anyways.
> You might want to check my article at:
> http://www.sqlservercentral.com/col...lem.
asp
> also.
>
>
> declare @.price float
> select @.price = 0.00
> select @.price = 2.9900
> --select @.price = null
> select top 5 * , title, price
> from pubs.dbo.titles
>
> where
> case
> when @.price = 0 then price --here i am saying.. i didn't actually supply
a
> price.. so match the price with the price (aka, it gets everything because
> price will always match price )
> when @.price is null then price
> else @.price -- aka, since i actually supplied a @.price, then match
> the @.price with the price
> end
> = price -- the db field
> ----
--
> ---
> -- the CASE statement above will translated into 1 of the two items below
> select top 5 * , title, price
> from pubs.dbo.titles where price = price -- aka, will always match, so y
ou
> get everything
> select top 5 * , title, price
> from pubs.dbo.titles where @.price = price --aka, only if the db value fo
r
> price matches the variable value for @.price
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> clause?
> this
>
>|||Rich
Unless I am completely wrong, I feel its fairly straightforward. try this.
declare @.s varchar(300)
set @.s = ' recID = 100'
exec('Select * from tbl1 Where ' + @.s)|||You can do this, but it's a horrible idea if @.s is
user-input or based on user-supplied data or metadata.
If @.s is something like '1=1; drop table tbl1' and the
code is executed with sufficient privilege, boom! Bye,
bye table 1. Worse and less noticeable things can
happen, too.
Read
http://www.*sommarskog*.se/dynamic_sql.html
http://www.unixwiz.net/techtips/sql-injection.html
Steve Kass
Drew University
Omnibuzz wrote:

>Rich
> Unless I am completely wrong, I feel its fairly straightforward. try this
.
>declare @.s varchar(300)
>set @.s = ' recID = 100'
>exec('Select * from tbl1 Where ' + @.s)
>
>