Case statement is stored procedure

Question

Post reply

Case statement is stored procedure

William Hurst

SSC Enthusiast

Points: 109
More actions
June 5, 2006 at 3:01 am

#97083

Hi,

I am new to stored procedures but I am quite keen to learn. I am trying to get it to insert a set of survey answers into a table. Eventually the questions might change so I fodn't want the the table tructure to be too rigid. anyways here is what i have:

CREATE
PROCEDURE dbo.usp_InsertSurveyAnswers
(
@SurveyID
int,
@QuestionTotal
int,
/*===== Step 1 =====*/
@Email
text,
@HearAboutus
text,
/*===== Step 2 =====*/
@iShortList
text,
@iFavourites
text,
@iSearch
text,
@iRate
text,
@iVoucher
text,
@strEasyToUse
text,
@strEasyComment
text,
@strColourComment
text,
/*===== Step 3 =====*/
@strSuggestFeature
text,
@strComments
text,
@streWineComments
text,
@strWinecozaComments
text,
@strGetWineComments
text,
@strKalariComments
text,
@strOtherComments
text
)
AS
DECLARE @i TINYINT
DECLARE @AnswerText Text
BEGIN TRANSACTION
SET @i = 1
SET @SurveyID = 1
WHILE @i <= @QuestionTotal
BEGIN
CASE @i
WHEN (@i=1)
THEN @AnswerText = @Email
WHEN (@i=2)
THEN @AnswerText = @HearAboutus
WHEN (@i=3)
THEN @AnswerText = @iShortList
WHEN (@i=4)
THEN @AnswerText = @iFavourites
WHEN (@i=5)
THEN @AnswerText = @iSearch
WHEN (@i=6)
THEN @AnswerText = @iRate
WHEN (@i=7)
THEN @AnswerText = @iVoucher
WHEN (@i=8)
THEN @AnswerText = @strEasyToUse
WHEN (@i=9)
THEN @AnswerText = @strEasyComment
WHEN (@i=10)
THEN @AnswerText = @strColourComment
WHEN (@i=11)
THEN @AnswerText = @strSuggestFeature
WHEN (@i=12)
THEN @AnswerText = @strComments
WHEN (@i=13)
THEN @AnswerText = @streWineComments
WHEN (@i=14)
THEN @AnswerText = @strWinecozaComments
WHEN (@i=15)
THEN @AnswerText = @strGetWineComments
WHEN (@i=16)
THEN @AnswerText = @strKalariComments
WHEN (@i=17)
THEN @AnswerText = @strOtherComments
ELSE @AnswerText = ''
If @AnswerText = '' OR @AnswerText = NULL
Continue
Else
BEGIN
INSERT INTO
Answers_table
(
SurveyID_FK
,
QuestionNumber
,
AnswerText
)
VALUES
(
@SurveyID
,
@i
,
@AnswerText
)
END
SET @i = @i + 1
COMMIT TRANSACTION
/* SET NOCOUNT ON */
RETURN
When trying to save it I get the following error:
Msg 156, Level 15, State 1, Procedure usp_InsertSurveyAnswers, Line 34
Incorrect syntax near the keyword 'CASE'.
I can't for the life of me figure this out.
Any help?

Viewing 9 posts - 1 through 8 (of 8 total)

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

stewart noble SSCrazy Points: 2009 More actions · Answer 1

Hi,

there are 2 flavours of the CASE Syntax, I your example, i think this should do the trick:

SELECT AnswerText = CASE @i

when 1 then @email

when 2 then @hearaboutus.....

end

Hope this helps

S

William Hurst SSC Enthusiast Points: 109 More actions · Answer 2

Where would I put that?

I put it just after the first BEGIN

BEGIN

SELECT @AnswerText = CASE @i

WHEN (@i=1) THEN @Email

and get

Incorrect syntax near '='.

stewart noble SSCrazy Points: 2009 More actions · Answer 3

The end specified in my example relates to the case statement:

begin

declare @AnswerText varchar(30),

@i int,

@email varchar(20),

@hearaboutus varchar(20)

select @i = 2,

@email = 'an.email@anaddress.com',

@hearaboutus = 'From a place'

SELECT AnswerText = CASE @i

when 1 then @email

when 2 then @hearaboutus

end

Does this help?

S

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

CASE is not a flow-of-control statement in T-SQL, it is a function. Therefore it is used within another statement to provide a value, it doesn't begin a block of statements. Each THEN or ELSE clause must evaluate to a single value that is data-type compatible with the result of the first THEN clause.

It may be a funny-looking function because it is terminated by END rather than using parentheses around the arguments, but it is still a function.

Mauro Ciaccio SSCrazy Points: 2617 More actions · Answer 5

Hi,

Not sure if the problem was due to the usual panic we sometimes encounter when this site is the last resort, but there were quite a few syntax errors in your code. I have corrected them and added comments where relevant. The modified and working SProc is as follows. The code to test it is at the end of the SProc.

NB - I have used a temporary table as a work-around.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertSurveyAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[usp_InsertSurveyAnswers]

GO

CREATE PROCEDURE dbo.usp_InsertSurveyAnswers

(

-- Define defaults for all your text inputs, so you don't need to supply them if not

-- required

@SurveyID int,

@QuestionTotal int,

/*===== Step 1 =====*/

@Email text = '',

@HearAboutus text = '',

/*===== Step 2 =====*/

@iShortList text = '',

@iFavourites text = '',

@iSearch text = '',

@iRate text = '',

@iVoucher text = '',

@strEasyToUse text = '',

@strEasyComment text = '',

@strColourComment text = '',

/*===== Step 3 =====*/

@strSuggestFeature text = '',

@strComments text = '',

@streWineComments text = '',

@strWinecozaComments text = '',

@strGetWineComments text = '',

@strKalariComments text = '',

@strOtherComments text = ''

)

AS

DECLARE @i TINYINT

--Cannot declare local variables as Text

--??? Are any of the input params really true TEXT types???

--??? If not, convert your input params to appropriate varchars

--SEE: http://www.mcse.ms/archive96-2005-12-2023743.html

DECLARE @AnswerText varchar(8000)

CREATE TABLE #Answers_Table

(

SurveyID_FK int,

QuestionNumber int,

AnswerText varchar(8000)

)

BEGIN TRANSACTION

SET @i = 1

--This parameter is passed in

--SET @SurveyID = 1

WHILE @i <= @QuestionTotal

BEGIN

SELECT @AnswerText = CASE @i

WHEN (1) THEN @Email

WHEN (2) THEN @HearAboutus

WHEN (3) THEN @iShortList

WHEN (4) THEN @iFavourites

WHEN (5) THEN @iSearch

WHEN (6) THEN @iRate

WHEN (7) THEN @iVoucher

WHEN (8) THEN @strEasyToUse

WHEN (9) THEN @strEasyComment

WHEN (10) THEN @strColourComment

WHEN (11) THEN @strSuggestFeature

WHEN (12) THEN @strComments

WHEN (13) THEN @streWineComments

WHEN (14) THEN @strWinecozaComments

WHEN (15) THEN @strGetWineComments

WHEN (16) THEN @strKalariComments

WHEN (17) THEN @strOtherComments

ELSE ''

END

IF @AnswerText = '' OR @AnswerText = NULL

BEGIN

CONTINUE

END

ELSE

BEGIN

INSERT INTO #Answers_table (SurveyID_FK, QuestionNumber, AnswerText)

VALUES (@SurveyID, @i, @AnswerText)

END

SET @i = @i + 1

END

SELECT * FROM #Answers_table

DROP TABLE #Answers_table

COMMIT TRANSACTION

/* SET NOCOUNT ON */

GO

/**************** TESTING SCRIPT *************/

DECLARE @rc int

DECLARE @SurveyID int

DECLARE @QuestionTotal int

-- Set parameter values

--EXEC @rc = [FDRC_Labour].[dbo].[usp_InsertSurveyAnswers] @SurveyID, @QuestionTotal, <@Email text>, <@HearAboutus text>, <@iShortList text>, <@iFavourites text>, <@iSearch text>, <@iRate text>, <@iVoucher text>, <@strEasyToUse text>, <@strEasyComment text>, <@strColourComment text>, <@strSuggestFeature text>, <@strComments text>, <@streWineComments text>, <@strWinecozaComments text>, <@strGetWineComments text>, <@strKalariComments text>, <@strOtherComments text>

SET @SurveyID = 1

SET @QuestionTotal = 2

EXEC @rc = [FDRC_Labour].[dbo].[usp_InsertSurveyAnswers] @SurveyID, @QuestionTotal, 'mauro@test1', 'HearAboutUs1'

SET @SurveyID = 2

SET @QuestionTotal = 3

EXEC @rc = [FDRC_Labour].[dbo].[usp_InsertSurveyAnswers] @SurveyID, @QuestionTotal, 'mauro@test2', 'HearAboutUs2', 'ShortListText2'

Mauro Ciaccio SSCrazy Points: 2617 More actions · Answer 6

Oopps,

Replace

EXEC @rc = [FDRC_Labour].[dbo].[usp_InsertSurveyAnswers]

with your own DB name for testing.

William Hurst SSC Enthusiast Points: 109 More actions · Answer 7

William Hurst

SSC Enthusiast

Points: 109

June 8, 2006 at 12:06 am

#642433

thanks for help everyone. All sorted now

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 8

Could you not use two stored procudures instead of that loop and case statment?

E.g.

CREATE PROCEDURE dbo.usp_InsertAnswer

@SurveyID int,

@QuestionNumber int,

@AnswerText text

AS

IF @AnswerText = '' OR @AnswerText IS NULL RETURN

INSERT INTO #Answers_table (SurveyID_FK, QuestionNumber, AnswerText)

VALUES (@SurveyID, @QuestionNumber, @AnswerText)

Then in InsertSurveyAnswers just call

BEGIN TRAN

EXEC usp_InsertAnswer @SurveyID, 1, @Email

...

EXEC usp_InsertAnswer @SurveyID, 17, @strOtherComments

COMMIT TRAN

This to me just seems much easier to read and understand when returning to it at a later date?