Geeks With Blogs
Nouman Zakir while (true) { Post.NewArticle(); }

You can add optional parameters in stored procedures by setting a default value for each parameter that you want to make optional. The default value is typically NULL, but it's not necessary.


Example:

CREATE PROCEDURE dbo.mySP
@firstParam VARCHAR(32) = NULL,
@secondParam INT = NULL
AS

BEGIN

SET NOCOUNT ON
SELECT *
FROM [TableName]
WHERE
Param1 = @firstParam
AND Param2 = @secondParam

END
GO

--
EXEC dbo.
mySP @firstParam ='bar', @secondParam =4
EXEC dbo.
mySP @firstParam ='bar'
EXEC dbo.
mySP @secondParam =4
EXEC dbo.
mySP 'bar',4
EXEC dbo.
mySP 'bar'
EXEC dbo.
mySP
GO

DROP PROCEDURE dbo.
mySP
GO

Posted on Saturday, February 28, 2009 5:48 AM SQL | Back to top


Comments on this post: Optional parameters in SQL Stored Procedures

# re: Optional parameters in SQL Stored Procedures
Requesting Gravatar...
Nice one. What if you have some optional parameters and some mandatory ones? Do they need to come in a certain order?
Left by Ed on Jan 22, 2010 10:22 AM

# re: Optional parameters in SQL Stored Procedures
Requesting Gravatar...
Red Gate software is basically a software that gives system administrators and developers tools in doing their job. The roots of this software can be traced back to Cambridge in the United Kingdom. It has been in the programming business for quite some time already dating back 1999. - Madison Pharmacy Associates
Left by MAdison Pharmacy Associates on Feb 20, 2012 6:02 PM

# re: Optional parameters in SQL Stored Procedures
Requesting Gravatar...
Good, But if set GetDate() in parameter it throws an error. Is there any way to define it.
Left by Azhar Hussain Tarar on Jan 19, 2013 3:52 AM

Your comment:
 (will show your gravatar)
 


Copyright © Nouman Zakir | Powered by: GeeksWithBlogs.net | Join free