String concatenation as a procedure variable

Question

Post reply

String concatenation as a procedure variable

david.buncle

Right there with Babe

Points: 774
More actions
April 11, 2008 at 9:21 am

#74886

Just wondering why i'm unable to pass a couple of variables as a concatenated string to the following stored procedure...

EXEC sp_xml_preparedocument @iDoc OUTPUT, '''' + @xml_0 + @xml_1 + '''',

' '

It gives the error that there's incorrect syntax near '+'

Note that doing

SELECT '''' + @xml_0 + @xml_1 + ''''

gives me the desired string that I want to pass to the procedure.

Cheers.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Rob Goddard SSCrazy Points: 2622 More actions · Answer 1

It's not allowed in Transact-SQL. You can't even do that with sp_executesql.

I think it's to do with the fact that it's implicitly converting the parameter to unicode and so these operations aren't allowed in the procedure call. Just concatenate them into a single string variable and pass that instead.

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

david.buncle Right there with Babe Points: 774 More actions · Answer 2

david.buncle

Right there with Babe

Points: 774

April 11, 2008 at 9:40 am

#801594

I can't put them into one variable because the combined text is larger than any local variable will store. The data is originally from a BLOB in an oracle table that I've imported into a NTEXT field within SQL Server.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Not true... you can use a TEXT datatype as a parameter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

david.buncle Right there with Babe Points: 774 More actions · Answer 4

david.buncle

Right there with Babe

Points: 774

April 14, 2008 at 2:42 am

#801946

TEST? This datatype isn't recognised for SQL Server 2000... Did you mean TEXT?, if so this is invalid for a local variable.

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 5

Seggerman-675349

Hall of Fame

Points: 3563

April 14, 2008 at 6:45 am

#802026

what's wrong with:

DECLARE @xml_2 VARCHAR(50) -- or whatever

SELECT @xml_2 = '''' + @xml_0 + @xml_1 + ''''

@iDoc OUTPUT, xml_2,

am I missing something?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

david.buncle (4/14/2008)

TEST? This datatype isn't recognised for SQL Server 2000... Did you mean TEXT?, if so this is invalid for a local variable.

Yes... TEXT... coffee deprivation while typing. Thank you for the catch.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

david.buncle (4/14/2008)

TEST? This datatype isn't recognised for SQL Server 2000... Did you mean TEXT?, if so this is invalid for a local variable.

... and, although you can't declare a local variable as TEXT, you can use one as a parameter in a stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

I'm thinking xml_preparedocument doesn't want a formula as one of its parameters. You need to do the concatenation first.

As in - try this:



declare @xmlfull avrhcar(4000)

Set @xmlfull='''' + @xml_0 + @xml_1 + '''';

EXEC sp_xml_preparedocument @iDoc OUTPUT,@xmlfull

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

I agree with Matt. You cannot do expressions in an EXEC statement.

As a general rule only [font="System"]Select[/font] and the major DML statements can have actual scalar expressions. The procedural statements and others like EXEC, PRINT, etc. cannot.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

david.buncle Right there with Babe Points: 774 More actions · Answer 10

david.buncle

Right there with Babe

Points: 774

April 15, 2008 at 5:06 am

#802444

That would be nice but the actual text is larger that 4000 chars.

I'm scrapping this idea and doing the extraction of the XML on the oracle side as there at least I can declare a XMLType variable and work with the data better.

Cheers anyway.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

I was just going to add that if it weren't XML, perhaps it would fit.

If you're trying to get info from Oracle using SQL Server, why not just open up a Linked Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

david.buncle Right there with Babe Points: 774 More actions · Answer 12

Cheers, I am going to do that eventually. The problem is that the data is currently stored as compressed XML in BLOBS in Oracle. I actually need the data extracted from the XML BLOBS and stored in normalised tables (the original application read the xml from the oracle tables). So I've got the lovely task of creating 50 new tables with which I'll extract the XML data too... and then use DTS to bring the data into SQL Server 2000. A fun week ahead for me.