sql server programming: Hai all,

I need to do the following...

declare @strwhere varchar(100)
set @strwhere = ' and col2 = 6' -- where columnname is an integer datatype.
select * from <tablename> where col1 = 'B' + @strwhere

but the above query is not working when @strwhere takes a where condition
for an integer datatype. How can i do this..i don't want to use
sp_executesql..

Is there any other way to do this?

Thanks,
V.Boomessh

[quoted text, click to view]

No. Not without dynamic SQL based on the information you provided. EXEC
or sp_executesql are the options here.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Hi,
I agree with David
You will ahve to use dynamic sql or sp_executesql (which is better)
I tried to solve the problem by using dynamic WHERE condition
and CASE expression. I might wrong but maybe you will get some ideas

Use NorthWind
go
declare @country as varchar(10)
declare @CustomerID as varchar(10)
set @country = 'Germany'
set @CustomerID='ALFKI'

select * from customers
where case @country when '' then 1-----get everything
when 'Germany' then
case when Country like @country then 1 else 0 end end=1
and
case @CustomerID when '' then 1
when 'ALFKI' then
case when CustomerID like @CustomerID then 1 else 0 end end=1







[quoted text, click to view]

[quoted text, click to view]

OP = Original Poster... You wrote "My question is why would you NOT want
to use dynamic SQL" which made me think that original post (name of
Boomessh) was you. I couldn't tell if it was, so I just predicated my
post with that question.

I agree with your comments philisophically, but disagree when it comes
to dynamic SQL. The "limitations" as you put it are more profound than a
single statement that makes use of dynamic sql. Once you use it in an
database, even just once, the underlying objects are exposed to all
users/groups who need to run the SQL statement (this will be addressed
in SQL Server 2005 when run from a stored procedure). And for DML
operations, that just isn't acceptable for most systems that must be
locked down as best as possible. On systems that allow access to the
underlying objects and use embedded SQL, using dynamic SQL is probably
not much of an issue. But in an age where security is a paramount
concern of businesses, I'd hate to be the one responsible for allowing
access to the Customer table and exposing all that data to someone
simply issuing a "SELECT * from Customers".


--
David Gugick
Quest Software
www.imceda.com
www.quest.com

My question is why would you NOT want to use dynamic SQL. I understand
there are limitations to using it, and you should avoid it whenever
possible, but sometimes you need it. To say you don't want to use it
when the situation clearly calls for it is like saying "I need to screw
something in, but I don't want to use a screwdriver. Will a hammer
work?"

Stu

[quoted text, click to view]

I can't tell... Are you the OP?

The main reason for avoiding dynamic SQL is security. In order to use
it, users must be granted direct access to the underlying tables. For
many systems, this is not allowed as all access must take place through
stored procedures. It's bad enough with SELECT statements, but even
worse when you need to use dynamic SQL with inserts, updates, and
deletes.


--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Not sure what you mean by "Are you the OP?";

I recognize that there are certain limitations to using dynamic SQL,
and that for 90% of the time when one might be tempted to use it, you
don't really need it. However, it is a tool that can be used to solve
certain business problems, and I don't think you should approach a
business problem saying "I'm NOT going to use this tool"; you should
always look at using the best solution for the problem, even if it's
one that shouldn't be used in 90% of the situations.

Granted, the scenario described above is one that I probably wouldn't
use dynamic SQL, but since I don't know the whole backstory, I wouldn't
rule anything out.

[quoted text, click to view]