asked
Stored Procedure returns one row only
Hi.. I create a new stored procedure: CREATE PROC USP_TEST1 @CRNO int, @CRNO1 int OUTPUT, @CRDESC nvarchar(1000) OUTPUT, @SRNO int OUTPUT, @SRNAME nvarchar(1000) OUTPUT, @SRBNO int OUTPUT, @SRBCODE nvarchar(1000) OUTPUT, @SRBNAME nvarchar(1000) OUTPUT AS BEGIN SET NOCOUNT ON SELECT @CRNO1 = GRS.CRNO, @CRDESC = CRSES.CRDESC, @SRNO = STS.SRNO, @SRNAME = STS.SRNAME, @SRBNO = SRBTS.SRBNO, @SRBCODE = SRBTS.SRBCODE, @SRBNAME = SRBTS.SRBNAME FROM GRS INNER JOIN STS ON STS.SRNO = GRS.SRNO INNER JOIN SRBTS ON SRBTS.SRBNO = GRS.SRBNO INNER JOIN CRSES ON CRSES.CRNO = GRS.CRNO WHERE GRS.CRNO = @CRNO END GO The problem is when I execute this procedure, it will return one row only while it supposed to returns more than one row DECLARE @CRNO1 int DECLARE @CRDESC nvarchar(1000) DECLARE @SRNO int DECLARE @SRNAME nvarchar(1000) DECLARE @SRBNO int DECLARE @SRBCODE nvarchar(1000) DECLARE @SRBNAME nvarchar(1000) EXEC USP_TEST1 @CRNO = 1, @CRNO1 = @CRNO1 OUTPUT, @CRDESC = @CRDESC OUTPUT, @SRNO = @SRNO OUTPUT, @SRNAME = @SRNAME OUTPUT, @SRBNO = @SRBNO OUTPUT, @SRBCODE = @SRBCODE OUTPUT, @SRBNAME = @SRBNAME OUTPUT SELECT @CRNO1 AS CRNO, @CRDESC AS CRDESC, @SRNO AS SRNO, @SRNAME AS SRNAME, @SRBNO AS SRBNO, @SRBCODE AS SRBCODE, @SRBNAME AS SRBNAME How can I fix that
You are assigning the rows returned in the select to variables, this will overwrite the variables' contents each time a row is returned that matches the criteria You need to remove the variables from the stored procedure and just return the columns: CREATE PROC USP_TEST1 @CRNO int AS BEGIN SET NOCOUNT ON SELECT GRS.CRNO AS CRNO1, CRSES.CRDESC AS CRDESC, STS.SRNO AS SRNO, STS.SRNAME AS SRNAME, SRBTS.SRBNO AS SRBNO, SRBTS.SRBCODE AS SRBCODE, SRBTS.SRBNAME AS SRBNAME FROM GRS INNER JOIN STS ON STS.SRNO = GRS.SRNO INNER JOIN SRBTS ON SRBTS.SRBNO = GRS.SRBNO INNER JOIN CRSES ON CRSES.CRNO = GRS.CRNO WHERE GRS.CRNO = @CRNO END You then get the rows returned as expected. DECLARE @CRNO1 int EXEC USP_TEST1 @CRNO = 1 If you are calling this from a program and expect the variables to be filled, then you will need to change that to call the sproc and fill a datatable (or something similar, depending upon language).
@Sanfoor - If @WilliamD's answer has helped, then please mark it as the right answer. There's a tick for you to click at the top of his answer... He gets a karma boost (not that he needs it ;-) ), you get one too, and it lets others know that your question was answered satisfactorily. And might help others with the same problem quickly find the right solution!
