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.