Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Unanswered: Testing a stored procedure with output params in SQL Server Managment Studio

    I have an SP like this (edited for brevity):

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[TESTING_SP]
    @Username MediumText,
    @Password MediumText,
    @UserKey int OUTPUT,
    @RoleKey int OUTPUT,
    @UserGroupKey int OUTPUT,

    AS

    BEGIN

    SELECT
    @UserKey = UserKey
    FROM UserProfile
    WHERE Username = @UserName
    AND [Password] = @Password
    END

    I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error:

    Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0
    Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied.

    How do I set up the output parameters and then select the values in MS for testing purposes?

    Thanks a ton for helping a noob.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    declare @@ukey integer
    declare @@rkey integer
    declare @@ugkey integer
    
    exec TESTING_SP
      @Username = 'Todd'
    , @Password = 'Todd'
    , @UserKey = @@ukey OUTPUT
    , @RoleKey = @@rkey OUTPUT
    , @UserGroupKey = @@ugkey OUTPUT
    
    print @@ukey 
    print @@rkey 
    print @@ugkey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Posts
    6
    Cool, thank you very much for the input.

    -E

Posting Permissions

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