Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL returning only one row Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 9:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 9:42 PM
Points: 6, Visits: 61
Hi There,

i have a query as below:

by running the sql i.e. select NAMEEMPL from SAMINC.dbo.ARSAP INNER JOIN dbo.Users ON NAMEEMPL = User_FirstName + ' ' + User_LastName Collate Latin1_General_CI_AS

it returns

NAMEEMPL

Bill Bhasion

Peter louder

I now want to use below to create my stored procedure for checking the values and returning it to the screen.

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
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 query runs fine but it returns only one row of name i.e.

User Matched from Sage ERP 300 to Sage CRM:
Peter Louder

the actual result should be:



User Matched from Sage ERP 300 to Sage CRM:

Bill Bhasion

Peter Louder

i am not sure what i am doing wrong - please advise.



regards
Post #1513325
Posted Monday, November 11, 2013 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 9:42 PM
Points: 6, Visits: 61
I fixed the issue by below query

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
Post #1513328
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse