I am exploring the use of dynamic SQL within a stored procedure and have run
in to a problem. The dynamic SQL has no visibility of variables declared
outside the dynamic SQL. Try this snippet which causes an error:

declare @branch int
set @branch = 10

exec ( 'select @branch_no' )

Is there any way to make these variables visible to the dynamic sql without
concatenation?

The reason why this will be a problem is that I will be use the openxml
command within the dynamic sql. I will be using very large XML strings so I
am pretty sure that I will have problems concatenating XML strings with
dynamic sql statements.

Any ideas?

Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
example which explains how to pass & return values from such strings.

Great that was really useful. I have combined a couple of examples (openxml
and sp_executesql) from books online in the snippet below to show how XML
can be passed in as a parameter to dynamic sql:

DECLARE @SQLString NVARCHAR(500)

/* Build the SQL string */
SET @SQLString =
     N'
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT    *
FROM       OPENXML (@idoc, ''/ROOT/Customer'',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

EXEC sp_xml_removedocument @idoc'


/* Execute the string */
EXECUTE sp_executesql @SQLString, N'@doc text',
                      @doc = '<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'


Show quoteHide quote

As long as each string is <= 8000 characters (or 4000 characters with
Unicode), you can say EXEC(@sql1 + @sql2 + @sql3);

A

in to a problem. <<

As well you should!!  This is an awful way to even think of writing
code of any kind.  Remember coupling, cohesion and all that stuff in
your fist Software Engineering course?

So you want on-the-fly, mixed, proprietary languages so you can
manipulate XML with T-SQL?  This whole thing sounds like a pile of
kludges, but without better specs we can only guess at a relatioanl
solution.

The reason for dynamic SQL is that I need to parameterize the database name
in the queries. We have a database with 20 odd tables with exactly the same
structure - so rather than duplicating the stored procedure 20 odd times I
am looking at writing it once with dynamic SQL.

The reason for XML is so that I can send large batches of data at a time to
the stored procedure. Which is very efficient.

I am not sure that I will use this technique but it is certainly one of
several I am considering. Its not a position I relish being in but that's
the way the database is so more likely than not I will have to work with its
shortcomings.

See another post by my titled "Parameterize table name without constructing
dynamic query?"

Show quoteHide quote

Parameterize table name without constructing dynamic query?
use trigger
Is subquery bad?
Generating the perfect Primary Key: Using CRC32? Using MD5?
Max date in a row