Case statement inside 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 2000 Forums

Transact-SQL (2000)

Case statement inside Stored Procedure

Author

Topic

miamikk
Starting Member

19 Posts

Posted - 2007-06-02 : 22:33:38

I am a Newbie and I need help with case statement in SP.

I am using a case statement in a SP and I keep getting error. I think it a syntax error but I am not able to figure it out. I get error on line "@TblNamea='2006'"

any help is appreciated. I am pasting a part of code below.

ALTER Procedure [dbo].[free_CustomsDistrict_HS4_XX](
@TblName1 varchar(20)
)

Declare @TblNamea varchar(15)
Declare @TblNameb varchar(15)

BEGIN
CASE @TblName1
WHEN '2007' THEN
(
@TblNamea='2006' //Error on this line//
@TblNameb='2005'
)
ELSE
(
@TblNamea='2005'
@TblNameb='2004'
)

END CASE

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-03 : 00:09:48

what you want is a simple if/else construct. CASE is not for flow control, it returns a single value in a select.

try this:


if @TblName1='2007'
begin
   set @TblNamea='2006'
   set @TblNameb='2005'
end
else
begin
   set @TblNamea='2005'
   set @TblNameb='2004'
end

EDIT: btw, if you are planning to use dynamic sql, you should read this first: http://www.sommarskog.se/dynamic_sql.html

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources