SP with 4 selects using union all getting error

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

SQL Server 2005 Forums

Transact-SQL (2005)

SP with 4 selects using union all getting error

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-08-27 : 10:53:34

I am using 4 select queries using union all, getting error saying incorrect syntax near union.

but when i am using just 2 select statements it is working is there a limit with union all, i am really confued. i could'nt find any mistake the way i wrote the select statements.

please help:



Alter PROCEDURE [dbo].[USP_GetDMTree] 
		@UserName			nvarchar(50),
		@ContractID			int
 AS

DECLARE @ErrorCode				int

Declare @projid					int

Declare @progid					int

Set @progid = (Select top 1 progid from TAB_ccsNetContracts where contractid=@ContractID and deleted='0')


Set @projid = (Select top 1 projid from TAB_ccsNetContracts where contractid=@ContractID and deleted='0')


SET NOCOUNT ON
SELECT @ErrorCode = @@Error
IF @ErrorCode = 0
BEGIN

SELECT DISTINCT DMID, DMTitle, '' as AccessMode FROM TAB_ccsNetDM where agency='1' and deleted = '0'

union all

	-- get the DM rows with userid and contractid
	SELECT DISTINCT A.DMID, A.DMTitle, B.AccessMode FROM TAB_ccsNetDM AS A join TAB_ccsNetUserAccess AS B ON
		A.ContractID = B.ContractID
	WHERE B.UserName = @UserName AND B.ccsNetModule = 'DM' AND A.ContractID=@ContractID AND A.Deleted = '0' AND B.Deleted = '0' ORDER BY A.DMID

union all

	-- get the DM rows with userid and progid
	SELECT DISTINCT A.DMID, A.DMTitle, B.AccessMode FROM TAB_ccsNetDM AS A join TAB_ccsNetUserAccess AS B ON
		A.ProgID = B.ProgID AND A.ContractID = 0
	WHERE B.UserName = @UserName AND B.ccsNetModule = 'DM' AND A.ProgID=@progid AND A.Deleted = '0' AND B.Deleted = '0' ORDER BY A.DMID


union all

	-- get the DM rows with userid and projid
	SELECT DISTINCT A.DMID, A.DMTitle, B.AccessMode FROM TAB_ccsNetDM AS A join TAB_ccsNetUserAccess AS B ON
		A.ProjID = B.ProjID AND A.ContractID = 0
	WHERE B.UserName = @UserName AND B.ccsNetModule = 'DM' AND A.ProjID=@projid AND A.Deleted = '0' AND B.Deleted = '0' ORDER BY A.DMID



	SELECT @ErrorCode = @@Error
END
SET NOCOUNT OFF
RETURN @ErrorCode

Thank you very much for the helpful info.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-27 : 10:56:40

Remove the Order By clause wihtin each select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources