When we create function we can use default value for parameters if we need so, is there any way to create a procedure with default value parameter in SQL Server so when we don’t pass the value for that parameter they can use default values? Yes, we can. In this article we will create procedure with default parameter value.
Let's see default template provided by microsoft:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO
Now let's create a simple procedure in SQL Server
CREATE PROCEDURE SearchProducts @Category varchar(50) ,@PriceUpto DECIMAL(18,2) AS SELECT P.* FROM Products P WHERE P.Category = @Category AND P.Price <= @PriceUpto
We created a procedure SearchProducts when we need to use it or test it we always have to provide the category and price, say
EXEC SearchProducts 'Toys', 50.00
Now let's say we want to create a procedure with default category 'Toys' and Price upto 50.00 then we need to change our procedure like this
CREATE PROCEDURE SearchProducts @Category varchar(50) = 'Toys' ,@PriceUpto DECIMAL(18,2) = 50.00 AS SELECT P.* FROM Products P WHERE P.Category = @Category AND P.Price <= @PriceUpto
Notice at the time of creating the parameter we also provided some values which will be used as a default value, If now we want to test/execute our procedure then simply we can use
And it will use 'Toys' for category parameter and 50.00 for Price upto parameter.
Having 10+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 8 years and still with the same company. Always ready to learn new technologies and tricks.
|By Ali Adravi On 14 May, 13 Viewed: 893|
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 359
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 482
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 1,158
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 329
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 4,852