Declare @CategoryID as int
SET @CategoryID = CASE WHEN EXISTS(SELECT 1
FROM Categories
WHERE Categoryname = @CategoryName)
THEN 1 ELSE 0
END
Another way would be something like ….
IF EXISTS (SELECT 1
FROM Categories
WHERE Categoryname = @CategoryName)
BEGIN
SET @CategoryID = 1;
END
ELSE
BEGIN
SET @CategoryID = 0;
END