Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Begginer in SQL Procedures Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 22, 2013 3:12 PM
Points: 3, Visits: 14
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
Post #1514440
Posted Monday, January 06, 2014 8:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
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.
Post #1528308
Posted Tuesday, January 07, 2014 12:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1528365
Posted Tuesday, January 07, 2014 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1528381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse