union problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

union problem

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2010-09-28 : 11:33:45

Hi all.
I create procedure


create procedure [dbo].[sp_GetUserName]
(
@BlankAll varchar(1)
,@Svc_No varchar(50)
)
as
Declare @SQLStr as NVarChar(4000)

Set @SQLStr = 'select
Visit_Id
,u.User_Id
,Cust_no+ '' - '' +LName+ '', '' +FName Patient
,Svc_No
from dbo.User u
join dbo.Visit v
on u.User_id = v.User_Id
where v.Svc_No = ''@Svc_No''
order by LName'

If @BlankAll = 0 OR @BlankAll = 2 Set @SQLStr = @SQLStr + ' Union Select 0, 0, ''Select User name ...'','''''
If @Svc_No is not null Set @SQLStr = @SQLStr + ' and Svc_No = ' + @Svc_No

But after ran I got error messages:
Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'Union'.
Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'and'.

How to fix a problem?

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-28 : 11:42:57

The ORDER BY clause has to be moved after the UNION statement.

eugz
Posting Yak Master

210 Posts

Posted - 2010-09-28 : 12:04:09

Thanks for replay.

After I modified by you suggestion like

select...
Union Select 0,0,'Select Patient ...',''
order by LName

I got error: Msg 104, Level 16, State 1, Line 1.
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Thanks.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-09-28 : 12:35:00

Your LName column is not selected as it is part of a compound column (Cust_no+ ' - ' +LName+ ', ' +FName User). In order to be able to order by it within a UNION, you need to include it as one of the columns in the SELECT.

Some days you're the dog, and some days you're the fire hydrant.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-28 : 14:53:44

why don't you post the whole thing...along with DDL for the tables...

and are you making this a view?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

eugz
Posting Yak Master

210 Posts

Posted - 2010-09-28 : 15:42:02

Hi X002548. Thanks for replay.

The first my post I modified to display the full code of my procedure. How to fix it?

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 16:54:39

So many things wrong here...

As said before, you still have to move the order by to the end.

Also, you are taking a character data type and throwing numbers at it. why?

This is just a pet peeve of mine and not really an issue, but why in the world would anyone ever use varchar(1)?

To debug your code, put a print statement at the end of the string building (which is bad in itself) to see the sql statement.

Finally, you still haven't posted the complete code. Or if you have, this proc doesn't do anything anyway

EDIT: and whats up with the 2 selects after the union...?

eugz
Posting Yak Master

210 Posts

Posted - 2010-09-28 : 17:10:03

Hi, thanks for replay.
Now the code looks

create procedure [dbo].[sp_GetUserName]
(
@BlankAll varchar(1)
,@Svc_No varchar(50)
)
as
Declare @SQLStr as NVarChar(4000)

Set @SQLStr = 'select
Visit_Id
,u.User_Id
,Cust_no+ '' - '' +LName+ '', '' +FName UserName
,Svc_No
from dbo.User u
join dbo.Visit v
on u.User_id = v.User_Id
where v.Svc_No = ''@Svc_No''
If @BlankAll = 0 Set @SQLStr = @SQLStr +
' Union 
Select 
0
,0
,''Select User name ...''
,''''
,''''
from dbo.User u
join dbo.Visit v
on u.User_id = v.User_Id
where u.Svc_No = ''@Svc_No'' 
order by LName'

Set @SQLStr = @SQLStr

print @SQLStr

Now no error but and result is nothing. The Select User name ... in second SELECT is default value. What is problem now?

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 17:46:39

whats result of print statement?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 17:47:48

this line where v.Svc_No = ''@Svc_No''

is missing closing quote

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 17:49:07

Please show the ENTIRE proc.

you still have about a million things wrong, plus the stuff we can't see.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-28 : 17:54:17

I agree with Russell. It's better if you explain what you're trying to do, rather than post the code.

My guess is that you want a query to return details about a visit, that include the Visit_ID, User_ID, and a combined column of Cust_no, last name and first name, for a particular service number. Is this correct?

Depending on the @Blank_All flag, you also want to include a blank row in the results. Correct? If so, why do you need a blank row?

You also want to order the results by Last name. You CANNOT do that in a UNION the way you've written the query so far, because last name has to appear separately (as has already been mentioned)

Lastly, you're generating a SQL statement instead of returning the results directly. Is this intended? Would you prefer a procedure that returns the results?

One comment: it is a bad practice to use "sp_" for stored procedure names. I recommend you remove it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources