SQL StoredProcedure - Return value, depend from the query.

Question

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#?

Answer 1

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.

Answer 2

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.