stored procedure seems to only return last row of result set

[spowel4]

I'm new to stored procedures and, really anything about sql beyond the basic SELECT/INSERT/etc... so this is probably just something I don't understand but my problem is that my stored procedure seems to only return the last row of the result set (i.e. if it should return 10 items it only shows the last one (number 10)). When I run the stored procedure in the query analyzer it runs without any errors but it only displays the last item; here's the procedure (this is MS SQL Server 2008R2 if that makes any difference):

USE [NISUSInvyControl]
GO
/****** Object:  StoredProcedure [dbo].[NISUS_GetEDIDocTypes]    Script Date: 11/16/2011 20:30:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NISUS_GetEDIDocTypes] 
	@pEntityType int,
	@pEntityID char(10),
	@pEdxDocType varchar(10) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT @pEdxDocType = EDXDOCTYPE FROM [dbo].[EDI_EntityDocCrossref] WHERE (EDXENTITYTYPE = @pEntityType) AND (EDXENTITYID = @pEntityID)
END

[BitBlt]

It is because you are assigning a scalar variable as output. If you want to get the whole set, just change your stored proc as follows:

alter PROCEDURE [dbo].[NISUS_GetEDIDocTypes] 
	@pEntityType int,
	@pEntityID char(10)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT EDXDOCTYPE 
	FROM [dbo].[EDI_EntityDocCrossref] 
	WHERE (EDXENTITYTYPE = @pEntityType) 
	AND (EDXENTITYID = @pEntityID)
END

That way the return value of the stored procedure will be a rowset rather than a single scalar value.

Hope this helps! Good luck!

[spowel4]

Thanks BitBit but I realized I left out some important information, I apologize. I'm calling the procedure from a c# program & I need to be able to process all the values that the select statement returns, so I need it to return all the values to the program.

[adam_k]

This doesn't change what you've got to put in the stored procedure. It just might affect the way you handle it from your program. Make the changes that BitBit indicated and if you have a problem with your program open a thread in the appropriate section of the forum.