UNION and Stored Procedures

[jensies]

Hi,
I would like to return a value from a stored procedure. However, I am using UNION to join a lot of tables together and I keep getting an error:

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

Would anyone know how to do this?

Thanks,
Jenny

E.G.
CREATE PROCEDURE proced
@docId int
@@logProvider varchar (40) OUTPUT
AS

select @@logProvider=logProvider,
eventId
from tableA
where docId=@docId
union
select @@logProvider=logProvider,
eventId
from tableB
where docId=@docId
.....

 

[petersJazz]

I dont se why you select both logProvider and eventId. Try removing eventID to se if it works. I dont think SQL Server knows what to do with eventID in this case. Or you can put it into a variable as well: @event = eventID.

 

[jensies]

Thanks!
I actually have about 40 fields being selected....
I'll give the @eventId, etc a go and let you know if it is any help!

Jenny

 

[JulianTW]

Are you trying to return a recordset or set the value of a variable to be returned ? It looks like you may be trying to do both !

If you want to return a recordset you need to get rid of the '@@logProvider=' bit which allocates a value to a variable or parameter.

If you're trying to set the value of the parameter to be returned then I'm not sure why you're using unions which combine resultsets - variables are for single values.

Julian Watson

http://www.EurodataComputing.co.uk