June 24, 2010 at 3:17 pm
As the title says, I can't seem to get the count of a CTE into a variable. Below is an example of what I am trying to do. I can get it to return the could of the rows in the CTE using SELECT but I can't seem to store than value in a variable.
Also, please don't suggest other methods to count or whatever, I'm just using this method as an example of what I'm trying to do. Main goal is to store the count(*) of a CTE into a variable, and use that variable in a if statement.
--First check that table is not already made and get rid of it if it is
if OBJECT_ID('tempdb..#MyTemp') is not null
DROP TABLE #MyTemp
--Make temp table for test data
CREATE TABLE #MyTemp
(
PersonID INT,
DisplayName nvarchar(50),
SIN INT
)
--Load test data into temp table
INSERT INTO #MyTemp
SELECT 1,'Jones,Bob',123456 UNION ALL
SELECT 2,'Jones,Bob',123456 UNION ALL
SELECT 3,'Bond,James',456456 UNION ALL
SELECT 4,'Parker,Sally',321654
;
--Declare my CTE
WITH MY_CTE AS
(
SELECT
SIN
FROM
#MyTemp
GROUP BY
SIN
HAVING
count(SIN) > 1
)
--Declare variable to hold count of CTE rows
DECLARE @duplicates INT
SET @duplicates = (SELECT * FROM MY_CTE)
--Return message to the user if duplicates exist
IF (@duplicates>0)
(BEGIN PRINT 'Duplicates exist' END)
ELSE
(BEGIN PRINT 'Table has unique SINs' END)
DROP TABLE #MyTemp
June 24, 2010 at 3:32 pm
OK, nm I solved it. Just used
SELECT @duplicates = (SELECT * FROM MY_CTE)
to set the value of @duplicates
--First check that table is not already made and get rid of it if it is
if OBJECT_ID('tempdb..#MyTemp') is not null
DROP TABLE #MyTemp
--Declare variable to hold count of CTE rows
DECLARE @duplicates INT
--Make temp table for test data
CREATE TABLE #MyTemp
(
PersonID INT,
DisplayName nvarchar(50),
SIN INT
)
--Load test data into temp table
INSERT INTO #MyTemp
SELECT 1,'Jones,Bob',123456 UNION ALL
SELECT 2,'Jones,Bob',123456 UNION ALL
SELECT 3,'Bond,James',456456 UNION ALL
SELECT 4,'Parker,Sally',321654
;
--Declare my CTE
WITH MY_CTE AS
(
SELECT
SIN
FROM
#MyTemp
GROUP BY
SIN
HAVING
count(SIN) > 1
)
--Set variable with value of rows of CTE
SELECT @duplicates = (SELECT count(*) FROM MY_CTE)
IF (@duplicates>0)
BEGIN PRINT 'Duplicates exist' END
ELSE
BEGIN PRINT 'Table has unique SINs' END
DROP TABLE #MyTemp
June 25, 2010 at 8:45 am
IF EXISTS
(
SELECT *
FROM (
SELECT *,
rn = ROW_NUMBER() OVER (
ORDER BY [SIN])
FROM #MyTemp
) CTE
WHERE rn > 1
)
BEGIN
PRINT 'Duplicates exist';
END
ELSE
BEGIN
PRINT 'Table has unique SINs';
END;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply