Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raymon2683
Starting Member
5 Posts |
 Posted - 2005-10-20 : 11:23:27
|
| 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' ENDI wish that you help me to debug and optimize this Query. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Sql output problem