Setting a variable from a multi-table Select

Question

Setting a variable from a multi-table Select

JaybeeSQL

Hall of Fame

Points: 3501
More actions
January 18, 2022 at 7:10 pm

#3974346
Hi all,
I have a variable to declare, and this works:
Declare @CopyFromUser Varchar(10) =
(Select Top 1 Op_Ref From [MyServername].[TemperTest_BATON].[dbo].Op Where Op_Forename+' '+Op_Surname = @CopyFromUserFull)
However, the Op_Ref could be in any combination of 5 databases with thankfully identical schema, so I need to select against them, the following is as far as I've got but is still erroring out, could use a hand 🙂
Declare @CopyFromUser Varchar(10) = (Select Top 1 * from
(Select Op_Ref From [MyServername].[TemperTest_BATON].[dbo].Op Where Op_Forename = 'Alice'
And Op_Surname = 'Cooper'
Union
Select Op_Ref From [MyServername].[TemperTest_SERGEI].[dbo].Op Where Op_Forename = 'Alice'
And Op_Surname = 'Cooper'
Union
Select Op_Ref From [MyServername].[TemperTest_PRO].[dbo].Op Where Op_Forename = 'Alice'
And Op_Surname = 'Cooper'
Union
Select Op_Ref From [MyServername].[TemperTest_C30].[dbo].Op Where Op_Forename = 'Alice'
And Op_Surname = 'Cooper'
Union
Select Op_Ref From [MyServername].[TemperTest_ROp].[dbo].Op Where Op_Forename = 'Alice'
And Op_Surname = 'Cooper'))

TIA
- This topic was modified 2 years, 3 months ago by JaybeeSQL.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3622 More actions · Answer 1

You will need an alias for the derived table containing the unions.

Just add "AS a" after the final close bracket/parenthesis. (Ironically the TIA from your message would probably have sufficed).

Without an alias, you will see this error message "Incorrect syntax near ')'."

This reply was modified 2 years, 3 months ago by Ed B.

ratbak Ten Centuries Points: 1334 More actions · Answer 2

What is the error?

I'd guess it's related to not having aliased the subquery -- e.g.,

Declare @CopyFromUser Varchar(10) = (Select Top 1 * from
                                    (Select Op_Ref From [MyServername].[TemperTest_BATON].[dbo].Op Where Op_Forename = 'Alice'
                                    And Op_Surname = 'Cooper'
                                    Union
                                    Select Op_Ref From [MyServername].[TemperTest_SERGEI].[dbo].Op Where Op_Forename = 'Alice'
                                    And Op_Surname = 'Cooper'
                                    Union
                                    Select Op_Ref From [MyServername].[TemperTest_PRO].[dbo].Op Where Op_Forename = 'Alice'
                                    And Op_Surname = 'Cooper'
                                    Union
                                    Select Op_Ref From [MyServername].[TemperTest_C30].[dbo].Op Where Op_Forename = 'Alice'
                                    And Op_Surname = 'Cooper'
                                    Union
                                    Select Op_Ref From [MyServername].[TemperTest_ROp].[dbo].Op Where Op_Forename = 'Alice'
                                    And Op_Surname = 'Cooper') Ops
                                    )

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 3

The alias indeed, but after the penultimate closing bracket 🙂

Cheers fellas

Michael L John One Orange Chip Points: 27042 More actions · Answer 4

Without an order by, are you sure you are getting the proper value? This looks like a bug waiting to happen.

Can more than one of the 5 selects return a value? If so, how are you determining the correct one to return?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Ed B Hall of Fame Points: 3622 More actions · Answer 5

JaybeeSQL wrote:

The alias indeed, but after the penultimate closing bracket 🙂
Cheers fellas

Yes! I forgot that the query was inside brackets.