SQL StoredProcedure - Return value, depend from the query.

Question

SQL StoredProcedure - Return value, depend from the query.

Markus Freitag 3,791

Hello,
USE [MeineDatenbank]
GO
/****** Object: StoredProcedure [dbo].[GetOrders] Script Date: 12.01.2022 14:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetOrders]
@ProductName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

  SELECT        [Order].Name AS Auftrag, [Order].Quantity AS Menge  
FROM            [Order] INNER JOIN  
                         OrderProduct ON [Order].OrderId = OrderProduct.OrderId  
WHERE             [OrderProduct].Product = @ProductName  
  
return 4712  
 --Select @TotalCount = COUNT(ID) from my own query  
END

The return value should give me back the number of records found. How can I achieve this.

-1 nothing found
0 query ok
1 ...n foundes records

Can I define the name of the return myself? If yes, how?

164270-test-script-sql.txt

How do I query this correctly in C#?

Accepted answer

1 additional answer

Your answer

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

While you can use the return value to return the number of rows, or some other piece of data, the return code is conventionally used to return error/success information with 0 meaning success and anything else means error.

So your code should either be a result set or an output parameter.

Then again, if you are returning the full result set, there is little point in returning the row count. You can easily retrieve the row count in your C# code.

Markus Freitag 3,791 Reputation points

2022-01-13T10:53:07.99+00:00

You can easily retrieve the row count in your C# code.

ok, can you make a short sample for me. Thanks.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-01-13T22:00:14.707+00:00

There are many ways to do it, all depending on how you consume your result set.

If you read it into a DataTable, there is a Count property on the Rows property.

If you just use a plain SqlDataReader, maybe you are adding the the data to a collection? If so, that collection will have a Count or a Length property.

And you can always increment a variable as you move through the reader...

Answer 2

Olaf Helper 47,516

Can I define the name of the return myself? If yes, how?

When you use an OUTPUT parameter, then yes, you can name it as you like, see Return data using an output parameter

What you used is the return code, you get it when you assign it to a variable, see the second example.

Markus Freitag 3,791

Hello Olaf,
Thanks, but for me is not clear.
How do I need to extend the code?

DECLARE @ret_code int;

Where do i have to write this in?

SELECT        [Order].Name AS Auftrag, [Order].Quantity AS Menge
    FROM            [Order] INNER JOIN
                             OrderProduct ON [Order].OrderId = OrderProduct.OrderId
    WHERE             [OrderProduct].Product = @ProductName

From which the number of records found should be returned? Then I have to query this out in C#.

Can you expand my StoredProcedure on what this should look like? Thanks in advance.

USE [MeineDatenbank]
GO
/****** Object:  StoredProcedure [dbo].[GetOrders]    Script Date: 12.01.2022 15:41:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetOrders]
  @ProductName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  SELECT        [Order].Name AS Auftrag, [Order].Quantity AS Menge
FROM            [Order] INNER JOIN
                         OrderProduct ON [Order].OrderId = OrderProduct.OrderId
WHERE             [OrderProduct].Product = @ProductName

return 4712

END

Bruce (SqlWork.com) 78,161 Reputation points Volunteer Moderator

2022-01-16T22:36:06.43+00:00
Just

return @@rowcount

note: as you must read the rows before you can access the return value, you might as well just count the rows in the caller.

Share via

SQL StoredProcedure - Return value, depend from the query.

1 additional answer

Your answer