SQL Server - Inject values from outer query into subquery (without using a function)

Question

I have this query:

DECLARE @Names VARCHAR(8000) 

SELECT [Number],[ErrorMessage], 
      (SELECT @Names = COALESCE(@Names + ',','') + [Name]
                            FROM [test].[dbo].[b]
                            WHERE [ErrorId] like '%' + CONVERT(NVARCHAR(20),Number) + '%')
                            )                   
                     as [ErrorHandlingName]
  FROM [test].[dbo].[a]

My goal is to send each Number into the subquery so the condition WHERE [ErrorId] like '%' + CONVERT(NVARCHAR(20),Number) + '%') would be able to use it.

P.S I need to do it without wrapping the subquery with a function

Can someone assist ?

Can you give us more info about the tables? Where the colums lie etc? Have a look at creating a derived table and joining to it, rather than using the sub query in the select. — Keith, May 15 '17 at 14:29
You are describing how you think the solution should look like, but you forgot to explain what you want to do. You are also using confusing terminology (inject values ?), and use very unusual constructs (subqueries in SELECT ?) — Panagiotis Kanavos, May 15 '17 at 14:29
Are you trying to *concatenate* error names? You'll find a lot of explanations and duplicate SO questions on how to do this — Panagiotis Kanavos, May 15 '17 at 14:31
If you are using multiple values in your subquery, you are going to get an error returned that you cannot have multiple values in your subquery. I think you may need to reassess your approach here. — Jacob H, May 15 '17 at 14:34
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Panagiotis Kanavos, May 15 '17 at 14:35
Apart from the XML-based techniques in the duplicate question, SQL Server 2017 will have a `STRING_AGG` function — Panagiotis Kanavos, May 15 '17 at 14:36
When you add alias to a sql query, you allow subqueries to have access to the vars of your aliased table. For example `SELECT (SELECT id FROM sec_userRole AS role WHERE usr.id = role.user_id) FROM sec_user AS usr`. — Omar Yafer, May 15 '17 at 14:37

score 1 · Accepted Answer · answered May 15 '17 at 14:36

1

Will this work?

SELECT a.[Number],a.[ErrorMessage], 
      (SELECT COALESCE(@Names + ',','') + b.[Name]
                            FROM [test].[dbo].[b] b
                            WHERE b.[ErrorId] like '%' + CONVERT(NVARCHAR(20),a.Number) + '%')
                            )                   
                     as [ErrorHandlingName]
  FROM [test].[dbo].[a] a

answered May 15 '17 at 14:36

Mazhar

3,797
1
12
29

Hi, I'm getting this error : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – ohadinho May 15 '17 at 14:44
Not easy when there's no sample data to test against, If you can provide some sample data then I can review my answer – Mazhar May 15 '17 at 14:46

score 0 · Answer 2 · answered May 15 '17 at 14:36

0

SELECT [Number],[ErrorMessage], 
      [ErrorHandlingName] = STUFF(( 
                                SELECT ',' + [Name] AS [text()]
                                FROM [test].[dbo].[b]
                                WHERE [ErrorId] like '%' + CONVERT(NVARCHAR(20),Number) + '%'
                                FOR XML PATH ('')
                            ), 1, 1)                   
  FROM [test].[dbo].[a]

answered May 15 '17 at 14:36

Mikhail Lobanov

2,976
9
24

This doesn't work. Number doesn't affect the subquery – ohadinho May 15 '17 at 14:46

SQL Server - Inject values from outer query into subquery (without using a function)

2 Answers2