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

Question

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.

We need to see the SP and the indexes you have on your user table — JohnS
– JohnS, Commented May 26, 2015 at 3:25
"This takes forever to execute the query without returning any result.". Yes. Your proc is running with a NULL value but there is now a performance issue. — Nick.Mc
– Nick.Mc, Commented May 26, 2015 at 6:46

KuldipMCA · Accepted Answer · 2015-05-26 12:31:56Z

2

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.

answered May 26, 2015 at 12:31

KuldipMCA

3,1617 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zeek2 Over a year ago

But sometimes it is more convenient to always pass the parameter (e.g. if it can be null or not null).

Weihui Guo · Accepted Answer · 2015-06-01 01:50:34Z

2

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

edited Jun 1, 2015 at 1:50

answered May 26, 2015 at 13:39

Weihui Guo

4,0656 gold badges40 silver badges62 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related