January 16, 2008 at 9:54 am
when using multiple unions get Only the first query in a UNION statement can have a SELECT with an assignment
--------------------------------------------------------------------------------
When I try to run the following code I get an error message that Server: Msg 8122, Level 16, State 1, Only the first query in a UNION statement can have a SELECT with an assignment
PROCEDURE [dbo].[KB_Search] @userid varchar(30), @groups varchar(250),
@opco varchar(50), @SearchText varchar(8000),
@TYPE varchar(50) output, @TITLE varchar(30) output,
@BRIEF varchar(50) output,@RANK varchar(10) output AS
--BEGIN
DECLARE @SelStatement varchar(8000)
DECLARE @SearchWord2 varchar(255)
DECLARE @SEARCHWORD varchar(255)
SET @SEARCHWORD = RTRIM(ltrim(@SearchText))
------------------------------------------------------------------------------
---- this section will is for sending back data to search screen
------------------------------------------------------------------------------
BEGIN
-- this will select on the Key words FTS field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= K.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) K
WHERE [T334].C1 = K.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
UNION ALL
-- this will select on the Type field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= Y.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C777700001, @SEARCHWORD) Y
WHERE [T334].C1 = Y.
and (@opco = T338.C536870913 or @groups = T338.C536870913)
UNION ALL
-- this will select on the brief description field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= B.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C777700003, @SEARCHWORD) B
WHERE [T334].C1 = B.
and (@opco = T338.C536870913 or @groups = T338.C536870913)
END
When I have only one select it works fine. This error message does not show up to frequently on the web so can anybody seen this or knows how to solve it I would deeply Appreciate your help. I am new to ms SQLerver all my other work was done in Oracle Thanks, Jerry
January 16, 2008 at 12:16 pm
you can only set variables in the first select of your union-select
BTW how many rows do you expect from your select statement.
Maybe you're better off returning a rowset.
PROCEDURE [dbo].[KB_Search] @userid varchar(30), @groups varchar(250),
@opco varchar(50), @SearchText varchar(8000),
@TYPE varchar(50) output, @TITLE varchar(30) output,
@BRIEF varchar(50) output,@RANK varchar(10) output AS
--BEGIN
DECLARE @SelStatement varchar(8000)
DECLARE @SearchWord2 varchar(255)
DECLARE @SEARCHWORD varchar(255)
SET @SEARCHWORD = RTRIM(ltrim(@SearchText))
------------------------------------------------------------------------------
---- this section will is for sending back data to search screen
------------------------------------------------------------------------------
BEGIN
-- this will select on the Key words FTS field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= K.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) K
WHERE [T334].C1 = K.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
UNION ALL
-- REMOVED THE @TYPE = , ...
-- this will select on the Type field
SELECT [T334].C777700001,[T334].C536870929,
[T334].C777700100, Y.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C777700001, @SEARCHWORD) Y
WHERE [T334].C1 = Y.
and (@opco = T338.C536870913 or @groups = T338.C536870913)
UNION ALL
-- this will select on the brief description field
SELECT [T334].C777700001,[T334].C536870929,
[T334].C777700100, B.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C777700003, @SEARCHWORD) B
WHERE [T334].C1 = B.
and (@opco = T338.C536870913 or @groups = T338.C536870913)
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply