CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7532

Query database using result from previous query and combining them

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:

enter image description here

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?

Upvotes: 0

Views: 47

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

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.

Upvotes: 1

David Tunnell
David Tunnell

Reputation: 7532

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;

Upvotes: 0

Related Questions