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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply