?
Solved

Default Option For Stored Procedure With IF - ELSE  Condition

Posted on 2011-03-21
3
Medium Priority
?
206 Views
Last Modified: 2013-11-05
I need to set a default for the stored procedure to begin automatically at type if no value is supplied -    Right now you need to specify 0 for the type condition to begin -----    So Basically the default will automatically check the IF(@Type=0) Condition and maybe a 1 or something else will skip that condition Completely - Any help will be appreciated

CREATE PROCEDURE Report].[Report2]
      
      @StartDate datetime,
      @EndDate datetime,
                @Type tinyint
AS

BEGIN

set transaction isolation level read uncommitted;

                declare @Type tinyint
      declare @pStartDate datetime
      declare @pEndDate datetime
      declare @IsMonthlyReport bit
                set @Type =0
      set @pStartDate = @StartDate
      set @pEndDate = @EndDate

      
      
       

      BEGIN TRY

            set @IsMonthlyReport = [CoRegReport].[rpt_IsMonthlyReport] (@pStartDate, @pEndDate)
       
       
       
       IF(@Type= 0)
       BEGIN
   
            
            
            
       IF (@IsMonthlyReport = 0)
            BEGIN
0
Comment
Question by:dlrickman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Expert Comment

by:Roman Gherman
ID: 35184341
Hi,
This should help you.

CREATE PROCEDURE Report].[Report2]
      
      @StartDate datetime,
      @EndDate datetime,
                @Type tinyint = 0
AS

Open in new window

0
 

Author Comment

by:dlrickman
ID: 35184448
okay but how do you get @Type to Begin as a default if no input parameter is specified
0
 
LVL 9

Accepted Solution

by:
Roman Gherman earned 2000 total points
ID: 35186874
JUST do not pass anything  to @Type

You will call the sproc like this (i.e SPROC NAME: Report2):
EXEC Report2 @StartDate = GETDATE(), @EndDate = GETDATE()

And that's all.

Just to clarify - you need to call a sproc which will have one optional parameter where if not passed it will have 0 as default. Did I understood correctly? or you meant something else?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question