Mathematics Mathematics - 1 month ago 7
SQL Question

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)

Answer Source
 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.