CODEWITHSUNDEEP
sqlsql-servert-sql
Pawan Nogariya
Pawan Nogariya

Reputation: 8990

SQL - Variable assignment with other columns selection

I am trying to get the records as well as records count using common table expressions, but I want to store the records count in a variable and it will ofcourse give me the error

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I am trying something like this

declare @count int

;with allRecords as 
(
   -- query fetching all the records with many joins
),
recordsCount as 
(
    select count(*) as Total from allRecords
)
select allRecords.*, @count=recordsCount.Total from allRecords, recordsCount 
where -- multiple conditions

Is there any work around for this?

Actually the @count variable is an output variable of my stored procedure so I want to return the result as well as fill this @count variable

Upvotes: 1

Views: 777

Answers (3)

Jaloopa
Jaloopa

Reputation: 750

I'd use a temp table or table variable here. You can then do separate statements for select @count = count(*) from #allrecords and select * from #allrecords

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

You can't do it like this. If you want to get the number of rows the select statement returned into a variable you should use the built-in global variable @@ROWCOUNT:

DECLARE @count int

;WITH allRecords as 
(
   -- query fetching all the records with many joins
)

SELECT allRecords.*
FROM allRecords

SELECT @Count = @@ROWCOUNT

Update:

Well, in that case you have no choise that I'm aware of other then using a temporary table:

SELECT /* columns */ INTO #tempTableName 
-- rest of the select statement

SELECT @Count = COUNT(*) 
FROM #tempTableName

SELECT * 
FROM #tempTableName 
WHERE <conditions>

DROP TABLE #tempTableName

Upvotes: 1

xdd
xdd

Reputation: 545

declare @count int

;with allRecords as 
(
   -- query fetching all the records with many joins
)
select @count = count(*) as Total from allRecords

Upvotes: 0

Related Questions