SQL StoredProcedure - Return value, depend from the query.

Markus Freitag 3,791 Reputation points
2022-01-12T13:52:53+00:00

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

164307--return-8.png

Developer technologies | Transact-SQL
SQL Server | Other
Developer technologies | C#
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-01-12T22:32:00.227+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 47,516 Reputation points
    2022-01-12T14:28:31.673+00:00

    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.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.