Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Stored Procedure and If Statements

    Im a newb to stored procedures and have come up with the following to update several tables depending on the values passed:

    Code:
    USE [custmanagementgeneral]
    GO
    /****** Object:  StoredProcedure [dbo].[tblonenet_sdt_procoupdate]    Script Date: 12/08/2010 16:57:00 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[tblonenet_sdt_procoupdate]
    -- ProCo1
    @search varchar(100),
    @porttype int,
    @nooffixed int,
    @noofmobile int,
    @other int,
    @upgradenewdevices bit,
    @imptype varchar(50),
    @manualreasdon varchar(200),
    @dslordered bit,
    -- Sales
    @CDCrecieved bit,
    @CDCreturned datetime,
    @CDCpassed bit,
    @Creditchecked bit,
    @failed bit,
    @reason varchar(200),
    @sitesurvey varchar(50),
    -- Phase Status
    @proj1_complete  bit,
    @cancelled bit,
    @cancelled_reason bit,
    @onhold bit,
    @onholdreason bit
    
    AS
    BEGIN
    SET NOCOUNT ON;
    
    UPDATE    [tblonenet_sdt_proj_projco]
    SET       [porttype] = @porttype, [nooffixed] = @nooffixed, [noofmobile] = @noofmobile, [other] = @other, [upgradenewdevices] = @upgradenewdevices, [imptype] = @imptype, [manualreasdon] = @manualreasdon, [dslordered] = @dslordered
    WHERE     [cust_id] = @search;
                              
    UPDATE    [tblonenet_sdt_proj_sales] SET [CDCrecieved] = @CDCrecieved, [CDCreturned] = @CDCreturned, [CDCpassed] = @CDCpassed, [Creditchecked] = @Creditchecked, [failed] = @failed, [reason] = @reason, [sitesurvey] = @sitesurvey
    WHERE     [cust_id] = @search;                            
    
    	IF @proj1_complete=1 
    	BEGIN
    		UPDATE    [tblonenet_sdt_proj_status] SET [proj1_complete] = @proj1_complete, proj1_cdate = getdate(), Status = 'Awaiting design' WHERE	  [cust_id] = @search;
    	END
    
    	IF @cancelled=1 
    	BEGIN
    		UPDATE    [tblonenet_sdt_proj_status] SET [cancelled] = @cancelled, cancelled_reason = @cancelled_reason, Status = 'Cancelled' WHERE	  [cust_id] = @search;
    	END
    
    	IF @onhold=1 
    	BEGIN
    		UPDATE    [tblonenet_sdt_proj_status] SET [onhold] = @onhold, onholdreason = @onholdreason, Status = 'On Hold' WHERE	  [cust_id] = @search;
    	END
    
    END
    The statements in the IF's don't execute what am i doing wrong?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Whats your call looks like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your IF statements are not executing because you are not passing in the parameter values you think you are.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    Your IF statements are not executing because you are not passing in the parameter values you think you are.
    Being Blind, he makes those assumptions
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser View Post
    Being Blind, he makes those assumptions
    Being from Jersey, he makes these kinds of comments.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •