Executing a Stored Procedure That Expects Parameters

[clydejones]

Hi All,
I have a stored porcedure with parameters, however the parameter values may or may not be provided. I have this stored procedure attached to a crystal report and everything works fine, but when I try to execute the stored procedure through the Query Analyzer or through SSRS, I get errors like "expecting parameters". Is there a way around this? I assume crystal reports is sending a null value for parameters that are left blank. Help Please??

 

[clydejones]

Never Mind. Sorry

 

[SQLSister]

Either execute with a null value specifically provided or set default values in the sp and then only specify the values you are providing.

So suppose you sp has 3 parameters one of which you are not providing.

you can execute this way:

Exec usp_test 1, null, 'test'

or if you define the default values, you can execute this way:

Exec usp_test @var1 = 1, @var3 = 'test'

Either way of course the sp has to know how to handle the null values internally.

Questions about posting. See faq183-874