CODEWITHSUNDEEP
c#sqlsql-servert-sql
Schultz9999
Schultz9999

Reputation: 8936

Why sp_executesql doesn't return results while direct SP call does?

This is confusing. For this declaration of the stored procedure:

CREATE PROCEDURE dbo.JobGet
    @jobGuid uniqueidentifier = NULL,

This line returns results:

exec dbo.JobGet @jobGuid ='BDEA1E43-9EC7-42B0-A386-903FE1749FF7'

And this one does not:

exec sp_executesql N'dbo.JobGet',N'@jobGuid uniqueidentifier',@jobGuid='BDEA1E43-9EC7-42B0-A386-903FE1749FF7'

Can anyone explain why?

Upvotes: 3

Views: 540

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

You need to add @jobGuid as a parameter when you call the SP. Without it the parameter will have the default value NULL.

exec sp_executesql N'dbo.JobGet @jobGuid',
                   N'@jobGuid uniqueidentifier',
                   @jobGuid='BDEA1E43-9EC7-42B0-A386-903FE1749FF7'

Upvotes: 3

Related Questions