Stored Procedures

[rohithere]

Hi Everybody,

I have created a stored procedure which takes multiple parameters.But the date range para passed is optional on the part of the user.In the stored procedure definition i have specified para for it.Now if the user does not select the date range,the stored procedure would fail because para is mentioned in its creation but is not supplied by the user,as he has not selected it.

Eg: mySp para1,para2,para3,para4,para5,para6

Now the user selects only the 1,2,5,6 values to be shown in the report.the stored procedure will give error.
It is important.Please can anyone solve my query?????

Thanx

 

[SQLSister]

Either define the default values as nulls in the SP definition, so they do not need to be passed in or have the user pass an empty value to the stored procedure. Just fixing the problem of sending or not sending the parameters will not make your program run correctly unless your programlogic accounts for not having these values. This can be done through case statements or if statement or dynamic SQL depending onthe complexity of what you are trying to do.

 

[JohnDTampaBay]

You can assign default values to the parameters which will be used if a value is not passed for a parameter at execution. Here's a very simple example:

CREATE PROC uspBlah
  @Something varchar(10) = NULL
AS BEGIN
  IF @Something IS NULL
    SELECT 'You did not pass a value for the parameter'
  ELSE
    SELECT 'You passed ' + @Something + ' as the parameter'
END

Check out CREATE PROCEDURE in BOL for a complete explanation. There are specific syntax issues for using DEFAULT values depending on whether you use pass values by name or position. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[rohithere]

Thankx.
It was really very helpful.the date field in the database does not accept NULL values.

 

[rohithere]

Hi,

While saving stored procedure i get the following error:

Error 1008: The select item identified by the order by number 1 contains a variable as part of the expression identifying a column position.Variables are only allowed when ordering by an expression referencing a column name.

Actually Iam passing column name for the order by clause as a parameter to the stored procedure.

Please explain to me what it is all about.How it can be rectified.

Thankx.