0

My stored procedure need to return 5 different values and want to use those value in .NET. Stored procedure

CREATE PROCEDURE GET_NAME
(
  @COUNT INT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE NAME1 AS VARCHAR(100)
    DECLARE NAME2 AS VARCHAR(100)
    DECLARE NAME3 AS VARCHAR(100)

    SET @NAME1 = (SELECT DRIVER FROM TABLE1 WHERE @COUNT = 10)
    SET @NAME2 = (SELECT DRIVER FROM TABLE2 WHERE @COUNT = 10)
    SET @NAME3 = (SELECT DRIVER FROM TABLE3 WHERE @COUNT = 10)
     
    SELECT @NAME1
    SELECT @NAME2
    SELECT @NAME3
END

Now at .NET, I know ExecuteDataset function can use to retrieve multiple data, but I don't know how to get into three different variables as stored procedure returning 3 different output. Desired output I need is .NET variables store values from sp.

If someone knows, please help me. Thank you

Nishant Gupta
  • 3,533
  • 1
  • 11
  • 18
clark
  • 65
  • 5
  • Which dbms are you using? (MS SQL Server?) – jarlh Apr 26 '22 at 12:17
  • Microsoft Sql server – clark Apr 26 '22 at 12:17
  • I mean. You could use a dataset when you're calling the proc and fill that OR a quick thing would be simply return the three variables as one table with three columns and map it to an object? – JamesS Apr 26 '22 at 12:20
  • Either use a single select statement to return all three as a result set or setup output parameters for each value you want returned. – Sean Lange Apr 26 '22 at 13:07

1 Answers1

1
CREATE PROCEDURE GET_NAME
  @COUNT INT,
  @NAME1 VARCHAR(100) OUTPUT,
  @NAME2 VARCHAR(100) OUTPUT,
  @NAME3 VARCHAR(100) OUTPUT
AS
BEGIN

        SET NOCOUNT ON;
    
        SET @NAME1 = (SELECT DRIVER FROM TABLE1 WHERE @COUNT = 10)
        SET @NAME2 = (SELECT DRIVER FROM TABLE2 WHERE @COUNT = 10)
        SET @NAME3 = (SELECT DRIVER FROM TABLE3 WHERE @COUNT = 10)
    
END
SQLpro
  • 3,994
  • 1
  • 6
  • 14