CODEWITHSUNDEEP
sql-serverstored-proceduresparameter-passing
Weihui Guo
Weihui Guo

Reputation: 3997

Execute stored procedure with a nullable parameter from SQL server management studio

I have a stored procedure, uspUser_GetUserDetails, with several parameters. It gets called something like this:

uspUser_GetUserDetails 'LastName','FirstName', 'UserId', 'dtDate', 'DistrictId'

How do I run the stored procedure if the dtDate parameter is null. I tried 

uspUser_GetUserDetails 'LastName','FirstName','UserId','','DistrictId'

but it doesn't do anything. Will something like this work? 

uspUser_GetUserDetails 'LastName','FirstName','UserId',null,'DistrictId'

This takes forever to execute the query without returning any result. Note that the question is not about how to create a SP with a nullable parameter. My SP works fine from C#, it shows sqlvalue {null} in the parameters. I just want to test it quickly from SQL server management studio and know how to pass null parameter to it in this situation.

Upvotes: 2

Views: 11810

Answers (2)

Weihui Guo
Weihui Guo

Reputation: 3997

Turns out the syntax is fine for all the SPs I tried later. And there is no performance issue. The problem with uspUser_GetUserDetails 'LastName','FirstName','UserId',null,'DistrictId' before is probably related to "Timeout expired".

Alternatively, EXEC [dbo].[uspSPName] @LastName = N'LastName' @FirstName = N'', @UserId = 0, @dtDate = NULL, @DistrictId = 1

Upvotes: 2

KuldipMCA
KuldipMCA

Reputation: 3149

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS
BEGIN

END

Use Optional parameter, then no need to pass in calling of procedure.

Upvotes: 2

Related Questions