Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Stored Procedure help?

    Code:
    CREATE PROCEDURE usp_addHardwareDefinition
    
    @iType int,
    @cHardware varchar(10),
    @cDescription varchar(100),
    @dMaterial money,
    @dLabour money,
    @iError int OUTPUT,
    @cError varchar(255) OUTPUT
    
    AS
    
    SELECT tbHardware.[Description], tbHardware.Type, tbHardware.Hardware FROM tbHardware WHERE tbHardware.[Description] = @cDescription 
    if(@@rowcount > 0)
    BEGIN
          set @cError = 'Hardware with the same description already exists.' 
          set @iError = 50000 
          RETURN (0)
    END
    
    INSERT INTO tbHardware (Type, Hardware, [Description], Material, Labour) VALUES (@iType, @cHardware, @cDescription, @dMaterial, @dLabour)
    
    set @iError = @@error
    if(@iError = 0)
        set @cError = 'OK'
    else
        set @cError = 'Errors occured'
    GO
    Here I am checking if a hardware with the same description already exists. If so, I want to set the @iError to 50000 (user defined) and set @cError string. This portion of the code does not seem to be working. @iError and @cError are both empty when I step through the code in the front end application.

    Front end is c++ and ADO.

    Anyone see anything wrong with the above? I am just learning about stored procedures so.....

    Mike B

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm assuming that you want the stored procedure to raise an error that your ADO code can easily detect. If so, the @@error value is ephemeral, it changes after every statement executes. I would suggest using something more like:
    PHP Code:
    CREATE PROCEDURE usp_addHardwareDefinition

    @iType int,
    @
    cHardware varchar(10),
    @
    cDescription varchar(100),
    @
    dMaterial money,
    @
    dLabour money,
    @
    iError int OUTPUT,
    @
    cError varchar(255OUTPUT

    AS

    if (
    <= SELECT Count(*)
       
    FROM tbHardware WHERE tbHardware.[Description] = @cDescription 
       BEGIN
          set 
    @cError 'Hardware with the same description already exists.' 
          
    set @iError 50000 
          RAISERROR 
    @iError, @cError
          
    RETURN (0)
       
    END
    ELSE
       
    BEGIN
          INSERT INTO tbHardware 
    (TypeHardware, [Description], MaterialLabour)
             
    VALUES (@iType, @cHardware, @cDescription, @dMaterial, @dLabour)
          
    SELECT @iError = @@error, @cError 'Inserted'
       
    END

    RETURN 0
    GO 
    This will set the return values of @iError and @cError as you'd expect, plus it will actively signal an error via ADO. I think this is closer to what you want.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by Pat Phelan
    I'm assuming that you want the stored procedure to raise an error that your ADO code can easily detect. If so, the @@error value is ephemeral, it changes after every statement executes. I would suggest using something more like:
    PHP Code:
    CREATE PROCEDURE usp_addHardwareDefinition

    @iType int,
    @
    cHardware varchar(10),
    @
    cDescription varchar(100),
    @
    dMaterial money,
    @
    dLabour money,
    @
    iError int OUTPUT,
    @
    cError varchar(255OUTPUT

    AS

    if (
    <= SELECT Count(*)
       
    FROM tbHardware WHERE tbHardware.[Description] = @cDescription 
       BEGIN
          set 
    @cError 'Hardware with the same description already exists.' 
          
    set @iError 50000 
          RAISERROR 
    @iError, @cError
          
    RETURN (0)
       
    END
    ELSE
       
    BEGIN
          INSERT INTO tbHardware 
    (TypeHardware, [Description], MaterialLabour)
             
    VALUES (@iType, @cHardware, @cDescription, @dMaterial, @dLabour)
          
    SELECT @iError = @@error, @cError 'Inserted'
       
    END

    RETURN 0
    GO 
    This will set the return values of @iError and @cError as you'd expect, plus it will actively signal an error via ADO. I think this is closer to what you want.

    -PatP
    I attempted to use this code and it got a couple of errors. Because I am new to this, I am having a hard time trouble shooting it:

    Code:
    Error 156 : Incorrect syntax near the keyword "SELECT"
    Line 13 : Incorrect syntax near ')'
    Line 17 : Incorrect syntax new ','.
    Incorrect syntax near the keyword 'ELSE'.
    Mike B

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    I changed it to the following:
    Code:
    CREATE PROCEDURE usp_addHardwareDefinition
    
    @iType int,
    @cHardware varchar(10),
    @cDescription varchar(100),
    @dMaterial money,
    @dLabour money,
    @iError int OUTPUT,
    @cError varchar(255) OUTPUT
    
    AS
    
    SELECT Count(tbHardware.Hardware)  FROM tbHardware WHERE tbHardware.[Description] = @cDescription
    
    if(@@rowcount  > 1)
       BEGIN
          set @cError = 'Hardware with the same description already exists.' 
          set @iError = 50000 
          RAISERROR (@cError, @iError, 1)
          RETURN (0)
       END
    ELSE
       BEGIN
          INSERT INTO tbHardware (Type, Hardware, [Description], Material, Labour)
             VALUES (@iType, @cHardware, @cDescription, @dMaterial, @dLabour)
          SELECT @iError = @@error, @cError = 'Inserted'
       END
    
    RETURN 0
    GO
    I am still getting the same results.

    Mike B
    Last edited by MikeB_2k4; 03-05-04 at 15:24.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not to be critical, but wouldn't worrying aboiut if the same type exists be more important?

    And is there a primary key on this table?
    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Why are you checking @@rowcount? Won't it always be 1 (the record count) since you are doing a SELECT COUNT...

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My bad! That's what I get for posting from a midget screen (160 by 160 pixels), where I can't proofread for squat. Let's try this from a real PC, after a bit more thought!
    PHP Code:
    CREATE PROCEDURE usp_addHardwareDefinition
       
    @iType int,
       @
    cHardware varchar(10),
       @
    cDescription varchar(100),
       @
    dMaterial money,
       @
    dLabour money,
    AS

    if 
    EXISTS (SELECT *
       
    FROM tbHardware
          WHERE tbHardware
    .[Description] = @cDescription
       
    RAISERROR 50000'Hardware with the same description already exists.' 
    ELSE
       
    INSERT INTO tbHardware (TypeHardware, [Description], MaterialLabour)
          
    VALUES (@iType, @cHardware, @cDescription, @dMaterial, @dLabour)

    RETURN 
    0
    GO 
    This eliminates the passing of error information via variables... If an error occurs, let ADO signal that to your application. You could actually take this a step further by declaring a unique constraint on the tbHardware.[Description] column.

    While it might take a minute to "get your head around this design", I think it will pay you big dividends as you progress.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by Brett Kaiser
    Not to be critical, but wouldn't worrying aboiut if the same type exists be more important?

    And is there a primary key on this table?
    The problem is not just the type, but all too often do the engineers here enter the same information for multiple pieces of hardware.

    Example:

    Code:
    Mark (PK)| Type | Description 
    -------------------------------------
     P1          |  1     | 4"X4"X1/4" 
     P2          |  1     | 6"X6"x1/2"
     P3...........
     .........
    .......
     P60        |         | 4"X4"X1/4"
    .......
    The description can include anything really, but..... I figured I would try to add a constraint to this column and if it slows things too much, I will remove it. Won't hurt anything to try it.

    Mike B

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I still think type is the key, not the description...

    I'd have a PK on type, and then just attempt the insert...

    check @@ERROR to see if it failed because of dup key...

    don't overdo the sql if you don't have to....



    and if it is importnant to makes sure a description is unique, then put a contraint on it...but that seems strange


    because


    DESCRIPTION
    -----------------


    This is the same description
    This is the sam description
    This is the same descriptio


    No?
    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.

  10. #10
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by grahamt
    Why are you checking @@rowcount? Won't it always be 1 (the record count) since you are doing a SELECT COUNT...
    Will it be? Even if the record doesn't exist?

    Mike B

  11. #11
    Join Date
    Feb 2004
    Posts
    134
    [SIZE=1]Originally posted by Brett Kaiser
    I still think type is the key, not the description...

    I'd have a PK on type, and then just attempt the insert...
    Type is a many to one relationship with another table. The type indicates plates, channels, angles, etc.....

    The Mark is the unique value, as this is the identification of the part.

    Mike B

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by MikeB_2k4
    The Mark is the unique value, as this is the identification of the part.
    Then wouldn't you be concerned about duplicate marks instead of duplicate descriptions? I would expect that they might want to use 'generic horsefeathers' at times as a description for a whole lot of mark values, at least as an interim step.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Posts
    134

    Thumbs up

    RAISERROR, Very nice!!!

    Mike B

  14. #14
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by Pat Phelan
    Then wouldn't you be concerned about duplicate marks instead of duplicate descriptions? I would expect that they might want to use 'generic horsefeathers' at times as a description for a whole lot of mark values, at least as an interim step.

    -PatP
    Yes, I am concerened about mark numbers. They already cannot be duplicated because the Primary Key unique constraint. What I am concerned about also (as a secondary concern) is having 4000 plate records, all with the same description (meaning, they are the same thing) being referenced by 4000 different mark numbers.

    Mike B

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn surrogate keys.....

    Just put the constraint on, and just do the insert and check...

    Cookie cutter error handling...I do it this way for almost everything

    Code:
    CREATE PROCEDURE usp_addHardwareDefinition
    	  @iType	int
       	, @cHardware	varchar(10)
       	, @cDescription varchar(100)
       	, @dMaterial	money
       	, @dLabour	money
    
    AS
    --			Execute usp_addHardwareDefinition
    --
    --                	Enterprise Solutions
    --
    --           Date:      May 3rd, 2003
    --         Author:      Claudette Zakkak
    --         Server:      <server name>NJROS1D151\NJROS1D151DEV
    --       Database:      TaxReconDB
    --    Description:      
    --
    --
    --                      The stream will do the following:
    --
    --				1. 
    --
    --    Tables Used:	system_var
    --			Company_return
    --			Company_return_list
    --			Wh_proof
    --			Wh_Proof_List
    --			Disb_Proof
    --			Disb_Proof_List
    --
    -- Tables Created:      None
    --
    --
    --Change Log
    --
    -- UserId   	Date        	Description
    -- -----------  --------------  ------------------------------------------------------------------------------------------
    -- x057117  	05/02/2003  	1. Initial release
    --
    --
    --
    
    Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int
    
    BEGIN TRAN
    
    SET NOCOUNT ON
    
    
    	INSERT INTO tbHardware (Type, Hardware, [Description], Material, Labour)
    	     VALUES (@iType, @cHardware, @cDescription, @dMaterial, @dLabour)
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    		If @error_out <> 0
    			BEGIN
    				Select @Error_Loc = 1
    				Select @Error_Type = 50001
      				GOTO usp_addHardwareDefinition_Error
    			END
    		If @Result_Count = 0
    			BEGIN
    			SELECT @Error_Loc = 1
    			SELECT @Error_Message = 'No rows inserted to tbHardware table '
    			SELECT @Error_Type = 50002
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    COMMIT TRAN
    
    usp_addHardwareDefinition_Exit:
    
    SET NOCOUNT OFF
    
    return
    
    usp_addHardwareDefinition_Error:
    
    Rollback TRAN
    
    If @Error_Type = 50001
    	BEGIN
    		Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))  
    					     + ',"' + '  @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
    					     + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
    					     + ',"' + '  Message: ' + ',"' + RTrim(description)
    				  	   From master..sysmessages
    				 	  Where error = @error_out)
    	END
    If @Error_Type = 50002
    
    	BEGIN
    		Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
    			              + ',"' + ' Severity:  UserLevel ' 
    				      + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
    	END
    
    RAISERROR @Error_Type @Error_Message
    
    GOTO usp_addHardwareDefinition_Exit
    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.

Posting Permissions

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