February 22, 2006 at 7:21 am
While trying to improve the performance of an SP I stumbled across an interesting problem. I thought I would post it here to find out if anyone else has run across this problem and if there is a way to make it work. I suspect its just a case of misleading errors or me interpreting the error wrong, but we'll see.
Here is the code I am executing:
CREATE TABLE #First (Number int)
CREATE TABLE #Second (Number int)
INSERT INTO #First (Number)
SELECT 1
UNION
SELECT 2
INSERT INTO #Second (Number)
SELECT 1
UNION
SELECT 3
DECLARE @Count int
SELECT @Count = COUNT(DISTINCT Number)
FROM #First
UNION
SELECT @Count = COUNT(DISTINCT Number)
FROM #Second
DROP TABLE #First
DROP TABLE #Second
Here is the error I get:
Server: Msg 8122, Level 16, State 1, Line 17
Only the first query in a UNION statement can have a SELECT with an assignment.
Too me, this error seems to imply I can have a SELECT with an assignment when using a UNION since it says only the first query in a UNION statement can do the assignment. Otherwise I would have expected the error to say I can't have a UNION statement and assignment in the same query. Let me know if I am misunderstanding this error.
So, I remove the assignment from the second query in my union like so:
SELECT @Count = COUNT(DISTINCT Number)
FROM #First
UNION
SELECT COUNT(DISTINCT Number)
FROM #Second
And execute again. Didn't surprise me to much to get the following error:
Server: Msg 141, Level 15, State 1, Line 23
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
I tried removing the assignment from the first query and put it back in the second one but got the same error.
I am trying to code out all derived tables and temp tables from my SP and then encountered this problem. Is there a way to do this without using derived tables and without temp tables? If not I will dump the value into a temp table using a SELECT INTO and then put it in my variable.
Have any of you seen this problem/errors with using a UNION and does anyone know a way to make it work?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 22, 2006 at 8:22 am
>>I am trying to code out all derived tables and temp tables from my SP
Why ?
Derived tables exist for a reason. Sometimes they are the optimal or only solution.
SELECT @Count = Number
FROM
(
SELECT COUNT(DISTINCT Number) As Number
FROM #First
UNION
SELECT COUNT(DISTINCT Number)
FROM #Second
) dt
February 22, 2006 at 8:40 am
Oh, I agree derived tables are useful, but in this SP they don't work since there is so much data that the entire derived table won't fit in memory.
The example I posted does not represent the actual tables being used. I simplified the queries to demonstrate what I found.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 22, 2006 at 2:11 pm
What value do you expect @Count to have if this was to work?
SELECT @Count = COUNT(DISTINCT Number)
FROM #First
UNION
SELECT @Count = COUNT(DISTINCT Number)
FROM #Second
Given your sample, if you expect 4 then:
SELECT @Count = sum(Number)
FROM
(
SELECT COUNT(DISTINCT Number) As Number
FROM #First
UNION ALL
SELECT COUNT(DISTINCT Number)
FROM #Second
) dt
If you expect 3 then:
SELECT @Count = COUNT(Number)
FROM
(
SELECT DISTINCT Number
FROM #First
UNION
SELECT Number
FROM #Second
) dt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply