CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
hgcrpd
hgcrpd

Reputation: 1900

Stored Procedure with optional parameters but at least one to be required

Is it possible to create a stored procedure that has parameters which are all optional individually, but at least one of the parameters must be provided?

For example, if I have a procedure that updates a record, I must pass in a record id, and then at least one column to update. The procedure should check to ensure at least one additional parameter/column is provided.

Upvotes: 1

Views: 3361

Answers (3)

Deepan Chakravarthy
Deepan Chakravarthy

Reputation: 1

Use the following in where clause

where
(isnull(@param1,0)=0 or id=@param1)
and 
(isnull(@param2,'')='' or name=@param2)

Upvotes: 0

Jeff S
Jeff S

Reputation: 7484

I would do this in my programming language of choice instead of a stored proc. Why? This is the type of logic checking that TSQL is not very good at; the syntax for checking this will be "icky" and it will be slow.

Also, the biggest performance advantage a stored proc gives you is running compiled SQL. In this case, because the SQL needs to be dynamically built, you lose that advantage. Thus, why do it as a stored procedure?

Upvotes: 1

HLGEM
HLGEM

Reputation: 96600

I would put an if statement as the first action. 

IF @param1 is null and @param2 isnul and @param3 is null
Begin
--steps tpo raise an error or exit the proc
End

Upvotes: 2

Related Questions