Query database using result from previous query and combining them

Question

I am using dynamic sql, to query the database:

    DECLARE @VALUE VARCHAR(5000);
    SET @VALUE = '123'
    DECLARE @SQL5 NVARCHAR(MAX) = 'Select distinct item_id, attr_val from [dbo].[CONTRACT_ATTR] WHERE [ATTR_VAL]  LIKE ''%@VALUE%'' AND [FIELD_ID] = 413 ORDER BY [attr_val]';
    SET @SQL5 = replace(@SQL5, '@VALUE', @VALUE);
    EXEC SP_executesql @SQL5;

These are the results:

I am trying to take the resulting (item_id) and run another select query. Something like this:

UNION
Select Column3 From @SQL5 where other_column = 1234

The results would be 3 columns for each row returned, the 2 original and the new one found in the 2nd select.

What am I doing wrong and how do I fix it?

'Union' requires that the two select statements return same columns (schema of the output must be the same) — Sparrow
– Sparrow, Commented Feb 19, 2017 at 22:42
What relevance does your seconds query have to the first query? From what you have explained there is no need for dynamic SQL or to do this in two steps — Nick.Mc
– Nick.Mc, Commented Feb 19, 2017 at 22:48
Does the 1234 in the second query relate to the item_id in the first query? That's what a join does. Since you have three rows in the first query, I guess you are expecting possibly three or more rows as a result from your second query? It seems that you just need use a join. — Nick.Mc
– Nick.Mc, Commented Feb 19, 2017 at 22:59

Nick.Mc · Accepted Answer · 2017-02-21 00:42:21Z

1

Here is an answer without dynamic SQL:

DECLARE @VALUE VARCHAR(100)

SET @VALUE = '123'

SELECT myuser.item_id,
       myuser.attr_val,
       parent.attr_val
FROM [dbo].[CONTRACT_ATTR] AS myuser
JOIN [dbo].[CONTRACT_ATTR] AS parent 
  ON parent.item_id = myuser.item_id 
  AND myuser.item_id = parent.item_id 
WHERE myuser.field_id = 239 
and parent.[ATTR_VAL]  LIKE '%' + @VALUE + '%'

I also moved join conditions wholly into the on clause.

answered Feb 21, 2017 at 0:42

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Tunnell · Accepted Answer · 2017-02-19 22:52:15Z

0

I got it:

SELECT myuser.item_id,
       myuser.attr_val,
       parent.attr_val
FROM [dbo].[CONTRACT_ATTR] AS myuser
JOIN [dbo].[CONTRACT_ATTR] AS parent 
  ON parent.item_id = myuser.item_id 
WHERE myuser.item_id = parent.item_id and myuser.field_id = 239 and parent.[ATTR_VAL]  LIKE '%123%'

integrating dynamic sql:

    DECLARE @SQL5 NVARCHAR(MAX) = 'SELECT myuser.item_id, myuser.attr_val, parent.attr_val FROM [dbo].[CONTRACT_ATTR] AS myuser JOIN [dbo].[CONTRACT_ATTR] AS parent ON parent.item_id = myuser.item_id WHERE myuser.item_id = parent.item_id and myuser.field_id = 239 and parent.[ATTR_VAL]  LIKE ''%@VALUE%''';
    SET @SQL5 = replace(@SQL5, '@VALUE', @VALUE);
    EXEC SP_executesql @SQL5;

answered Feb 19, 2017 at 22:52

David Tunnell

7,55222 gold badges75 silver badges128 bronze badges

1 Comment

Nick.Mc Over a year ago

What is the point of dynamic SQL here? You can just use a variable directly.

Collectives™ on Stack Overflow

Query database using result from previous query and combining them

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related