subquery error - 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

SQL Server 2008 Forums

Transact-SQL (2008)

subquery error

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2013-11-11 : 18:49:32

Hi Folks,

i am trying to create a SP and and need to get below right first, following statements works fine till 1 record as i would like to return more than one record and display the match and not match.
The below doesnt work and ends up in error SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

DECLARE @MyVar nvarchar(max);
SET @MyVar = (select NAMEEMPL from SAMINC.dbo.ARSAP INNER JOIN dbo.Users ON NAMEEMPL = User_FirstName + ' ' + User_LastName Collate Latin1_General_CI_AS);
If (@MyVar = '')
Print 'No user name match, please create valid users matching from Sage ERP 300 to Sage CRM'
Else
Print 'user name match from Sage ERP 300 to Sage CRM: ' + @MyVar
GO

what i want to return from above is all users match with their user name and which users that were not matched.

please advise.

regards

ppatel112
Starting Member

35 Posts

Posted - 2013-11-11 : 23:21:38

Hi There,

i have changed the sql to below now:
DECLARE @MyVar nvarchar(max);
SELECT @MyVar = NAMEEMPL from SAMINC.dbo.ARSAP INNER JOIN dbo.Users ON NAMEEMPL = User_FirstName + ' ' + User_LastName Collate Latin1_General_CI_AS where NAMEEMPL IS NOT NULL
If (@MyVar = '')
Print 'No user name match, please create valid users matching from Sage ERP 300 to Sage CRM'
Else
Print 'User Matched from Sage ERP 300 to Sage CRM:' + CHAR(13) + @MyVar
GO

this works fine but instead of 2 names to be returned it just returns me one.

ppatel112
Starting Member

35 Posts

Posted - 2013-11-11 : 23:54:31

I fixed this by below:

DECLARE @MyVar nvarchar(max);
SELECT @MyVar = COALESCE(@MyVar + CHAR(13),'') + NAMEEMPL from SAMINC.dbo.ARSAP INNER JOIN dbo.Users ON NAMEEMPL = User_FirstName + ' ' + User_LastName Collate Latin1_General_CI_AS
If (@MyVar = '')
Print 'No user name match, please create valid users matching from Sage ERP 300 to Sage CRM'
Else
Print 'User Matched from Sage ERP 300 to Sage CRM:' + CHAR(13) + @MyVar
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources