Syntax error when parsing variable from XML within an SQL stored Procedure-CodePudding

So, I'm trying to run a stored procedure within another stored procedure and parse out some XML data into a variable. It is giving me a syntax error on the first plus sign im using to add that variable within the XML string. Its kinda like this: names are changed to protect the innocent. (SQL stored Proc:)

EXEC [database].some.Stored.proceddure
@variabelOne = 0
,@variableTwo = 
    '<Root>
    <r>
    <RefID>1</RefID>
    <GroupID>2</GroupID>
    <GroupFilter>'   @AnotherVariable   '</GroupFilter>
    </r>
</Root>'

CodePudding user response：

In SQL server you use CONCAT to concatenate strings.

EXEC [database].some.Stored.proceddure
@variabelOne = 0
,@variableTwo = CONCAT(
    '<Root>
    <r>
    <RefID>1</RefID>
    <GroupID>2</GroupID>
    <GroupFilter>' , @AnotherVariable , '</GroupFilter>
    </r>
</Root>')

CodePudding user response：

You cannot use or CONCAT in exec. Save the string first to a variable and then call the exec:

declare @var_two varchar(max)

set @var_two = '<Root>
    <r>
    <RefID>1</RefID>
    <GroupID>2</GroupID>
    <GroupFilter>'   @AnotherVariable   '</GroupFilter>
    </r>
</Root>'

EXEC [database].some.Stored.proceddure
@variabelOne = 0
,@variableTwo = @var_two