Return xml variable from dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Return xml variable from dynamic SQL

Author

Topic

Kgpedersen
Starting Member

1 Post

Posted - 2010-11-18 : 09:52:44

I get the error message: Must declare the scalar variable "@OrgXml"
using the following stored procedure:
Is it impossible to return the XML to the caller??

@OrgEnhed VARCHAR(8000),
@OrgXml XML OUTPUT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @InputOrgEnh VARCHAR(8000);
DECLARE @dSQL varchar(2000);
DECLARE @lan varchar(200);
DECLARE @OrgPart VARCHAR(10);
DECLARE @NivPart VARCHAR(2);
DECLARE @Pos INTEGER;
DECLARE @Pos1 INTEGER;

SET @InputOrgEnh = @OrgEnhed;
SET @InputOrgEnh = REPLACE(@InputOrgEnh, ' ', '');
SET @InputOrgEnh = REPLACE(@InputOrgEnh, 'N', '');
SET @InputOrgEnh = REPLACE(@InputOrgEnh, '''', '');

WHILE CHARINDEX(',', @InputOrgEnh, 0) > 0
BEGIN
SET @Pos = CHARINDEX('-', @InputOrgEnh, 0);
SET @Pos1 = CHARINDEX(',', @InputOrgEnh, @Pos);
SET @NivPart = SUBSTRING(@InputOrgEnh, @Pos-(@Pos-1),@Pos-1);
SET @OrgPart = SUBSTRING(@InputOrgEnh, @Pos+1, @Pos1 - @Pos - 1);

SET @InputOrgEnh = SUBSTRING(@InputOrgEnh, @Pos1+1, LEN(@InputOrgEnh) - @Pos1);

SET @dSQL =
'set @OrgXml =(
SELECT A.ORG_ENHEDNR X
FROM DWS.TBDWHP0045_D0024_ORG AS A
WHERE A.ORG_ENHEDNR_NIV' + @NivPart+ ' = ' + @OrgPart + '
AND A.ORG_DEFAULT_KD = ''J''
AND SLUT_DATO =''9999-12-31 00:00:00'' for XML PATH(''''), elements)';

-- print @dSQL
EXEC (@dSQL);
END;

IF CHARINDEX(',', @InputOrgEnh, 0) = 0
BEGIN
SET @Pos = CHARINDEX('-', @InputOrgEnh, 0);
SET @NivPart = SUBSTRING(@InputOrgEnh, 1, @Pos -1);
SET @lan = LEN(@InputOrgEnh);
SET @OrgPart = SUBSTRING(@InputOrgEnh, @Pos+1, (LEN(@InputOrgEnh)-(@Pos)))

SET @dSQL =

'set @OrgXml =(
SELECT A.ORG_ENHEDNR X
FROM DWS.TBDWHP0045_D0024_ORG AS A
WHERE A.ORG_ENHEDNR_NIV' + @NivPart+ ' = ' + @OrgPart + '
AND A.ORG_DEFAULT_KD = ''J''
AND SLUT_DATO =''9999-12-31 00:00:00'' for XML PATH(''''), elements)';

EXEC (@dSQL);

END

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 10:57:26

you cant use EXEC if you want to return variable values in dynamic sql. you need to use sp_executesql instead.

see
http://support.microsoft.com/kb/262499

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources