Best way to check a bunch of variables passed to a SP

24 Mar 2006 9:27 AM

Robert Bravery

HI all,
If I hav a SP like
CREATE PROCEDURE addclaim
@ctkey int,
@headerid int,
@cpkey int,
@brkref varchar(25),
@DET varchar(100),
@Status tinyint,
@peril int,
@cause int,
@resc int,
@fault int,
@polsection int,
@Assid int
AS

What is the best way to check that a value is passed to each variable, and
if a value is not passed to a particular variable, I need to raise an erro
describing which variable was not correctly passed
Thanks
RObert

24 Mar 2006 9:38 AM

Tibor Karaszi

None pf the parameters you specified were defined as optional, so SQL Server will generate an error
for you if no value is passed into one of the parameters.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Robert Bravery" <m*@u.com> wrote in message news:OMP9qOyTGHA.2244@TK2MSFTNGP14.phx.gbl...
> HI all,
> If I hav a SP like
> CREATE PROCEDURE addclaim
> @ctkey int,
> @headerid int,
> @cpkey int,
> @brkref varchar(25),
> @DET varchar(100),
> @Status tinyint,
> @peril int,
> @cause int,
> @resc int,
> @fault int,
> @polsection int,
> @Assid int
> AS
>
> What is the best way to check that a value is passed to each variable, and
> if a value is not passed to a particular variable, I need to raise an erro
> describing which variable was not correctly passed
> Thanks
> RObert
>
>

24 Mar 2006 1:25 PM

Wayne Snyder

As Tibor says, if you do NOT pass a value, SQL will tell you wich variable
was not passed. However if you wish to do this on your own, with your
message, it would be something like this...

Create proc test
@a int = null, @b int = null

If @a is NULL
BEGIN
RAISERROR('Please pass a value for @a', 12,1)
RETURN
END

IF @b is NULL...

You can get fancier and build a single string and return a single raiserror,
which would probably be better...
but this is the general way you might do this.

--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC

I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.

Show quote

"Robert Bravery" wrote:

> HI all,
> If I hav a SP like
> CREATE PROCEDURE addclaim
> @ctkey int,
> @headerid int,
> @cpkey int,
> @brkref varchar(25),
> @DET varchar(100),
> @Status tinyint,
> @peril int,
> @cause int,
> @resc int,
> @fault int,
> @polsection int,
> @Assid int
> AS
>
> What is the best way to check that a value is passed to each variable, and
> if a value is not passed to a particular variable, I need to raise an erro
> describing which variable was not correctly passed
> Thanks
> RObert
>
>
>