Semi Colon use in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Semi Colon use in Stored Procedure

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-08-31 : 14:49:33

I am trying to modify a stored procedure to use the below code.
My variable @foundService would be used within a where clause.
Can someone help me out with my semi colon use?
I want to put quotes around each value in the case statement, but am having troubles with the dynamic sql. (Something like:
@Ser = 'aaa' then "'02', '03', '04', '05', '07'"

Hope this makes sense.....

declare @foundService varchar(50)

set @foundService = case
when @Ser = 'aaa' then '02, 03, 04, 05, 07'
when @Ser = 'bbb' then '01, 02, 03, 06, 09, 0A, 11'
when @Ser = 'ccc' then '09, 0A, 0B, 0C, 0F'
when @Ser = 'ddd' then '10, 11'
ELSE '01, 02, 03, 04, 05, 06, 07, 09, 10, 11, 0A, 0B, 0C, 0F'
END

select * from xyz
where serv in (foundService)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-31 : 15:12:58

this is what you want:


declare @foundService varchar(50) 

set @foundService = case
when @Ser = 'aaa' then '''02'', ''03'', ''04'', ''05'', ''07'''
when @Ser = 'bbb' then '''01'', ''02'', ''03'', ''06'', ''09'', ''0A'', ''11'''
when @Ser = 'ccc' then '''09'', ''0A'', ''0B'', ''0C'', ''0F'''
when @Ser = 'ddd' then '''10'', ''11'''
ELSE '''01'', ''02'', ''03'', ''04'', ''05'', ''06'', ''07'', ''09'', ''10'', ''11'', ''0A'', ''0B'', ''0C'', ''0F''' 
END



exec ('select * from xyz where serv in (' + @foundService + ')')

but it would be better to throw the values into a lookup table:

Create Table aLookupTable (ser char(3), foundService char(2));
GO

insert aLookupTable values('aaa', '02');
insert aLookupTable values('aaa', '03');
insert aLookupTable values('aaa', '04');
insert aLookupTable values('aaa', '05');
insert aLookupTable values('aaa', '07');
insert aLookupTable values('bbb', '01');
insert aLookupTable values('bbb', '02');
insert aLookupTable values('bbb', '03');
insert aLookupTable values('bbb', '06');
insert aLookupTable values('bbb', '09');
insert aLookupTable values('bbb', '0A');
insert aLookupTable values('bbb', '11');
insert aLookupTable values('ccc', '09');
insert aLookupTable values('ccc', '0A');
insert aLookupTable values('ccc', '0B');
insert aLookupTable values('ccc', '0C');
insert aLookupTable values('ccc', '0F');
insert aLookupTable values('ddd', '10');
insert aLookupTable values('ddd', '11');
GO

and join to that.

Looks to me like you want it to default to every possible value if @ser isn't in the list. you can do that like this

declare @ser char(3)
set @ser = 'bbb'

SELECT	distinct xyz.*
FROM	xyz
JOIN	aLookupTable t
On	t.ser = @ser
or	@ser is null

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-01 : 08:29:54

Thanks Russell, that's exactly what I was looking for. Also, I appreciate your suggestion to use a lookup table. I will check it out as an option.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-01 : 10:28:59

Without using Dynamic SQL, how can I get this SP to recognize the variable @foundService used in the where clause?

declare @foundService varchar(50)
set @foundService = case
when @Ser = 'aaa' then '''aa'', ''bb'', ''cc''
ELSE '''01'', ''02'', ''03'', ''04'', ''05''
END

select name, type
into #d
from history
where
mydate between @Beg and @End
and type In (@foundService)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-01 : 11:34:50

you can't. that's why i suggested creating a lookup table

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-01 : 11:38:23

you could create it on the fly if you don't want to create a table...


declare	@foundService varchar(50) 
declare @tbl table (foundService char(2))

IF @ser = 'aaa'
BEGIN
	insert @tbl Values('aa')
	insert @tbl values('bb')
	-- etc
END

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-01 : 12:59:16

Lookup table appears to be working as needed.
Thanks for the suggestion!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 13:02:09

you can do this though


declare @foundService varchar(50) 

set @foundService = case
when @Ser = 'aaa' then '02, 03, 04, 05, 07'
when @Ser = 'bbb' then '01, 02, 03, 06, 09, 0A, 11'
when @Ser = 'ccc' then '09, 0A, 0B, 0C, 0F'
when @Ser = 'ddd' then '10, 11'
ELSE '01, 02, 03, 04, 05, 06, 07, 09, 10, 11, 0A, 0B, 0C, 0F' 
END


select * from xyz
where ',' + @foundService + ',' LIKE '%,' + serv + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-02 : 09:08:53

Hi Visakh16, thanks, that's what I originally was trying to do.

Russell suggested that I throw the values in a lookup table and join to this table in my query. What is the big advantage of using the lookup table approach?

The original way seems much cleaner and less lines of SQL.

Thanks
Qman

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-02 : 13:18:32

putting them in a table allows you to change the business rules as the business evolves without any code change. much easier/cleaner to maintain.

leaves you far fewer lines of code. I would say that this

SELECT	xyz.*
FROM	xyz
JOIN	aLookupTable t
On	t.ser = @ser

is a lot less than this:

declare @foundService varchar(50) 

set @foundService = case
when @Ser = 'aaa' then '02, 03, 04, 05, 07'
when @Ser = 'bbb' then '01, 02, 03, 06, 09, 0A, 11'
when @Ser = 'ccc' then '09, 0A, 0B, 0C, 0F'
when @Ser = 'ddd' then '10, 11'
ELSE '01, 02, 03, 04, 05, 06, 07, 09, 10, 11, 0A, 0B, 0C, 0F' 
END


select * from xyz
where ',' + @foundService + ',' LIKE '%,' + serv + ',%'

but, Visakh's solution is good. and for a one time shot his is what i would choose. for a long term business solution, i'll make it table driven.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-02 : 13:40:19

Russell, thanks for the explanation, much appreciated!

For my case, it is a one time request to be used for reporting purposes.

Question, What do the ', ' mean in the below SQL code?
It work great, just trying to understand how this piece of code works. Is ', ' special, I would have thought a quote would be needed rather than a semi?

and ',' + @foundService + ',' LIKE '%' + SrchType + '%'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-02 : 13:49:20

Visakh has wrapped commas around the string so you can match on it without resorting to dynamic sql as i did in my 1st post above.

you can print out the values to see what he's doing

print ',' + @foundService + ','
for example

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-02 : 13:59:34

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources