String Execution in SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » String Execution in SQL

String Execution in SQL

Rate Topic

Display Mode

Topic Options

Author

Message

dsaran

Posted Thursday, June 26, 2008 9:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2008 11:57 PM
Points: 9, Visits: 42

select * from t_system

O/p is
---
AS400
CPS
HBS

But the below code is not work

declare @i varchar(100)
set @i = 'as400,cps'
select * from t_system where systemcode in (@i)

anybody help

I need to implement this one to production db.
Actual problem is, when the client pass 'as400' server will respond only as400 and when the client pass 'CPS' server will respond only 'CPS'

But the client pass the argument 'ALL; server need to return all the above. How to implement anybody plz help!!!!!!!!!!!!!!!!!!!!!!!!!!!

Post #524277

Mahesh Bote

Posted Thursday, June 26, 2008 10:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

Try this...

declare @i varchar(100)
set @i = '''as400'',''cps'''
select * from t_system where systemcode in (@i)

Mahesh

MH-09-AM-8694

Post #524384

GSquared

Posted Thursday, June 26, 2008 11:39 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

Mahesh Bote (6/26/2008)

Try this...

declare @i varchar(100)
set @i = '''as400'',''cps'''
select * from t_system where systemcode in (@i)

Mahesh

That won't work. "In" doesn't work that way with variables.

There are a couple of ways you can do this. One is create a table of the system codes and query that as a sub-query or a join. Another is provide a comma-delimited list of system codes, and then query it with a string parser (there are some on the scripts page of this site).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #524451

Jeff Moden

Posted Thursday, June 26, 2008 8:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

To wit...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #524699

« Prev Topic | Next Topic »

Permissions