exec sp_executesql

Question

Post reply

exec sp_executesql

issam mansour

SSC-Addicted

Points: 478
More actions
May 22, 2008 at 2:59 pm

#99142

Hi,
I am trying to move a verable to the sp_executesql but i have an error:-
SELECT @position = LEN(@inputNumber) - 2
set @x=1
WHILE @x<=3
begin
set @esql =N'set @xnum'+ convert(varchar(10),@x)+' = RIGHT(''000'' + SUBSTRING('+@inputNumber+','+convert(varchar(10),@position)+', 3), 3)'
exec sp_executesql @esql, N'''@xnum'+convert(varchar(10),@x)+' char(3) INT output''','@xnum'+convert(varchar(10),@x) output
set @x=@x+1
SELECT @counter = @counter + 1
, @position = @position - 3
END
end
please any help.

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

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

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

It would really help if you did two things...

1. Post what the actual error is

2. Post the generated conten of the dynamic SQL code

You may even want to take a peek at the URL in my signature.

--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

issam mansour SSC-Addicted Points: 478 More actions · Answer 2

Thanks for your repons, firest the error as following:

Msg 102, Level 15, State 1, Procedure Currencyjbm, Line 153

Incorrect syntax near '+'.

second the dynmic sql ..

set @xnum1 = RIGHT('000' + SUBSTRING(3211110,5, 3), 3)

set @xnum2 = RIGHT('000' + SUBSTRING(3211110,2, 3), 3)

set @xnum3 = RIGHT('000' + SUBSTRING(3211110,-1, 3), 3)

Thanks

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

And, I take it, line 153 is somewhere in the code you posted?

--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

issam mansour SSC-Addicted Points: 478 More actions · Answer 4

line 153 is:-

exec sp_executesql @esql, N'''@xnum'+convert(varchar(10),@x)+' char(3) INT output''','@xnum'+convert(varchar(10),@x) output

swekster Grasshopper Points: 13 More actions · Answer 5

I'm not even sure what you're trying to accomplish

or that the sp_executeSql syntax is just right.

A bit of background might help.

Anyhow, that Dynamic SQL fails me with a

"Argument data type int is invalid for argument 1 of substring function." because of the SUBSTRING(3211110,5, 3) part, so maybe that's where the problem is.

Furthermore, the dynSQL contains three variables, and you just pass one via the second sp_executeSql param, for which i'm sure the SQL Server would give you grief.

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

Issam,

Are you simply trying to slice a given number into slices containing 3 digits each?

--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

p.s.

I don't know what the code is actually trying to do nor why you feel you need dynamic SQL to do it, but if you break all the lines just before "+" signs everywhere, you'll have a better chance of debugging your own code... the wrong number of single quotes sticks out like a sore thumb when you do that and you'll quickly see what's causing the error.

--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

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

instead of trying to count single quotes when building a string for dynamic SQL consider CHAR(39), which is a single quote, as in

@strSQL = @strSQL + 'DateField=' + CHAR(39)

@strSQL = @strSQL + CONVERT(VARCHAR,@DateField,100) + CHAR(39)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Try building up your strings and placing them in variables then passing those variables to sp_executesql. I seem to recall that concatenating bits of string in the call to a stored proc doesn't work.

so, something like this

DECLARE @sql nvarchar(4000)

DECLARE @paramdef nvarchar(4000)

SET @sql = ...

SET @paramdef ...

EXEC sp_executesql @sql, @paramdef, ....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

GilaMonster (5/27/2008)
Try building up your strings and placing them in variables then passing those variables to sp_executesql. I seem to recall that concatenating bits of string in the call to a stored proc doesn't work.

Gail has it right, the EXEC command does not "do" expressions, you have to pass variables and literals only.

[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]

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

my bad

I pulled this snippet out of a working stored procedure:

IF @KOUNT > 0

BEGIN

SET @strSQL = @strSQL + ' DateModified=' + CHAR(39) + CAST(@RightNow AS VARCHAR(20)) + CHAR(39) + ',' + @CRLF

SET @strSQL = @strSQL + ' EmployeeID_ModifiedBy=' + CAST(@EmployeeID_ModifiedBy AS VARCHAR(6)) + @CRLF

SET @strSQL = @strSQL + 'WHERE OrderDetailID=' + CAST(@OrderDetailID AS VARCHAR(8))

EXECUTE (@strSQL)

END

btw - I SET @RightNow to GetDate() and @CRLF to CHAR(10) + CHAR(13)