sp_executesql bug?

Question

sp_executesql bug?

Henrik Staun Poulsen-237657

Old Hand

Points: 346
More actions
September 27, 2005 at 3:32 am

#66904

Dear All,
I'm trying to call some sql from VS2003 to SQL 2005 June CTR Standard edition. But I keep getting a weird error message:
Msg 8178, Level 16, State 1, Line 1 Parameterized Query '(@UseASpecialGroup char(1),@T varchar(5), @U nvarchar(50))select' expects parameter @U, which was not supplied.
Here is what I do:
sp_executesql N'select 1, @UseASpecialGroup',
N'@UseASpecialGroup char(1),@T varchar(5), @U nvarchar(50)',
@UseASpecialGroup='T', @T='14572', @U=default
When I change it to
sp_executesql N'select 1, @UseASpecialGroup', N'@UseASpecialGroup char(1),@T varchar(5), @U nvarchar(50)', @UseASpecialGroup='T', @T='14572', @U='1234'
it works.
Best regards
Henrik Staun Poulsen
Stovi Software, Denmark

Viewing 5 posts - 1 through 4 (of 4 total)

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

Henrik Staun Poulsen-237657 Old Hand Points: 346 More actions · Answer 1

I've done further tests, and it looks as if there is a 64 byte limit on the length of the parameters. Anyone else seen this?

Henrik

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here is my guess. It doesn't like you using default as the value to the parameter. Just a another guess, but it might work if you quote default ('default'). Don't know if it will work the way you want it though. I haven't run across this problem using sp_executesql.

Lynn

Henrik Staun Poulsen-237657 Old Hand Points: 346 More actions · Answer 3

Lynn,

I've tried both 'default' and ('default') to no avail. The parameter comes from my C# application, where I've skipped (not added) the @U parameter, because I want the default value for the SPROC that I'm going to call.

Best regards,

Henrik

scporich SSC-Addicted Points: 493 More actions · Answer 4

Try NULL instead of DEFAULT. DEFAULT is a keyword that is not in context for this use. Generally, DEFAULT is NULL as far as SP/UDF parameters go; you can supply NULL or DEFAULT to a parameter that has a default assigned, and SQL will do the same thing. sp_ExecuteSQL is kind of like sprintf, and it could not figure out what DEFAULT meant, since it is not a string, number, date or NULL.

I tried this using your code, and it works fine.

One thing: The code you supplied does not even even use the @U parameter. It returns 2 columns containing 1 and whatever is in @T. If you left something out for brevity or secrecy, fine, but let us know...

If your desired result is a table with 2 columns (or 3...), you may want to consider a table (or inline, depending on the complexity) UDF, which will take DEFAULT just fine and return your table with alacrity.

Hope this helps!