sql cte check if empty

Hi I have a cte that I need to check to see if it is empty before I pass it to a function; please see code below

This works fine as long as the cte (Photos) has data but my function crashes if it has no data.

Is there a way to check if the cte has rows before running my function?

I tried

SELECT * from Photos

IF @@ROWCOUNT = 0
BEGIN
    PRINT @@ROWCOUNT    
END
ELSE
SELECT [dbo].[SQLEncode]((SELECT * From  Photos), '34', '43') AS myResult;

But then it does not see the cte any more when I try to run the function...
Invalid object name 'Photos '

Thanks in advance



WITH Photos AS
(
SELECT ROW_NUMBER() OVER(Order by [CreatedDate] DESC) as RowNum 
      ,[id]        
      ,[CreatedDate]
      ,[ImageTitle]
      ,COUNT([id]) OVER () AS [totalRows]   
  FROM [dbo].[Photos]
  where   
    [UserId] = @Userid       
)

SELECT [dbo].[SQLEncode]((SELECT * From  Photos), '34', '43') AS myResult;

Open in new window

doctor069Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you cannot use the CTE more than once, only inline in the same statement ...

not sure if the below will work. otherwise, I would recommend the SQLEncode function to be "fixed" in the 0 rows regards
WITH Photos AS
(
SELECT ROW_NUMBER() OVER(Order by [CreatedDate] DESC) as RowNum 
      ,[id]        
      ,[CreatedDate]
      ,[ImageTitle]
      ,COUNT([id]) OVER () AS [totalRows]   
  FROM [dbo].[Photos]
  where   
    [UserId] = @Userid       
)
SELECT CASE WHEN (SELECT COUNT(*) FROM Photos) = 0 THEN NULL ELSE [dbo].[SQLEncode]((SELECT * From  Photos), '34', '43') END AS myResult;

Open in new window

0
 
doctor069Author Commented:
Yes the Select Case worked perfectly!

Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.