Solved

Strange behaviour of sp_executesql with xml parameter

Posted on 2016-08-08
8
10 Views
Last Modified: 2016-10-09
I am getting various results from 'EXEC sp_executesql @sql, N'@xml xml', @xml', depending on how I construct the @sql variable. Only 2 of these constructs are successful. All others fail, with different error messages. Whether successful or not, the only thing that changes is the number of repetitions of a single field in the SELECT clause, and whether they are on a single line, or separated by carriage returns.

The general construct is:

   SET @sql='
   SELECT
   (repeated field)
   FROM @xml.nodes(''/row'') As t(c)
   INNER JOIN [SQLRONA].[dbo].[T_item] ON [SQLRONA].[dbo].[T_item].[CodeRona]=t.c.value(''@sk'',''char(8)'')'

First, here are the 2 field repetitions that work:

   1) [SQLRONA].[dbo].[T_item].[CodeRona] (N.B.: Not a repetition. Just a single field.)
   2) [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona]

Following are some of the failing repetitions, and their error messages:

   3) [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona]
       Incorrect syntax near '('.

   4) [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona]
       Unclosed quotation mark after the character string 'Code'. Incorrect syntax near 'Code'.

   5) [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona], [SQLRONA].[dbo].[T_item].[CodeRona]
       An expression of non-boolean type specified in a context where a condition is expected, near 'SQLRONA'.

   6) [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona],
       [SQLRONA].[dbo].[T_item].[CodeRona]
       Unclosed quotation mark after the character string 'db'. Incorrect syntax near 'db'.
       
Can anyone explain why sp_executesql is behaving like that?
0
Comment
Question by:J2015
  • 4
  • 3
8 Comments
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
The format for sp_executesql does not look correct.

By definition, it should be:
'EXEC sp_executesql @sql, N'@xml xml', @xml = <value>'

Where are you setting the passed in parameter equal to a value?
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
You can see examples at Microsoft's page for sp_executesql:
https://msdn.microsoft.com/en-us/library/ms188001.aspx
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
Correction to the definition. There shouldn't have been an extra apostrophe at the end:
'EXEC sp_executesql @sql, N'@xml xml', @xml = <value>
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:J2015
Comment Utility
Here's the entire code:

USE [SQLXRAY_FE_EXTENSION]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_FetchXml] @List varchar(Max)
AS
DECLARE @xml xml, @sql nvarchar(255)
BEGIN
      SET NOCOUNT ON
      SET @xml=CAST(@List As XML)
      SET @sql=' SELECT [SQLRONA].[dbo].[T_item].[CodeRona] FROM @xml.nodes(''/row'') As t(c)
      INNER JOIN [SQLRONA].[dbo].[T_item]
      ON [SQLRONA].[dbo].[T_item].[CodeRona]=t.c.value(''@sk'',''char(8)'')'
      EXEC sp_executesql @sql, N'@xml xml', @xml
END

I call the stored procedure with the following query:

usp_FetchXml '<row sk="7805059" /> <row sk="0001982" /> <row sk="11635000" /> <row sk="11635001" />'
0
 

Author Comment

by:J2015
Comment Utility
I forgot to mention: I am using SQL Server 2008 R2.
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
This line:
EXEC sp_executesql @sql, N'@xml xml', @xml

should be:
EXEC sp_executesql @sql, N'@xml xml', @xml = @xml

To make it clearer:
EXEC sp_executesql @sql, N'@xmlData xml', @xmlData = @xml
0
 

Accepted Solution

by:
J2015 earned 0 total points
Comment Utility
I found why it fails. The line DECLARE @xml xml, @sql nvarchar(255)
should be DECLARE @xml xml, @sql nvarchar(MAX).

Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now