sp_executesql with parameters - SQL Server Forums

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

Transact-SQL (2000)

sp_executesql with parameters

Author

Topic

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-14 : 17:44:15

I'm trying to use sp_executesql using parameters but I can't get it to work and I have no idea what's wrong !!

Version 1
***********
Declare @max_no as smallint
Declare @i as smallint
Declare @SQLStr1 as Nvarchar(750)
Declare @SQLStr2 as Nvarchar(300)
Declare @ParmDef1 as Nvarchar(50)
Declare @ParmDef2 as Nvarchar(10)
Declare @title_var as char(7)
Declare @firstname_var as char(12)

Set @max_no = (Select Max(no_of_names) From SplitName_Test Where Split_ind = 'A')
Set @i = 1

Set @SQLStr1 = N'Update DW_Staging.dbo.SplitName_Test Set @title = T.OutTitle,'
Set @SQLStr1 = @SQLStr1 + N' RemainName = Case And_posn'
Set @SQLStr1 = @SQLStr1 + N' When 0 Then '''' '
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, S.And_posn+1, Len(S.RemainName)))) End,'
Set @SQLStr1 = @SQLStr1 + N' @firstname = Case'
Set @SQLStr1 = @SQLStr1 + N' When And_posn = 0 And T.OutTitle Is Null Then S.RemainName'
Set @SQLStr1 = @SQLStr1 + N' When And_posn = 0 Then LTrim(RTrim(Right(S.RemainName, Len(S.RemainName) - Len(S.RemainStart))))'
Set @SQLStr1 = @SQLStr1 + N' When T.OutTitle Is Null Then RTrim(Left(S.RemainName, And_posn - 1))'
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, Len(S.RemainStart)+1, And_posn - (Len(S.RemainStart)+1)))) End'
Set @SQLStr1 = @SQLStr1 + N' From DW_Staging.dbo.SplitName_Test as S'
Set @SQLStr1 = @SQLStr1 + N' Left Join DW_Staging.dbo.L_Titles as T'
Set @SQLStr1 = @SQLStr1 + N' On S.RemainStart = T.InTitle'
Set @SQLStr1 = @SQLStr1 + N' Where S.Split_ind = ''A'' And S.No_of_names >= @no'

Set @ParmDef1 = N'@title char(7), @firstname char(12), @no int'

Set @SQLStr2 = ' Update DW_Staging.dbo.SplitName_Test'
Set @SQLStr2 = @SQLStr2 + N' Set RemainStart = RTrim(Left(RemainName, CharIndex('' '', RemainName+'' ''))),'
Set @SQLStr2 = @SQLStr2 + N' And_posn = CharIndex(''&'', RemainName)'
Set @SQLStr2 = @SQLStr2 + N' Where Split_ind = ''A'' And No_of_names >= @no'

Set @ParmDef2 = N'@no int'

While @i <= @max_no
BEGIN
Set @title_var = 'TITLE_' + Str(@i, 1)
Set @firstname_var = 'FIRST_NAME_' + Str(@i, 1)

EXECUTE sp_executesql @SQLStr1, @ParmDef1, @title = @title_var, @firstname = @firstname_var, @no = @i

EXECUTE sp_executesql @SQLStr2, @ParmDef2, @no = @i

Set @i = @i + 1
END

When I run this I get the following error :

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

By doing a REPLACE and so converting the parameters to their values before attempting to execute the string the code runs fine!

Version 2
***********

Declare @max_no as smallint
Declare @i as smallint
Declare @SQLStr1 as Nvarchar(750)
Declare @SQLStr2 as Nvarchar(300)
Declare @SQLStrA as Nvarchar(800)
Declare @SQLStrB as Nvarchar(350)

Set @max_no = (Select Max(no_of_names) From SplitName_Test Where Split_ind = 'A')
Set @i = 1

Set @SQLStr1 = N'Update DW_Staging.dbo.SplitName_Test Set @title = T.OutTitle,'
Set @SQLStr1 = @SQLStr1 + N' RemainName = Case And_posn'
Set @SQLStr1 = @SQLStr1 + N' When 0 Then '''' '
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, S.And_posn+1, Len(S.RemainName)))) End,'
Set @SQLStr1 = @SQLStr1 + N' @firstname = Case'
Set @SQLStr1 = @SQLStr1 + N' When And_posn = 0 And T.OutTitle Is Null Then S.RemainName'
Set @SQLStr1 = @SQLStr1 + N' When And_posn = 0 Then LTrim(RTrim(Right(S.RemainName, Len(S.RemainName) - Len(S.RemainStart))))'
Set @SQLStr1 = @SQLStr1 + N' When T.OutTitle Is Null Then RTrim(Left(S.RemainName, And_posn - 1))'
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, Len(S.RemainStart)+1, And_posn - (Len(S.RemainStart)+1)))) End'
Set @SQLStr1 = @SQLStr1 + N' From DW_Staging.dbo.SplitName_Test as S'
Set @SQLStr1 = @SQLStr1 + N' Left Join DW_Staging.dbo.L_Titles as T'
Set @SQLStr1 = @SQLStr1 + N' On S.RemainStart = T.InTitle'
Set @SQLStr1 = @SQLStr1 + N' Where S.Split_ind = ''A'' And S.No_of_names >= @no'

Set @SQLStr2 = ' Update DW_Staging.dbo.SplitName_Test'
Set @SQLStr2 = @SQLStr2 + N' Set RemainStart = RTrim(Left(RemainName, CharIndex('' '', RemainName+'' ''))),'
Set @SQLStr2 = @SQLStr2 + N' And_posn = CharIndex(''&'', RemainName)'
Set @SQLStr2 = @SQLStr2 + N' Where Split_ind = ''A'' And No_of_names >= @no'

While @i <= @max_no
BEGIN
Set @SQLStrA = replace(@SQLStr1, '@title', 'TITLE_' + str(@i,1))
Set @SQLStrA = replace(@SQLStrA, '@firstname', 'FIRST_NAME_' + str(@i,1))
Set @SQLStrA = replace(@SQLStrA, '@no', str(@i,1))
Set @SQLStrB = replace(@SQLStr2, '@no', str(@i,1))

EXECUTE sp_executesql @SQLStrA

EXECUTE sp_executesql @SQLStrB

Set @i = @i + 1
END

I'm happy to go with version 2, but I am just curious to see if anyone knows what I am doing wrong in version 1 .....

(I'm using SQL2000 on Windows 2000)

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-14 : 18:13:16

I didn't go through it in detail to figure out which one, but one of your variables is not big enough to hold all of the data you are trying to stuff into it.

That error means that it can't append what you want because the variable isn't big enough.

-Chad

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-14 : 18:28:08

Yep that's what the error says....(special aussie to -tryhard- Kiwi wink)

Why don't you try Version 1 and just make all your vars Nvarchar(1000) - this is the highly technical and professional approach, mind...

Alternatively, (and I'm guessing here) - but you've defined @title as varchar(7), so if @i > 9 then you're (to put it politely) -
in trouble.

HTH

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-14 : 18:35:28

Thanks guys. I've fixed it.

By changing @ParmDef1 to

Set @ParmDef1 = N'@title char(30), @firstname char(40), @no int'

it now works.

What I don't understand is why it doesn't work when it is set to

Set @ParmDef1 = N'@title char(7), @firstname char(12), @no int'

since @title is set to the value of @title_var which is defined as char(7)
and @firstname is set to the value of @firstname_var which is defined as char(12) ??

(EXECUTE sp_executesql @SQLStr1, @ParmDef1, @title = @title_var, @firstname = @firstname_var, @no = @i)

Can anyone enlighten me?

By the way - how come there is no smilie for 'confused'?
I'd be using it all the time !

Edited by - tigger on 02/14/2002 18:43:30

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-14 : 18:45:30

quote:

Thanks for that.

yep - well sometimes you're better off on your own...sorry for the dud answer (now I look at it again)...

I agree that looks odd... I'll have a play and get back to you.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-14 : 18:50:43

Just out of interest....this works fine

Declare @max_no as smallint
Declare @i as smallint
Declare @SQLStr1 as Nvarchar(750)
Declare @SQLStr2 as Nvarchar(300)
Declare @ParmDef1 as Nvarchar(50)
Declare @ParmDef2 as Nvarchar(10)
Declare @title_var as char(7)
Declare @firstname_var as char(12)

Set @max_no = 9
Set @i = 1

Set @SQLStr1 = N'select @title '

Set @ParmDef1 = N'@title char(7), @no int'
Set @ParmDef2 = N'@no int'

While @i <= @max_no
BEGIN
Set @title_var = 'TITLE_' + Str(@i, 1)

EXECUTE sp_executesql @SQLStr1, @ParmDef1, @title = @title_var, @no = @i

EXECUTE sp_executesql @SQLStr2, @ParmDef2, @no = @i

Set @i = @i + 1
END

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-14 : 18:58:34

Bizarre!!

I've changed title_var and firstname_var from 7 and 12 to
Declare @title_var as char(10)
Declare @firstname_var as char(15)

(just in case i > 9 - this should never happen but I thought it
might make you feel better after the cricket !!

)

and you're right the @title part works. However, @firstname fails until it is set to 39 !! [still confused]

Set @ParmDef1 = N'@title char(10), @firstname char(39), @no int'

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-14 : 19:18:23

quote:

Bizarre!!

(Now here I assume you're talking about the dumping of Steve Waugh)

What do you get when you do run the following::?
--******************************
Declare @max_no as smallint
Declare @i as smallint
Declare @SQLStr1 as Nvarchar(750)
Declare @SQLStr2 as Nvarchar(300)
Declare @ParmDef1 as Nvarchar(50)
Declare @ParmDef2 as Nvarchar(10)
Declare @title_var as char(7)
Declare @firstname_var as char(12)

Set @max_no = (Select Max(no_of_names) From SplitName_Test Where Split_ind = 'A')
Set @i = 1

Set @SQLStr1 = N'Update DW_Staging.dbo.SplitName_Test Set @title = T.OutTitle,'
Set @SQLStr1 = @SQLStr1 + N' RemainName = Case And_posn'
Set @SQLStr1 = @SQLStr1 + N' When 0 Then '''' '
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, S.And_posn+1, Len(S.RemainName)))) End,'
Set @SQLStr1 = @SQLStr1 + N' @firstname = Case'
Set @SQLStr1 = @SQLStr1 + N' When 1=0 Then S.RemainName'
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, Len(S.RemainStart)+1, And_posn - (Len(S.RemainStart)+1)))) End'
Set @SQLStr1 = @SQLStr1 + N' From DW_Staging.dbo.SplitName_Test as S'
Set @SQLStr1 = @SQLStr1 + N' Left Join DW_Staging.dbo.L_Titles as T'
Set @SQLStr1 = @SQLStr1 + N' On S.RemainStart = T.InTitle'
Set @SQLStr1 = @SQLStr1 + N' Where S.Split_ind = ''A'' And S.No_of_names >= @no'

Set @ParmDef1 = N'@title char(7), @firstname char(12), @no int'

Set @SQLStr2 = ' Update DW_Staging.dbo.SplitName_Test'
Set @SQLStr2 = @SQLStr2 + N' Set RemainStart = RTrim(Left(RemainName, CharIndex('' '', RemainName+'' ''))),'
Set @SQLStr2 = @SQLStr2 + N' And_posn = CharIndex(''&'', RemainName)'
Set @SQLStr2 = @SQLStr2 + N' Where Split_ind = ''A'' And No_of_names >= @no'

Set @ParmDef2 = N'@no int'

While @i <= @max_no
BEGIN
Set @title_var = 'TITLE_' + Str(@i, 1)
Set @firstname_var = 'FIRST_NAME_' + Str(@i, 1)

EXECUTE sp_executesql @SQLStr1, @ParmDef1, @title = @title_var, @firstname = @firstname_var, @no = @i

EXECUTE sp_executesql @SQLStr2, @ParmDef2, @no = @i

Set @i = @i + 1
END

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-14 : 21:26:44

Sorry for the delay - I was at the gym !!

I get the usual error message :

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-14 : 22:12:40

quote:

Sorry for the delay - I was at the gym !!

Sorry for the dumb posts - that's two in one day - I quit!

What I was meaning was that I'd be interested in seeing the recordset returned by...

quote:

Case'
Set @SQLStr1 = @SQLStr1 + N' When And_posn = 0 And T.OutTitle Is Null Then S.RemainName'
Set @SQLStr1 = @SQLStr1 + N' When And_posn = 0 Then LTrim(RTrim(Right(S.RemainName, Len(S.RemainName) - Len(S.RemainStart))))'
Set @SQLStr1 = @SQLStr1 + N' When T.OutTitle Is Null Then RTrim(Left(S.RemainName, And_posn - 1))'
Set @SQLStr1 = @SQLStr1 + N' Else LTrim(RTrim(Substring(S.RemainName, Len(S.RemainStart)+1, And_posn - (Len(S.RemainStart)+1)))) End'

But if you're happy - don't bother...

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-14 : 22:40:01

Not too sure what you mean - must be the aussie accent!

The final results will look something like this -

OriginalName; Title_1; First_name_1; Title_2; First_name_2
MR FRED & MRS WILMA FLINTSTONE; MR; FRED; MRS; WILMA FLINTSTONE
MR BARNEY & BETTY RUBBLE; MR; BARNEY; ; BETTY RUBBLE
MR A SMITH & MRS B SMITH; MR; A SMITH; MRS; B SMITH

(; just indicates a different column)

Then I will have to do some more processing and stuff until you end up with
MR FRED FLINTSTONE & MRS WILMA FLINTSTONE
MR BARNEY RUBBLE & BETTY RUBBLE
MR A SMITH & MRS B SMITH

If only people would key stuff in in the right format !!!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-14 : 23:06:11

quote:

Not too sure what you mean - must be the aussie accent!

aussie accent?? We don't have an accent!

What I was trying to get at was that the code I quoted before is what @firstname is set to in your dynamic Update statement. I just wanted to see the values to see if there was something odd. In theory - it shouldn't matter because if @firstname is defined as char(12), even if you set it to a value longer than 12 chars it should ignore the rest.

But obviously from what you said, this is not what appears to be happening.

I initially thought that dynamic SQL may not do the length clipping properly - so I tried
declare @s nvarchar(1000)
set @s = N'declare @s char(12) '
set @s = @s + N'set @s = ''this is a statement longer than I expected'' '
set @s = @s + N'select @s'
execute sp_executesql @s

but that was fine. So I give up. Unless you want to go further.....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-17 : 14:49:57

I give up too !!!

Edited by - tigger on 02/17/2002 14:54:43

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources