Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql cte check if empty

Posted on 2011-04-27
2
Medium Priority
?
1,188 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:doctor069
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35480287
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
 

Author Closing Comment

by:doctor069
ID: 35480320
Yes the Select Case worked perfectly!

Thanks for your help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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