CODEWITHSUNDEEP
sqlsql-server-2008
Mathematics
Mathematics

Reputation: 7618

How to put select statement returned into a variable

I have this stored procedure,

Create procedure [dbo].[Craping_GetFruitCrapedOrNot]
(
    @FruitID int,
    @CrateID int,
    @FruitName varchar(100)
)
AS
  SELECT CASE WHEN EXISTS
  (
     SELECT     Craping.CRAPED
     FROM         Fruits 
          INNER JOIN Craping 
          ON Fruits.ID = Craping.FruitID
     WHERE     
         (Fruits.FruitTable = @FruitName) 
         AND (Craping.FruitID = @FruitID) 
         AND (Craping.CrateID = @CrateID)
  ) 
  THEN 'False'
  ELSE 'True'
  END AS FruitNotCraped
GO

Now if exists then instead of returning false THEN 'False' I want to return whatever returned from select statement, not sure how though?

Then return SELECT     Craping.CRAPED
         FROM         Fruits 
              INNER JOIN Craping 
              ON Fruits.ID = Craping.FruitID
         WHERE     
             (Fruits.FruitTable = @FruitName) 
             AND (Craping.FruitID = @FruitID) 
             AND (Craping.CrateID = @CrateID)

Upvotes: 1

Views: 74

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

 DECLARE @Craped NVARCHAR(50)

 SELECT     @Craped = CONVERT(NVARCHAR(50), Craping.CRAPED)
 FROM         Fruits 
      INNER JOIN Craping 
      ON Fruits.ID = Craping.FruitID
 WHERE     
     (Fruits.FruitTable = @FruitName) 
     AND (Craping.FruitID = @FruitID) 
     AND (Craping.CrateID = @CrateID)

 SELECT CASE
            WHEN @@ROWCOUNT = 0 THEN 'True'
            ELSE @Craped
        END AS [FruitNotCraped]

Notes:

  • I tested for @@ROWCOUNT instead of @Craped IS NULL just in case it is possible that the Craping.CRAPED field could be NULL as a valid value.

Upvotes: 3

Related Questions