Stored Procedure Case Statement

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Case Statement

Author

Topic

extrapulp
Starting Member

9 Posts

Posted - 2010-06-14 : 09:32:45

I am fairly new to SQL. I have a stored procedure that is working currently. I would like to expand it to handle each input differently. It is being called from a WebApp.

Here is the current working stored procedure

procedure dbo.SeriesSelect (
	@Series char
)
as



If (@Series = 'F')
	Begin

		Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.DomeStock
		Where PartNum  Like '%'+ @Series + '%'
 		and PartNum  not Like '%D' + @Series + '%'
 		and PartNum not Like '%N' + @Series + '%'
 		and PartNum not Like '%C' + @Series + '%'
 		Order by PartNum
	End
Else

Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.Domestock
Where PartNum Like '%' + @Series + '%'
Order by PartNum

I would like to add a case statement instead of the if statement. Every way I have tried it gives me syntax errors.

I have tried
Select Case @Series
When 'F' Then

And Select Case
When @Series = 'F' Then

And Select Case
When (@Series = 'F') Then

I would like to end up with the functionality of something like this.

Case @Series
When 'F' Then Select statement for 'F'
When 'S99' Then Different select statement for 'S99'
When 'BL' Then Different select statement for 'BL'
Else
Default select statement

Any suggestions are greatly appreciated!

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 09:47:39

Are number of columns different for each case?

Madhivanan

Failing to plan is Planning to fail

extrapulp
Starting Member

9 Posts

Posted - 2010-06-14 : 11:02:15

madhivanan,

No the columns returned are exactly the same and in the same order. It is the search criteria that i need to tweak. I am doing a Like lookup. As in the example if statement. If we are looking for 'F' I need to filter out the records that have 'DF', 'NF', and 'CF'.

I just want to refine the search results for each series that the user can select. To make it easier on the Java programmer and to keep some standards I would like all the data related logic in stored procedures.

The users are able to select the following choices. F, U, NS, P, M, C, RK, A, WT, XF, DT, BL, GX, Q, S99, E, Dart, and PNP. That is the exact text that is being passed to the stored procedure. In instance of the Dart selection the PartNum will be similar to A4085-310. But the data being passed to the stored procedure is 'Dart'.

Here is a sampling of the data that I am returning if it will help.

Min Max PartNum Available SalesSample PendingSale Notes
0 0 E12-RDF-340 8830 0
0 0 E12.7-RDF-400 5940 0
0 0 E12.7-RDFT-280 4610 0
0 0 E12.7-RDFT-400 6060 0 2000
5000 25000 F08210 20440 0
1000 10000 F08210N 7810 0
0 0 PNP-08X2 (F08400) 0 0
0 0 PNP-08 (F08400N) 60 0
0 0 PNP-08 (UD08290) 0 0
0 0 PNP-08 (UD08290N) 0 0
0 0 PNP-08 (NS08400) 0 0
20000 50000 F08260 1890 0 0
0 0 F08260C 4500 (1500 per Cart)

Thanks for the quick reply!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 02:10:01

Is this not working?

Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.Domestock
Where PartNum Like '%' + @Series + '%'
Order by PartNum

Madhivanan

Failing to plan is Planning to fail

extrapulp
Starting Member

9 Posts

Posted - 2010-06-15 : 10:20:17

madhivnan,

Yes that query works. The issue is that I need to create 18 separate select statements depending on the value passed into @Series. I can create the queries. I can not seem to create the needed case statement to be able to process the different queries.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 10:24:54

Something like this



	Begin

		Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.DomeStock
		Where 
		(
		PartNum  Like '%'+ @Series + '%'
 		and PartNum  not Like '%D' + @Series + '%'
 		and PartNum not Like '%N' + @Series + '%'
 		and PartNum not Like '%C' + @Series + '%'
		and
		(@Series = 'F')
		)
		OR
		(
		PartNum Like '%' + @Series + '%' 
		and
		(@Series = 'D')
		)
		OR
		(
		PartNum Like '%' + @Series + '%' 
		and
		(@Series = 'S39')
		)
		
 		Order by PartNum
	End

Madhivanan

Failing to plan is Planning to fail

extrapulp
Starting Member

9 Posts

Posted - 2010-06-15 : 11:40:05

madhivanan,

I think I am following you, and yes I think it will work.

I will create the rest of the select statements and try it and post back here.

Thank you very much!

Just because I am curious is there any reason that the case statement gives me syntax errors?

Thanks

extrapulp
Starting Member

9 Posts

Posted - 2010-06-15 : 12:46:29

madhivanan,

I tried your logic and it does not return any results. I started with your code but the stored procedure now returns no results. Here is the exact code I am running.


Begin

		Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.DomeStock
		Where
		(
		PartNum  Like '%'+ @Series + '%'
 		and PartNum  not Like '%D' + @Series + '%'
 		and PartNum not Like '%N' + @Series + '%'
 		and PartNum not Like '%C' + @Series + '%'
		and
		(@Series = 'F')
		)
		OR
		(
		PartNum Like '%' + @Series + '%' 
		and
		(@Series = 'DT')
		)
		OR
		(
		PartNum Like '%' + @Series + '%' 
		and PartNum not like '%A%'
		and PartNum not like '%E%'
		and PartNum not like '%F%'
		and PartNum not like '%N%'
		and
		(@Series = 'S99')
		)
		
 		Order by PartNum
	End

If I run each select statement that is between the OR I get the results I was expecting.

The only way I know to show what I am trying to do is with this example, which will not run due to syntax error 156 near the keyword CASE.


CASE @Series
      When 'F' Then
                Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.DomeStock
		Where PartNum  Like '%'+ @Series + '%'
 		and PartNum  not Like '%D' + @Series + '%'
 		and PartNum not Like '%N' + @Series + '%'
 		and PartNum not Like '%C' + @Series + '%'
 		Order by PartNum
     When 'S99' Then
                Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.DomeStock
		Where PartNum Like '%' + @Series + '%' 
		and PartNum not like '%A%'
		and PartNum not like '%E%'
		and PartNum not like '%F%'
		and PartNum not like '%N%'
 		Order by PartNum
 		
 End

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-16 : 02:22:01

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

extrapulp
Starting Member

9 Posts

Posted - 2010-06-16 : 14:33:04

madhivanan,

OK here is some sample data.


PartNum		Min		Max		Available	SalesSample	PendingSale
F10200		0		0		14,230				        0
F10200N		25000		75000		17,510		                        0
F10200NC	0		0		5,000		1250 per Cart)	        0
FD10200N	0		0		,320					0
F10260		0		0		19,570				        0
F10260T		0		0		0		(1000 per Cart)	        0
U06180		10000		30000		37,220				        0
U06180N		0		0		13,130				        0
UD08290		20000		60000	0						0
UD08290C	0		0		6,000		(1500 per Cart)	        0
NS12400N	0		0		2,340					0
NS12450		0		0							0
P22170		0		0		31,240			         	0
P22220		0		0		9,220					0
P22300		0		0		0					0
M22170		0		0		3,950					0
M22220		0		0		2,940					0
C42220		0		0							0
C50220		0		0							0
RK22170		0		0		4,880					4,880
RK35170		0		0		6,420					0
A12400		0		0		3,700					0
WT04170		5000		20000		25,370			        	0
WTD05200	0		0		12,370				        0
DT12450N	10,000	        30,000	        37,470			     	        0
DTBL12340N	0		0		4,840					0
DTBL12450N	0		0		0					0
TB12###N	0		0				     		        0
TCF08400	0		0		4,590		      		        0
TCG06240	0		0		23,710				        0
BL08260		0		0		50,000				        0
BL08400		0		0		2,540					0
BL10280		10000		30000		3,890					0
GX12700		0		0		32,055				        0
GX12900		0		0		5,280					0
Q08400NT	0		0		11,000	          (1000 per Cart)       0
Q10200N		0		0							0
Q10340NT	0		0		16,000	          (1000 per Cart)       0
S99366		0		0		5,900					0
S99390		50000		75000		40,000	          (1,000 per cart)      0
S99390C		50000		75000		15,000				        0
S99394		0		0		-418,330				0
E8-TDF-280	0		0		4,260					0
E8-TDF-340	0		0		5,830					0
E9-RDF-400	0		0		8,285					0
E10-RDF-230	0		0							0
E10-RDF-280	0		0							0
E10-RDF-340	0		0		9,910					0
E.5-ONN1-340	3000		10000		2,160					0
E.5-ONN1-450	0		0		1,910					0
PNP-06 (F06085)	0		0		120					0
PNP-06 (F06130)	0		0							0
PNP-06 (F06180)	0		0		90					0
PNP-06(U06180N)	0		0		90					0

Some example results would be (Im only putting the PartNum as the results):
If 'F' is passed
F10200
F10200N
F10200NC
FD10200N
F10260
F10260T

If 'S99' is passed
S99366
S99390
S99390C
S99394

If 'GX' is passed
GX12700
GX12900

I hope this clears it up some.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-17 : 03:04:52

Try this

		Select PartNum,Min,Max,Available,SalesSample,PendingSale,Notes,SeqNum from dbo.DomeStock
		Where
		(
		PartNum  Like @Series + '%'
 		and PartNum  not Like '%D' 
 		and PartNum not Like '%N' 
 		and PartNum not Like '%C' 
		and
		(@Series = 'F')
		)
		OR
		(
		PartNum Like @Series + '%' 
		and
		(@Series = 'DT')
		)
		OR
		(
		PartNum Like @Series + '%' 
		and PartNum not like '%A%'
		and PartNum not like '%E%'
		and PartNum not like '%F%'
		and PartNum not like '%N%'
		and
		(@Series = 'S99')
		)
		
 		Order by PartNum