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

    Unanswered: How to test stored procedure with output parameters in Management Studio

    I have this SP:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[GetSessionInformation]
    @CustomerID int,
    @Success bit OUTPUT,
    @Email VarChar(55) OUTPUT,
    @FirstName VarChar(55) OUTPUT,
    @LastName VarChar(50) OUTPUT,
    @PhoneNumber VarChar(50) OUTPUT,
    @CompanyName VarChar(50) OUTPUT
    AS

    SET NOCOUNT ON

    DECLARE @UserKey AS int

    SELECT @CustomerID = CustomerID
    FROM Customers
    WHERE CustomerID = @CustomerID

    IF @CustomerID IS NULL
    BEGIN
    SET @Success = 0
    END
    ELSE
    BEGIN
    SET @Success = 1
    END

    BEGIN

    SELECT customerID, Email, FirstName, LastName, PhoneNumber, CompanyName
    FROM Customers
    WHERE CustomerID = @UserKey

    How do I test it in management studio?

    When I run a EXECUTE GetSessionInformation 56

    I get this error:
    Procedure 'GetSessionInformation' expects parameter '@Success', which was not supplied.

    Thanks for any help!

  2. #2
    Join Date
    Apr 2004
    Posts
    64
    You need to pass variables for your output parameters. All parameters not defined optional need to be passed.

    DECLARE @Success bit,
    @Email VarChar(55),
    @FirstName VarChar(55),
    @LastName VarChar(50),
    @PhoneNumber VarChar(50),
    @CompanyName VarChar(50)

    EXECUTE GetSessionInformation 56, @Success OUTPUT, @Email OUTPUT, @FirstName OUTPUT, @LastName OUTPUT, @PhoneNumber OUTPUT, @CompanyName OUTPUT

  3. #3
    Join Date
    Dec 2003
    Posts
    6

    Thumbs up

    Cool thanks TechnicalAli!

Posting Permissions

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