Hello Chris.

Use the following stored procedures, this will help you

CREATE PROCEDURE sp_MySelect
@ID int = null
@city varchar(6) = null
@ccountry varchar(6) = nul
A
SELECT * FROM MyTabl
WHERE (@ID is null or ID = @ID
AND (@city is null or city = @City
AND (@country is null or country = @country
G

Now your stored procedure is fully optional

Thank

http://www.aspfaq.com/2348

(Followups set to .programming only. Please don't cross-post to irrelevant
groups.)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


[quoted text, click to view]

Hi,

I have a stored procedure :

CREATE PROCEDURE sp_MySelect
@ID int,
@city varchar(6),
@ccountry varchar(6)
AS
SELECT * FROM MyTable
WHERE
(ID = @ID or (@ID is null))
AND (City = @city or (@city is null))
AND (Country = @country or (@country is null))
GO

now, I want to execute this sp specifying a variable amount of parameters :
e.g. only specifying the 'city'
sp_MySelect "Sydney"
or city and country
sp_MySelect "Sydney" , "Australia"
or ...

as it is now do I have to specify the 3 arguments.

In other words : how do I implement optional parameters in SP ?

thanks

Chris

Hi,
I'm a beginner myself, but I guess by defining "default values" to those
parameters you want to omit.
For your code I'd write

@ID int,
@city varchar(6) = 'Sydney',
@ccountry varchar(6) = 'Australia'

Anyway, more ways I'd like to learn myself :)

--
With the best wishes,
Shaul Feldman

[quoted text, click to view]

hi chris,

Passing default value to the stored procedure parameter is the option with
you.See following example. whereby, im passing null value as a default to
all the parameters so that if no value is passed while executing the stored
procedure then null will be taken as default.

Also inside the procedure body im using CASE (see more help on this in BOL)
expression for checking condition of nullability So, following statement
will be evaluated, by checking the value for @id parameter if it is null
then condition will be evaluated as "id = id", meaning no filteration will
be done on ID column's value and all rows will be retrieved. if @id
parameter's value is not null then condition will be evaluated as "id = @id"
meaning filter the resultset for id's value matching with @id.

(ID = case when @ID is null then ID else @id end)


--stored procedure

CREATE PROCEDURE sp_MySelect
@ID int = null,
@city varchar(16) = null,
@country varchar(16) = null
AS
SELECT * FROM MyTable
WHERE
(ID = case when @ID is null then ID else @id end) AND
(City = case when @city is null then city else @city end)
AND
(Country = case when @country is null then country else
@country end)
GO

--Different ways to execute stored procedure.

sp_MySelect @city='city1'
sp_MySelect @city='city1', @country = 'country1'
sp_MySelect @id = 1, @city='city1', @country = 'country1'
--or if you are specifying all the parameters you can execute as follows:
sp_MySelect 1, 'city1', 'country1'



--
Vishal Parkar
vgparkar@yahoo.co.in