sql cte rowcount

Hi I have a simple cte but I need to check to see if it returns any rows

Please see my code below. It seems that I can't put an "If" after the CTE. I get a syntax error

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       
)

IF @@ROWCOUNT = 0 
BEGIN
    PRINT 'No Photos' 
    
END
ELSE
   PRINT 'Has photos'

Open in new window

doctor069Asked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:

Thats because you have not run any executable statement yet
Here
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 * from Photos

IF @@ROWCOUNT = 0 
BEGIN
    PRINT 'No Photos' 
    
END
ELSE
   PRINT 'Has photos'

Open in new window

0
 
doctor069Author Commented:
Thanks - That is what I was missing!
0
All Courses

From novice to tech pro — start learning today.