CODEWITHSUNDEEP
c#sql-server-2008stored-procedures
Willy
Willy

Reputation: 10648

SQL Server: return the result of a select as a result of stored procedure execution

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?

Upvotes: 2

Views: 64

Answers (3)

Eklavya
Eklavya

Reputation: 36

The second result that you are getting is from the statement select top 0 null

Upvotes: 0

Romano Zumbé
Romano Zumbé

Reputation: 8079

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Just replace all the logic with:

SELECT b.*
From AnotherTable b INNER JOIN
     AnotherTable2 c
     ON b.ID = c.ID
WHERE b.ID = @ID AND
      NOT EXISTS (SELECT 1 FROM MyTable WHERE ID = @ID);

And, if you don't want duplicates, you might as well do:

SELECT b.*
From AnotherTable b 
WHERE b.ID = @ID AND
      EXISTS (SELECT 1 FROM AnotherTable2 c WHERE b.ID = c.ID) AND
      NOT EXISTS (SELECT 1 FROM MyTable WHERE ID = @ID);

Then, learn about table valued functions. If you want to return a table, then the best approach -- if it is feasible -- is a function, not a stored procedure. (Functions are more limited in their functionality, so this is not always possible.)

Upvotes: 3

Related Questions