Union problem 2

[chicdog]

I keep getting the error 'Incorrect syntax near the keyword 'UNION'. What I'm trying to do is do an Union between two result sets. (FYI The SELECT Statements match)

SET @WhereClause ='AND (dbo.tblAttendEvent.EventKey IN (' + @WhereClause + '))'

DECLARE @SQL Varchar(3000)

SET @SQL='SELECT ..............
WHERE dbo.tblMeetingAttendee.MeetingKey ='
SET @SQL = @SQL + CONVERT(VARCHAR,@Meetingkey) + WhereClause
EXEC (@SQL)

UNION ALL

SET @SQL='SELECT ..............
WHERE dbo.tblMeetingAttendee.MeetingKey ='

SET @SQL= @SQL + CONVERT(VARCHAR,@Meetingkey) + @WhereClause
EXEC(@SQL)

RETURN

Thanks,
Bryan

 

[sunila7]

Try this.

SET @WhereClause ='AND (dbo.tblAttendEvent.EventKey IN (' + @WhereClause + '))'

DECLARE @SQL Varchar(3000)

SET @SQL='SELECT ..............
WHERE dbo.tblMeetingAttendee.MeetingKey ='
SET @SQL = @SQL + CONVERT(VARCHAR,@Meetingkey) + WhereClause + ' UNION ALL '

SET @SQL=@SQL + ' SELECT ..............
WHERE dbo.tblMeetingAttendee.MeetingKey ='

SET @SQL= @SQL + CONVERT(VARCHAR,@Meetingkey) + @WhereClause
EXEC(@SQL)

RETURN

Sunil

 

[Glyndwr]

Chikey,


your sql statement including the union must be a single string

declare @sql varchar(8000)
set @sql = 'select a from tab1 UNION ALL select a from tabb'

Glyndwr