microsoft.public.sqlserver.server - Passing Values To A Stored Procedure

Author

Passing Values To A Stored Procedure

Arpan De

2002-11-11, 12:23 pm

Consider the following stored procedure code snippet:

CREATE PROCEDURE spPaySlip
@loc varchar(50)
AS
SELECT...........WHERE..........

The variable @loc can be either 'allloc' or 'selloc'. If the value is 'selloc', then another input parameter should be passed to the procedure which means that apart from the variable @loc, there will be a 2nd variable, say @locname, as well. The 2nd variable, @locname, will not be needed if the value of @loc is 'allloc'. So if the stored procedure is framed as follows:

CREATE PROCEDURE spPaySlip
@loc varchar(10),
@locname varchar(200)
AS
SELECT...........WHERE..........

& the above procedure is executed in the Query Analyzer using

EXEC spPaySlip 'selloc','New York'

there's no problem but as I have said before, if 'allloc' is passed to the variable @loc, then the variable @locname will not be needed. So how do I execute the stored procedure if I am passing 'allloc' to the variable @loc since if I do the following

EXEC spPaySlip 'allloc'

an error will be thrown since the variable @locname expects a value? One way this can be done is like this

EXEC spPaySlip 'allloc',''

but is there any other way by which I can "tell" the procedure that if 'allloc' is passed, then do not expect any value for the variable @locname but if 'selloc' is passed, expect a value for the variable @locname?

Thanks,

Arpan

Tony Rogerson

2002-11-11, 12:23 pm

You can set a default value for @locname ...

@locname varchar(200) = NULL

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]

"Arpan De" <arpan_de5A@rediffmail.com> wrote in message news:ettbpgaiCHA.1336@tkmsftngp11...
Consider the following stored procedure code snippet:

CREATE PROCEDURE spPaySlip
@loc varchar(50)
AS
SELECT...........WHERE..........

The variable @loc can be either 'allloc' or 'selloc'. If the value is 'selloc', then another input parameter should be passed to the procedure which means that apart from the variable @loc, there will be a 2nd variable, say @locname, as well. The 2nd variable, @locname, will not be needed if the value of @loc is 'allloc'. So if the stored procedure is framed as follows:

CREATE PROCEDURE spPaySlip
@loc varchar(10),
@locname varchar(200)
AS
SELECT...........WHERE..........

& the above procedure is executed in the Query Analyzer using

EXEC spPaySlip 'selloc','New York'

there's no problem but as I have said before, if 'allloc' is passed to the variable @loc, then the variable @locname will not be needed. So how do I execute the stored procedure if I am passing 'allloc' to the variable @loc since if I do the following

EXEC spPaySlip 'allloc'

an error will be thrown since the variable @locname expects a value? One way this can be done is like this

EXEC spPaySlip 'allloc',''

but is there any other way by which I can "tell" the procedure that if 'allloc' is passed, then do not expect any value for the variable @locname but if 'selloc' is passed, expect a value for the variable @locname?

Thanks,

Arpan

Arpan De

2002-11-11, 1:23 pm

Hi Tony,

Thanks for your response. What you have suggested has worked out but I have encountered a somewhat similar problem again. Like the @loc variable, the procedure has another variable named @emp whose value can be either 'allemp' or 'selemp'. If @emp='selemp', another variable, say, @empname has to be used but if @emp='allemp', then the variable @empname should not come in the picture. So as you have suggested, this is what I have done:

CREATE PROCEDURE spPaySlip
@loc varchar(10),
@emp varchar(10),
@locname varchar(1000)=NULL,
@empname varchar(2000)=NULL
AS..............

Now suppose I am executing this procedure by passing 'allloc' to the variable @loc & 'selemp' to the variable @emp. Since I am passing 'selemp' to @emp, the variable @empname should be populated with some values but @locname will be NULL only(since I am passing 'allloc' to @loc). This is how I am executing the stored procedure in the Query Analyzer:

EXEC spPaySlip 'allloc','selemp',"'MRS. ELIZABETH'" --no value for the variable @locname

no records are retrieved but if I change the above to

EXEC spPaySlip 'allloc','selemp','',"'MRS ELIZABETH'" --passing an empty string to @locname

then the correct records get retrieved. Is there any way this can be avoided & just by using the 1st EXEC statement, the correct records can be retrieved?

Thanks once again,

Regards,

Arpan

"Tony Rogerson" <tonyrogerson@sqlserver.eu.com> wrote in message news:eQORVoaiCHA.2672@tkmsftngp09...
You can set a default value for @locname ...

@locname varchar(200) = NULL

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]

"Arpan De" <arpan_de5A@rediffmail.com> wrote in message news:ettbpgaiCHA.1336@tkmsftngp11...
Consider the following stored procedure code snippet:

CREATE PROCEDURE spPaySlip
@loc varchar(50)
AS
SELECT...........WHERE..........

The variable @loc can be either 'allloc' or 'selloc'. If the value is 'selloc', then another input parameter should be passed to the procedure which means that apart from the variable @loc, there will be a 2nd variable, say @locname, as well. The 2nd variable, @locname, will not be needed if the value of @loc is 'allloc'. So if the stored procedure is framed as follows:

CREATE PROCEDURE spPaySlip
@loc varchar(10),
@locname varchar(200)
AS
SELECT...........WHERE..........

& the above procedure is executed in the Query Analyzer using

EXEC spPaySlip 'selloc','New York'

there's no problem but as I have said before, if 'allloc' is passed to the variable @loc, then the variable @locname will not be needed. So how do I execute the stored procedure if I am passing 'allloc' to the variable @loc since if I do the following

EXEC spPaySlip 'allloc'

an error will be thrown since the variable @locname expects a value? One way this can be done is like this

EXEC spPaySlip 'allloc',''

but is there any other way by which I can "tell" the procedure that if 'allloc' is passed, then do not expect any value for the variable @locname but if 'selloc' is passed, expect a value for the variable @locname?

Thanks,

Arpan

Jasper Smith

2002-11-11, 2:23 pm

If you have optional paramaters and don't want to supply
all of them you must use named parameters or explicitly
pass NULL/default value if passing only values e.g.

EXEC spPaySlip 'allloc','selemp',NULL,'MRS ELIZABETH'

EXEC spPaySlip @loc = 'allloc',
@emp = 'selemp',
@empname = 'MRS ELIZABETH'

--
HTH

Jasper Smith (SQL Server MVP)

Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm

"Arpan De" <arpan_de5A@rediffmail.com> wrote in message
news:OONUvJbiCHA.1688@tkmsftngp09...
Hi Tony,

Thanks for your response. What you have suggested has worked out but I have
encountered a somewhat similar problem again. Like the @loc variable, the
procedure has another variable named @emp whose value can be either 'allemp'
or 'selemp'. If @emp='selemp', another variable, say, @empname has to be
used but if @emp='allemp', then the variable @empname should not come in the
picture. So as you have suggested, this is what I have done:

CREATE PROCEDURE spPaySlip
@loc varchar(10),
@emp varchar(10),
@locname varchar(1000)=NULL,
@empname varchar(2000)=NULL
AS..............

Now suppose I am executing this procedure by passing 'allloc' to the
variable @loc & 'selemp' to the variable @emp. Since I am passing 'selemp'
to @emp, the variable @empname should be populated with some values but
@locname will be NULL only(since I am passing 'allloc' to @loc). This is how
I am executing the stored procedure in the Query Analyzer:

EXEC spPaySlip 'allloc','selemp',"'MRS. ELIZABETH'" --no value for the
variable @locname

no records are retrieved but if I change the above to

EXEC spPaySlip 'allloc','selemp','',"'MRS ELIZABETH'" --passing an empty
string to @locname

then the correct records get retrieved. Is there any way this can be avoided
& just by using the 1st EXEC statement, the correct records can be
retrieved?

Thanks once again,

Regards,

Arpan

"Tony Rogerson" <tonyrogerson@sqlserver.eu.com> wrote in message
news:eQORVoaiCHA.2672@tkmsftngp09...
You can set a default value for @locname ...

@locname varchar(200) = NULL

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]

"Arpan De" <arpan_de5A@rediffmail.com> wrote in message
news:ettbpgaiCHA.1336@tkmsftngp11...
Consider the following stored procedure code snippet:

CREATE PROCEDURE spPaySlip
@loc varchar(50)
AS
SELECT...........WHERE..........

The variable @loc can be either 'allloc' or 'selloc'. If the value is
'selloc', then another input parameter should be passed to the procedure
which means that apart from the variable @loc, there will be a 2nd variable,
say @locname, as well. The 2nd variable, @locname, will not be needed if the
value of @loc is 'allloc'. So if the stored procedure is framed as follows:

CREATE PROCEDURE spPaySlip
@loc varchar(10),
@locname varchar(200)
AS
SELECT...........WHERE..........

& the above procedure is executed in the Query Analyzer using

EXEC spPaySlip 'selloc','New York'

there's no problem but as I have said before, if 'allloc' is passed to
the variable @loc, then the variable @locname will not be needed. So how do
I execute the stored procedure if I am passing 'allloc' to the variable @loc
since if I do the following

EXEC spPaySlip 'allloc'

an error will be thrown since the variable @locname expects a value? One
way this can be done is like this

EXEC spPaySlip 'allloc',''

but is there any other way by which I can "tell" the procedure that if
'allloc' is passed, then do not expect any value for the variable @locname
but if 'selloc' is passed, expect a value for the variable @locname?

Thanks,

Arpan

Sponsored Links