November 14, 2013 at 12:21 pm
Hi ,
below procedure with if else statements.Single query updates well but when i trying to do with condition not going well.
Im executing this way
exec 3,2,2,2(here ID,response,buildinhpoint,@ID1)when i execute like this it updates @ID1=3 statement but not @ID1=2 .Is there any solutions for this program.
ALTER PROCEDURE [dbo].[UpdateSurvey]
-- Add the parameters for the stored procedure here
@ID int,@response nvarchar(50),@buildingpoint nvarchar(50),@ID1 int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(@ID1=1)
Begin
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID1=2)
Begin
UPDATE survey SET
ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID1=3)
Begin
UPDATE survey SET
ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=4)
Begin
UPDATE survey SET
ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
January 6, 2014 at 8:36 pm
There is much wrong here, starting with the idea of having one proc do three things based on a 'magic number'. I suggest breaking this in to three procs, and then working out the issues with each one.
January 7, 2014 at 12:16 am
Break it into multiple procedures and ditch all the ELSE statements, you don't need them and they're making things more complex
IF (@Var = 1)
EXEC PROC1
IF (@Var=2)
EXEC PROC 2
...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2014 at 2:09 am
A slightly different approach from the previous two, triggered by a gruelling telephone survey I was foolish enough to agree to participate in just a couple of weeks ago. There must have been two hundred questions and it took 35 minutes.
Since all four statements are updates of the same table and must occur as a transaction, I'd change the code to update the table only once, like so - given the current calling syntax:
ALTER PROCEDURE [dbo].[UpdateSurvey]
-- Add the parameters for the stored procedure here
@ID int, -- explanation
@response nvarchar(50), -- explanation
@buildingpoint nvarchar(50), -- explanation
@ID1 int -- explanation
AS
SET NOCOUNT ON;
UPDATE c SET
ques_1= CASE WHEN @ID1 = 1 THEN x.response ELSE ques_1 END,
q1buildpoint = CASE WHEN @ID1 = 1 THEN y.buildingpoint ELSE q1buildpoint END,
ques_2a = CASE WHEN @ID1 = 2 THEN x.response ELSE ques_2a END,
q2abuildpoint = CASE WHEN @ID1 = 2 THEN y.buildingpoint ELSE q2abuildpoint END,
ques_2b = CASE WHEN @ID1 = 3 THEN x.response ELSE ques_2b END,
q2bbuildpoint = CASE WHEN @ID1 = 3 THEN y.buildingpoint ELSE q2bbuildpoint END,
ques_2c = CASE WHEN @ID1 = 4 THEN x.response ELSE ques_2c END,
q2cbuildpoint = CASE WHEN @ID1 = 4 THEN y.buildingpoint ELSE q2cbuildpoint END
FROM survey c
OUTER APPLY (SELECT response FROM surveyanswers t2 WHERE id = @response) x
OUTER APPLY (SELECT buildingpoint FROM surveyanswers t2 WHERE id = @buildingpoint) y
WHERE c.cid = @ID
RETURN 0
Christian makes a good point about the "magic number". Can you post up some code to show how this stored procedure is run? We might figure out a way to eliminate it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy