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;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy