C#Scripting [SOLVED]: SQL Server: return the result of a select as a result of stored procedure execution

C#Scripting [SOLVED]: SQL Server: return the result of a select as a result of stored procedure execution

Home Forums Scripting C# Tutorials C#Scripting [SOLVED]: SQL Server: return the result of a select as a result of stored procedure execution

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #132782

    Cloudy Point
    Keymaster

    QuestionQuestion

    I have a stored procedure that is called from C#:

    CREATE PROCEDURE [MySP] 
        @ID int,
        @Exists bit OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
    
        SET @Exists = 0
    
        SELECT TOP 1 ID 
        FROM MyTable 
        WHERE ID = @ID
    
        IF @@ROWCOUNT = 0
        BEGIN
            SELECT b.*
            FROM AnotherTable b
            INNER JOIN AnotherTable2 c ON b.ID = c.ID
            WHERE b.ID = @ID            
       END
       ELSE
       BEGIN
           SET @Exists = 1
    
           SELECT TOP 0 NULL
       END
    END
    

    IF @ID does not exist in table MyTable, then I return the SELECT b.*, otherwise if @ID exists in the table, then I return no rows

    The problem is that when @ID exists in the table, the stored procedure returns two tables as a result to C#, the one from SELECT TOP 1 and the one from SELECT b.* and I only want to return SELECT b.* so how can I do this?

    #132783

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    Use exists for this:

    CREATE PROCEDURE [MySP] 
        @ID int,
        @Exists bit OUTPUT
    AS
    BEGIN
    
        SET NOCOUNT ON;
    
       SET @Exists = 0
    
    
       IF EXISTS(SELECT TOP 1 ID FROM MyTable WHERE ID = @ID)
       BEGIN
            SELECT b.*
            From   AnotherTable b
                   INNER JOIN AnotherTable2 c on b.ID = c.ID
            Where  b.ID = @ID            
       END
       ELSE
    
       BEGIN
           SET @Exists = 1
           SELECT TOP 0 NULL
       END
    END
    

    Source: https://stackoverflow.com/questions/44590819/sql-server-return-the-result-of-a-select-as-a-result-of-stored-procedure-execut
    Author: Romano Zumbé
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.