Results 1 to 2 of 2

Thread: sql output syntax problem

  1. #1
    Join Date
    Oct 2005
    Posts
    1

    sql output syntax problem

    I try to get the following formatted result in transact-sql but i get some errors that i will give along the lines.

    this is the required formatted report required:


    The status for the position : Marketing Manager
    Budgeted Strength : 100
    Current Strength : 83
    cRequisitionCode vRegion NoOfVacancy
    ----------------------------------------------

    00002 Texas 11

    from the to following tables:
    Position Requisition
    --------------- ---------------
    cPositionCode(pk) char(4) cRequisitionCode(pk) char(5)
    vDescription nvarchar(35) cPositionCode(fk) char(4)
    iBudgetedStrength int vRegion varchar(35)
    iCurrentStrength int siNoOfVacancy smallint


    the following result return the various value based on the the given position,
    i tried achieve this position by using a procedure.

    the 1rst of the procedure is as follow:

    CREATE PROC prcGetPositionDetail
    @Pcode char (4), @Description char (30) OUTPUT,
    @Budget int OUTPUT, @CurrentStrength int OUTPUT
    AS
    Begin
    If exists (select * from Position where cPositionCode = @Pcode)
    Begin
    Select @Description = vDescription
    @Budget = iBudgetedStrength
    @CurrentStrength = iCurrentStrength
    From Position
    Where cPositionCode = @Pcode
    Return 0
    End
    Else
    Return 1
    End.

    When i executed the procedure, as 'exec prcGetPositionDetail '0002'', the Query Analyser return an error message concerning the @Description. I by pass the following error by adding executing the following procedure
    'Exec prcGetPositionDetail '0002', output, output, output’.
    But then it give a second error message saying that it is not able to "convert a nvarchar to int ".

    This is the 2nd procedure that shall call the first one and display the desired format .


    CREATE PROC prcDisplayPositionStatus @Pcode char (4)
    AS
    BEGIN
    DECLARE @Description char(30)
    DECLARE @Budget int
    DECLARE @CurrentStrength int
    DECLARE @ReturnValue int
    EXEC @ReturnValue = prcGetPositionDetail @Pcode
    @Description OUTPUT,
    @Budget OUTPUT,
    @CurrentStrength OUTPUT
    IF (@ReturnValue = 0)
    BEGIN
    Print 'The Status for the Position: '
    + @Description
    Print 'Budgeted strength : '
    +CONVERT (char (30), @Budget)
    Print 'Current Strength : '
    +CONVERT (char (30), @CurrentStrength)
    SELECT cRequisitionCode, vRegion, siNoOfVacancy
    FROM Requisition
    WHERE cPositionCode = @Pcode
    End
    ELSE
    Print 'No records for the given Position code'
    END


    I wish that you help me to debug and optimize this Query.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You have to pass the OUTPUT variable to execute a stored procedure that returns values as OUTPUT parameters, which you have done in second procedure.

    Do you get the same error when you run prcDisplayPositionStatus

Posting Permissions

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