Case statement is stored procedure
- 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?
-
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
- Where would I put that?I put it just after the first BEGINBEGIN
SELECT @AnswerText = CASE @i
WHEN (@i=1) THEN @Email
and getIncorrect syntax near '='.
-
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
end
Does this help?
S
-
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.
-
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'
-
Oopps,
Replace
EXEC @rc = [FDRC_Labour].[dbo].[usp_InsertSurveyAnswers]
with your own DB name for testing.
-
thanks for help everyone. All sorted now
-
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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply