samedi 25 juin 2016

SQL: creating Dynamic SQL getting scalar error on XML node

I'm getting 'Must declare the scalar variable "@model_look_xml"' error. When creating a dynamic query.

This executes fine:

SELECT * 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 =
model_skill.model_skill_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 and  exists
select 1 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:

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))'

Aucun commentaire:

Enregistrer un commentaire