String Execution in SQL

Question

Post reply

String Execution in SQL

dsaran

SSC Enthusiast

Points: 121
More actions
June 26, 2008 at 9:01 am

#93587

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!!!!!!!!!!!!!!!!!!!!!!!!!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

Try this...

declare @i varchar(100)

set @i = '''as400'',''cps'''

select * from t_system where systemcode in (@i)

Mahesh

[/font]

MH-09-AM-8694

GSquared SSC Guru Points: 260824 More actions · Answer 2

Mahesh Bote (6/26/2008)
[font="Verdana"]
Try this...
declare @i varchar(100)
set @i = '''as400'',''cps'''
select * from t_system where systemcode in (@i)
Mahesh
[/font]

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)