sp_executesql doesn't pass parameters, exec does

Question

sp_executesql doesn't pass parameters, exec does

ma-516002

Hall of Fame

Points: 3359
More actions
February 28, 2010 at 3:38 am

#211747

I have a stored procedure which expects a few parameters, all have a default value. The procedure returns a table, that is it ends with SELECT ... FROM. I call this procedure from C# with a DbCommand of type set to stored procedure. The profiler show the following command is passed:
exec sp_executesql N'[Analysis].[pInterfaceStatistic]',N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)',@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'
The output is an empty table.
When I run the following command from within ssms
exec [Analysis].[pInterfaceStatistic] @PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'
the output is a table with a few rows, as expected.
I added a few print statements to the procedure and it turns out, that the values are not passed to the procedure; it is run with all parameters set to their default values.
I also tried to run sp_executesql as administrator but to no avail, the parameters are not passed.
What is wrong here?

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

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

ma-516002 (2/28/2010)
I have a stored procedure which expects a few parameters, all have a default value. The procedure returns a table, that is it ends with SELECT ... FROM. I call this procedure from C# with a DbCommand of type set to stored procedure. The profiler show the following command is passed:
exec sp_executesql N'[Analysis].[pInterfaceStatistic]',N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)',@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'
The output is an empty table.
When I run the following command from within ssms
exec [Analysis].[pInterfaceStatistic] @PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'
the output is a table with a few rows, as expected.
I added a few print statements to the procedure and it turns out, that the values are not passed to the procedure; it is run with all parameters set to their default values.
I also tried to run sp_executesql as administrator but to no avail, the parameters are not passed.
What is wrong here?

You're passing this command:

exec sp_executesql N'[Analysis].[pInterfaceStatistic]',N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)',@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'

Why don't you pass this command?

exec [Analysis].[pInterfaceStatistic] @PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'

Or better still:

[exec Analysis.pInterfaceStatistic @PeriodStartName=N'Y2010', @PeriodEndName=N'Y2010', @PeriodType=N'Y']

- less chance of a clash between string delimiters and object identifiers.

In any case, I can't see why you need sp_executesql here.

Cheers

ChrisM

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ma-516002 Hall of Fame Points: 3359 More actions · Answer 2

That statement is generated by System.Data.Common.DbCommand, you cannot modify it.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

ma-516002 (2/28/2010)
That statement is generated by System.Data.Common.DbCommand, you cannot modify it.

Thanks for the extra info.

I think the problem is with setting up the command string in your interface code. SQL Server hasn't been told where to apply the declared parameters. It has them but doesn't know what to do with them.

This works:

execute sp_executesql N'Analysis.pInterfaceStatistic @PeriodStartName, @PeriodEndName, @PeriodType',N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)',

@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'

because the parameter usage is defined - in the stmt argument of sp_executesql, right where they're expected to be.

Cheers

ChrisM

Edit: Changed schema back to OP's from test environment

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ma-516002 Hall of Fame Points: 3359 More actions · Answer 4

No, your suggested command has the same result as the auto generated one, an empty table.

This looks like there is something missing to sp_executesql or the target procedure in terms of like permissions or something.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

ma-516002 (2/28/2010)
No, your suggested command has the same result as the auto generated one, an empty table.
This looks like there is something missing to sp_executesql or the target procedure in terms of like permissions or something.

It works in my little test here. The original syntax did not, for the reason already explained. If it were permissions, you'd get an error, not an empty table.

Can you post the command (the one I suggested) that you see in profiler, please?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

ma-516002 (2/28/2010)
No, your suggested command has the same result as the auto generated one, an empty table.
This looks like there is something missing to sp_executesql or the target procedure in terms of like permissions or something.

Also, have you tried running the modified command in SSMS?

exec sp_executesql N'[Analysis].[pInterfaceStatistic] @PeriodStartName, @PeriodEndName, @PeriodType',

N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)',

@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ma-516002 Hall of Fame Points: 3359 More actions · Answer 7

I'm not sure what you mean. The command I got from the profiler is the first one in my first post. As I said, this command is generated by the .Net runtime and this is for good reason not modifyable. I used the profiler in the first place to check wether a command is sent to the database at all. I don't care about the command itself, because I use the offical MS api to access a database object so I must not know about how the runtime retrieves the data I ask for, I must rely on the runtime to do it. At least, that is the idea behind an api, isn't it. I'll dig deeper into this, see what I can find.

ma-516002 Hall of Fame Points: 3359 More actions · Answer 8

Don't know what happened there. I recreated the database and everything works now 🙂 (Gremlins?)

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 9

{Edit} Sorry... posted an incorrect observation.

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 10

From your original post:

The command I got from the profiler is the first one in my first post. As I said, this command is generated by the .Net runtime and this is for good reason not modifyable.

Based on the below simplified test cases, the command being sent to SQL Server is incorrect as the @Stmt parameter does not include the parameters of the inner stored procedure.

Original statement:

exec sp_executesql N'[Analysis].[pInterfaceStatistic]'

, N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)'

,@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'

Required statement:

exec sp_executesql N'[Analysis].[pInterfaceStatistic] @PeriodStartName,@PeriodEndName,@PeriodType'

, N'@PeriodStartName nvarchar(5),@PeriodEndName nvarchar(5),@PeriodType nvarchar(1)'

,@PeriodStartName=N'Y2010',@PeriodEndName=N'Y2010',@PeriodType=N'Y'

See the below code for a reproducible test harness, which shows that:

When @stmt is "EXECUTE dbo.test_executesql @MyParm = @MyParm", the parameter is available to the sp.

When @stmt is "EXECUTE dbo.test_executesql", the parameter is NOT available to the sp.

Outputs of the test cases are:

with paramater EXECUTE dbo.test_executesql @MyParm = @MyParm
MyParmValue
--------------------------
@MyParm value is hello
without paramater EXECUTE dbo.test_executesql
MyParmValue
------------------------
@MyParm is NULL

-- Set "results to text" - hit CTRL-T

USE tempdb

GO

IFOBJECT_ID('dbo.test_executesql') IS NOT NULL

DROP PROCEDURE dbo.test_executesql

GO

CREATE PROCEDURE dbo.test_executesql

(@MyParmVARCHAR(255)= NULL

)

AS

SELECTCASE WHEN @MyParm IS NULL THEN '@MyParm is NULL'

ELSE '@MyParm value is ' + @MyParm

END AS MyParmValue

GO

SETNOCOUNT ON;

DECLARE @rcINT

,@MyParmVARCHAR(255)

,@StmtNVARCHAR(MAX)

,@paramsNVARCHAR(MAX)

SET@MyParm=' hello '

SET@params=N'@MyParmvarchar(255)'

SET@Stmt= N'EXECUTE dbo.test_executesql @MyParm = @MyParm';

PRINT'with paramater ' + @Stmt

EXEC@rc = sp_executesql @stmt = @Stmt , @params = @params , @MyParm = @MyParm

SET@Stmt= N'EXECUTE dbo.test_executesql';

PRINT'without paramater ' + @Stmt

EXEC@rc = sp_executesql @stmt = @Stmt , @params = @params , @MyParm = @MyParm

GO

SQL = Scarcely Qualifies as a Language

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

ma-516002 (2/28/2010)
Don't know what happened there. I recreated the database and everything works now 🙂 (Gremlins?)

Well done - but your observation that rebuilding the db solved the problem is almost certainly coincidental.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

peter.howard SSC Journeyman Points: 95 More actions · Answer 12

Probably just a PEBKAC or ID-10T error with your instance of SQL Server.