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

Boomessh wrote:
Show quoteHide quoteNo. Not without dynamic SQL based on the information you provided. EXEC
or sp_executesql are the options here.

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







Show quoteHide quote

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

Stu wrote:
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.

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.

David Gugick wrote:
Show quoteHide quote

Stu wrote:
Show quoteHide quoteOP = 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".

I understand your concerns, and I agree with them; however, I've seen
far too many posts that assume that there are absolute methods of
working with data (in other words, these rules MUST be followed, no
exceptions).  I'm just simply saying that there are some scenarios
where its OK to use a certain tool, such as dynamic SQL, and if I can
steal your own words:

"On systems that allow access to the underlying objects and use
embedded SQL, using dynamic SQL is probably not much of an issue. "

Logically, you've expressed at least two scenarios where dynamic SQL
might be used; a third is for application-specific databases.  We have
certain db's where there are no human users; we have 'bots that analyze
data patterns, and dynamic SQL enables us to  reuse execution plans in
a way that traditional SQL statements seemed unable to do.  There is
only one way to get data in and out of that database, and it's through
the application, so it is reasonably secure.  It's a scenario where
we've accepted the risk, because the benefits outweigh the costs.

Again, not trying to pick a philosophical fight; just stating my
opinion.

Stu

And I keep seeing posts where the guy wants 2 + 2 = 5 because it is the
easiest way to kludge a problem -- The "Enron School of  Relative Data
Rules" in action !  Correct math, normalization, etc. have no
exceptions.   After that, we can talk about product specific kludges
and the dangers with them.

Might want to look at other SQLs.  IBM will cache multiple execution
plans and pull out one of them based on the stats at run time.
StreamBase analyzes data on the flow.  Etc.

Query problem
Pros/Cons using sqltransaction class vs stored procedure
Can I avoid using a cursor
DECLARE CURSOR ON EXEC... (Sybase does it, so why cant SQL Server?
Aaron, Itzik - about the MIN Function