Solved

sql cte rowcount

Posted on 2011-03-16
2
897 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:doctor069
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35153120

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
 

Author Closing Comment

by:doctor069
ID: 35153191
Thanks - That is what I was missing!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 28
data lookup in Oracle - need suggestions 55 101
SQL Query 3 48
Using OPENQUERY in a SELECT statement 6 31
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now