Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Stored procedures in SQL Server

    I come from a MySQL background, and have recently began migrating to SQL Server.

    I can't, for the life of me, find an example of a stored procedure that does the following in SQL Server:
    - Multi-lined
    - Contains both input and output parameters
    - Sets one of the output parameters within the stored procedure

    Can anyone provide some insight? I.e. Why does the following not work?
    Code:
    IF NOT EXISTS (SELECT * FROM syscomments WHERE id = object_id('usp_test'))
    BEGIN
    	CREATE PROCEDURE "usp_test"
    	(@num1 INT, @num2 INT, @result INT OUTPUT)
    	AS
    	BEGIN
    		DECLARE newValue INT;
    		SET @newValue = num1 + num2;
    		SET @result = @newValue;
    	END;
    END;
    I get these errors (I'm using the Management Studio Express)
    Code:
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'PROCEDURE'.
    Msg 155, Level 15, State 2, Line 7
    'INT' is not a recognized CURSOR option.
    Msg 137, Level 15, State 1, Line 8
    Must declare the scalar variable "@newValue".
    Msg 137, Level 15, State 2, Line 9
    Must declare the scalar variable "@newValue".

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    you need to have the @ symbol in front of your variables in
    Code:
    SET @newValue = num1 + num2
    i would probably remove all of the semicolons.
    Then, remove the quotes around the procedure name in the CREATE line.

    EDIT: Also, the CREATE statement generally has to be at the beginning of a batch, so the whole IF NOT EXISTS construct will likely have to be removed.

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Thanks for the quick reply

    So I've modified it to incorporate your suggestions
    Code:
    IF NOT EXISTS (SELECT * FROM syscomments WHERE id = object_id('usp_test'))
    BEGIN
    	CREATE PROCEDURE usp_test
    	(@num1 INT, @num2 INT, @result INT OUTPUT)
    	AS
    	BEGIN
    		DECLARE newValue INT
    		SET @newValue = @num1 + @num2
    		SET @result = @newValue
    	END
    END
    I still end up with the following errors:
    Code:
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'PROCEDURE'.
    Msg 155, Level 15, State 2, Line 7
    'INT' is not a recognized CURSOR option.
    Msg 137, Level 15, State 2, Line 8
    Must declare the scalar variable "@num1".
    Msg 137, Level 15, State 2, Line 9
    Must declare the scalar variable "@newValue".
    Hmmm.... Is there something that needs to be done prior to being able to create a stored proc? Does the fact that I'm using the express edition have anything to do with it?

    Thanks for any insight.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20080516  See http://www.dbforums.com/showthread.php?t=1630388
    
    IF Object_Id('usp_test') IS NOT NULL DROP PROCEDURE usp_test
    GO
    --  ptp  20080516  See http://www.dbforums.com/showthread.php?t=1630388
    
    CREATE PROCEDURE usp_test (
       @num1       INT
    ,  @num2       INT
    ,  @result     INT OUTPUT
    ) AS
    
    SET @result = @num1 + @num2
    RETURN
    GO
    
    DECLARE @i     INT
    
    EXECUTE usp_test 3, 5, @i OUTPUT
    
    SELECT @i
    -PatP

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    All variable names need to have "@" as the first character in T-SQL.
    Also, the first word in the batch must be create.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dbguyfh
    I can't, for the life of me, find an example of a stored procedure that does the following in SQL Server:
    - Multi-lined
    - Contains both input and output parameters
    - Sets one of the output parameters within the stored procedure
    Then you haven't bothered looking in Books Online, as there are several example of sprocs with output parameters in there.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by Pat Phelan
    Code:
    --  ptp  20080516  See http://www.dbforums.com/showthread.php?t=1630388
    
    IF Object_Id('usp_test') IS NOT NULL DROP PROCEDURE usp_test
    GO
    --  ptp  20080516  See http://www.dbforums.com/showthread.php?t=1630388
    
    CREATE PROCEDURE usp_test (
       @num1       INT
    ,  @num2       INT
    ,  @result     INT OUTPUT
    ) AS
    
    SET @result = @num1 + @num2
    RETURN
    GO
    
    DECLARE @i     INT
    
    EXECUTE usp_test 3, 5, @i OUTPUT
    
    SELECT @i
    -PatP
    Thank you!!!

    That worked perfectly and was exactly what I was looking for

  8. #8
    Join Date
    Jan 2008
    Posts
    186
    BTW: Is it really necessary to include the word "OUTPUT" in the execute statement?
    Code:
    EXECUTE usp_test 3, 5, @i OUTPUT
    Removing it doesn't seem to produce any errors
    Code:
    EXECUTE usp_test 3, 5, @i

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Only if you want an output

Posting Permissions

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