Member Avatar for 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
  • 3 Contributors
  • 3 Replies
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

As found, the stored-proc was writing into a single scalar OUTPUT variable inside a SELECT that returns multiple rows. In T‑SQL a statement like SELECT @v = col FROM table assigns repeatedly for each row, so the variable ends up holding only the last value. 's direction—have the procedure return a rowset instead of filling a scalar—is the correct approach, and is right that the client code must then read that rowset.

Client-side (C#) pattern to consume the rowset (ADO.NET): use CommandType.StoredProcedure and ExecuteReader, then iterate the reader. Example:

using(var conn = new SqlConnection(connString))
using(var cmd  = new SqlCommand("NISUS_GetEDIDocTypes", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@pEntityType", SqlDbType.Int).Value = entityType;
    cmd.Parameters.Add("@pEntityID", SqlDbType.VarChar, 10).Value = entityId;
    conn.Open();
    using(var rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
            string edxDocType = rdr.IsDBNull(0) ? null : rdr.GetString(0);
            // process each value
        }
    }
}

Troubleshooting tips: confirm the proc returns rows in SSMS; do not use ExecuteScalar (it returns only one value); ensure parameter types/sizes match the table (char is fixed-length and pads with spaces—use varchar or trim when needed); if you truly need a single string of all values, aggregate them in SQL (FOR XML PATH on SQL Server 2008R2 or STRING_AGG on SQL Server 2017+).

Recommended Answers

All 3 Replies

Member Avatar for 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!

Edited by BitBlt because: n/a
Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.