SQL Procedure Help required - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Procedure Help required

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-03 : 08:07:33

Liju Thomas writes "I am very new to SQL Server, infact to Database Programming also.So It would be very helpful, if anybody can help me for this

I have written one stored Procedure which returns a set of values from two tables. here is the Stored Procedure :-

IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'SYNSelectFromSynSalary' AND TYPE = 'P')
DROP PROCEDURE SYNSelectFromSynSalary
GO
CREATE PROCEDURE SYNSelectFromSynSalary
@EmpID int OUTPUT,
@EmpRegionalCode char(10) OUTPUT,
@EmpName varchar(25) OUTPUT,
@EmpBand char(5) OUTPUT,
@Designation varchar(25) OUTPUT,
@NetSalary money OUTPUT
WITH ENCRYPTION
AS
SELECT @EmpID = A.EmpID, @EmpRegionalCode = A.EmpRegionalCode, @EmpName = A.EmpName,
@EmpBand = A.EmpBand, @Designation = A.Designation, @NetSalary = B.NetSalary
FROM
SYNEmployee A JOIN SYNSalaryDetails B
ON
A.EmpID = B.EmpID
AND
A.EmpRegionalCode = B.EmpRegionalCode
ORDER BY
A.EmpRegionalCode
GO

This should retrieve all the Employee ID ,Regional Code, name etc from two tables.

But after executing the above SP, Iam getting only the first row instead of 3 rows. Here is how i execute it

DECLARE @EmpID int,
@EmpRegionalCode char(10),
@EmpName varchar(25),
@EmpBand char(5),
@Designation varchar(25),
@NetSalary money
EXEC SYNSelectFromSynSalary @EmpID OUTPUT,@EmpRegionalCode OUTPUT,@EmpName OUTPUT,@EmpBand OUTPUT,@Designation OUTPUT,@NetSalary OUTPUT
IF @@ROWCOUNT = 0
PRINT ('No Data Found')
ELSE
BEGIN
SELECT RTRIM(CAST(@EmpID AS CHAR(2))), RTRIM(@EmpRegionalCode), RTRIM(@EmpName), RTRIM(@EmpBand),
RTRIM(@Designation), CONVERT(CHAR(10),@NetSalary)
PRINT (CAST(@@ROWCOUNT AS CHAR(2)) + ' Records Found')
END
GO

Here actually this query should return three rows, but i am getting only the first row. Is there any thing i need to do with the cursor for returning all the rows? If then, can anybody explain me, what is the use of cursor?"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 08:10:17

why are you returning column values via output paramteres????
that will result in the last record in the resultset.
varable can hold only one value at the time.

do just
SELECT A.EmpID, A.EmpRegionalCode, A.EmpName,
A.EmpBand, A.Designation, B.NetSalary
FROM
SYNEmployee A JOIN SYNSalaryDetails B
ON
A.EmpID = B.EmpID
AND
A.EmpRegionalCode = B.EmpRegionalCode
ORDER BY
A.EmpRegionalCode

and you should be ok.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources