Syntax for Case Statement in a Store Procedure in

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

Syntax for Case Statement in a Store Procedure in

Author

Topic

tvb2727
Starting Member

35 Posts

Posted - 2012-03-10 : 21:08:29

What is the correct syntax for a stored procedure in SQL server 2008? I want to pass in a code and then based on that code run a specific SQL statement. Of course all of the SELECT part will be the same for each case. The WHERE part is what will be different for each.

Thanks.

Code and Error:

Drop PROCEDURE [sp_set_sch_grid]
Go
CREATE PROCEDURE [sp_set_sch_grid]
@combobox_code [nvarchar](5),
@Opp_team [nvarchar](15),
@other_code_sql [nvarchar](200)

AS

CASE @combobox_code
WHEN 'pswl' THEN (SELECT Desc From tbl_sch Where TEAM_CODE = @combobox_code)
WHEN 'pswo' THEN (SELECT Desc From tbl_sch Where TEAM_CODE = @combobox_code)

END

Error:

Msg 156, Level 15, State 1, Procedure sp_set_sch_grid, Line 10
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure sp_set_sch_grid, Line 13
Incorrect syntax near the keyword 'WHEN'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 23:05:36

i cant see the need of case when here. isnt below enough?


Drop PROCEDURE [sp_set_sch_grid] 
Go
CREATE PROCEDURE [sp_set_sch_grid] 
@combobox_code [nvarchar](5),
@Opp_team [nvarchar](15),
@other_code_sql [nvarchar](200)

AS

SELECT Desc From tbl_sch Where TEAM_CODE = @combobox_code
GO

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

tvb2727
Starting Member

35 Posts

Posted - 2012-03-10 : 23:17:37

quote:
Originally posted by visakh16

i cant see the need of case when here. isnt below enough?

Drop PROCEDURE [sp_set_sch_grid]
Go
CREATE PROCEDURE [sp_set_sch_grid]
@combobox_code [nvarchar](5),
@Opp_team [nvarchar](15),
@other_code_sql [nvarchar](200)

AS

SELECT Desc From tbl_sch Where TEAM_CODE = @combobox_code
GO

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

Yes. Sorry, just made the example short. I actually have 9 different codes that will have 9 different SELECT statements. When the comboboxcode is passed in, I want it to match whichever code is there and run that select statement and process it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 23:41:53

you mean the search column varies?

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

tvb2727
Starting Member

35 Posts

Posted - 2012-03-10 : 23:46:03

quote:
Originally posted by visakh16

you mean the search column varies?

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

Not sure what you mean by that. I just want a normal VB.net code Select statement. If the code = 'X', process the case statement that is connected to 'X'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 23:54:29

can you explain the exact scenario? whatever you explained so far never calls for use of CASE...WHEN

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

tvb2727
Starting Member

35 Posts

Posted - 2012-03-11 : 00:01:03

quote:
Originally posted by visakh16

can you explain the exact scenario? whatever you explained so far never calls for use of CASE...WHEN

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

See if this helps:

CREATE PROCEDURE [sp_set_sch_grid]
@combobox_code [nvarchar](5),
@Opp_team [nvarchar](15),
@other_code_sql [nvarchar](200)

AS

CASE @combobox_code
WHEN 'pswi' THEN (SELECT TEAM_CODE From tbl_sch Where TEAM_CODE= @Opp_team AND WORL = 'W')
WHEN 'pslo' THEN (SELECT TEAM_CODE From tbl_sch Where TEAM_CODE= @Opp_team AND WORL = 'L')
WHEN 'psna' THEN (SELECT TEAM_CODE From tbl_sch Where TEAM_CODE= @Opp_team AND WORL = 'N/A')
END

If you notice the AND WORL = is different in all 3. So if @combobox_code = 'pswi', I want to pull the team_code where Team_Code = @Opp_team (what ever opponent is selected from the combo box) and where WORL = 'W' (equals a WIN)

tvb2727
Starting Member

35 Posts

Posted - 2012-03-11 : 00:06:20

All the CASE examples for a store procedure that I have seen, it seems you have to have the case within a SELECT. I do not want to do that. I want to do a normal CASE in like vb.net code and then have a specific SELECT for each Case statement etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 00:08:08

it should be like this


CREATE PROCEDURE [sp_set_sch_grid] 
@combobox_code [nvarchar](5),
@Opp_team [nvarchar](15),
@other_code_sql [nvarchar](200)

AS

SELECT TEAM_CODE From tbl_sch Where TEAM_CODE= @Opp_team AND WORL =
CASE @combobox_code
WHEN 'pswi' THEN 'W'
WHEN 'pslo' THEN 'L'
WHEN 'psna' THEN 'N/A'
END 

GO

Also please keep in mind that CASE..WHEN is not a control flow statement which branches the execution based on condition but its rather an expression which selectively returns a column value.

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

tvb2727
Starting Member

35 Posts

Posted - 2012-03-11 : 00:38:18

quote:
Originally posted by visakh16

it should be like this

CREATE PROCEDURE [sp_set_sch_grid]
@combobox_code [nvarchar](5),
@Opp_team [nvarchar](15),
@other_code_sql [nvarchar](200)

AS

SELECT TEAM_CODE From tbl_sch Where TEAM_CODE= @Opp_team AND WORL =
CASE @combobox_code
WHEN 'pswi' THEN 'W'
WHEN 'pslo' THEN 'L'
WHEN 'psna' THEN 'N/A'
END

GO

Also please keep in mind that CASE..WHEN is not a control flow statement which branches the execution based on condition but its rather an expression which selectively returns a column value.

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

Shoot!! I would have more things to test as well. I was thinking now that there is not a CASE statement in SQL that I can use, so I will just use a IF/ELSE. Sometimes in my WHERE I would only need to do 3 pieces and then in others I might need to do 4. Depends on the combobox code.

tvb2727
Starting Member

35 Posts

Posted - 2012-03-11 : 00:39:38

quote:
Originally posted by visakh16

Also please keep in mind that CASE..WHEN is not a control flow statement which branches the execution based on condition but its rather an expression which selectively returns a column value.

So is there a CASE statement that is a CONTROL FLOW STATEMENT?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 11:34:54

quote:
Originally posted by tvb2727

quote:
Originally posted by visakh16

Also please keep in mind that CASE..WHEN is not a control flow statement which branches the execution based on condition but its rather an expression which selectively returns a column value.

So is there a CASE statement that is a CONTROL FLOW STATEMENT?

Nope. CONTROL FLOW statement used in similar occasions is IF...ELSE

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources