Skip Skip - 1 year ago 89
SQL Question

SQL: creating Dynamic SQL getting scalar error on XML node

I'm getting an error

Must declare the scalar variable "@model_look_xml"

when creating a dynamic query.

This executes fine:

FROM model
WHERE (model_height BETWEEN 0.00 AND 80.00
AND model_id IN (SELECT DISTINCT assn.assn_model_id
FROM model_look
INNER JOIN model_skill ON model_look.model_look_model_id =
INNER JOIN assn ON assn.assn_model_id = model_skill.model_skill_model_id
WHERE assn.assn_office = 34
AND model_skill_skill_id = 12
FROM @model_look_xml.nodes('/root/id') AS result(node)
WHERE node.value('(.)[1]', 'int') = model_look_look_id))

When I try to break it up dynamically, I'm not sure how to handle the nodes.

This works fine:

declare @model_look_xml xml
declare @model_skill_xml xml
declare @model_eyes_xml xml
declare @model_hair_xml xml
declare @model_ethnicity_xml xml
declare @model_skill_skill_id int
declare @assn_office int
declare @top_height decimal(4,2)
declare @low_height decimal(4,2)
--testing values
set @assn_office = 34
set @top_height = 80.00
set @low_height = 0.00
set @model_look_xml = CAST('<root><id>7</id><id>6</id><id>12</id></root>' AS XML)

SET @SQL = N'SELECT * FROM model where '
SET @SQL = @SQL + N'(model_height between '
SET @SQL = @SQL + cast(@low_height as varchar(50))
SET @SQL = @SQL + ' and '
SET @SQL = @SQL + cast(@top_height as varchar(50))
SET @SQL = @SQL + N' and model_id in (Select distinct assn.assn_model_id From '
SET @SQL = @SQL + N'model_look Inner Join
model_skill On model_look.model_look_model_id =
model_skill.model_skill_model_id Inner Join
assn On assn.assn_model_id = model_skill.model_skill_model_id
Where assn.assn_office = '+ cast(@assn_office as varchar(50)) +'
and model_skill_skill_id = '+ cast(@model_skill_skill_id as varchar(50) ) --+'))'
SET @SQL = @SQL + N' and exists(select 1 from '

Until I get to this line, not sure how to handle it. If I was only going to do this once I would have found a different way, but I'm going to have 6 optional parameters(possible lists) coming in.

SET @SQL = @SQL + N'@model_look_xml.nodes(''/root/id'')as result(node) where node.value(''(.)[1]'', ''int'') = model_look_look_id))'

Answer Source

You never show us where @model_look_xm is actually declared. I assume it must be because you state it actually works. However, the most likely issue is your local variable is not accessible in your dynamic sql statement. You have to pass it as a parameter if order for you to access it.

To solve simply define @model_look_xm as a input parameter on your dynamic statement.

EXECUTE sp_executeql @SQL, N'@model_look_xm XML', @model_look_xm = @model_look_xm

This line is a little of an assumption because you are not showing all of your code so adjust as needed.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download