Solved

how to assign default value to a parameter

Posted on 2014-09-08
1
142 Views
Last Modified: 2014-09-08
I need to set a default value to the the following parameters in my SQL query so that I do not have to pass them in via a form.
@gbloptState AS VARCHAR(4) ,   = 4-
@InsPrfCrdLtrOpt AS VARCHAR(1) = 1

Query

USE [EXPORTmdhpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[InsProfile_CredLetter_Parm]    Script Date: 9/8/2014 8:49:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/* ***************************************************************************************************/
/*              Insured Profile & Credentialing Letter                                                              */
/* **************************************************************************************************/

ALTER PROCEDURE [dbo].[InsProfile_CredLetter_Parm]
    (
      @gbloptState AS VARCHAR(4) ,
      @InsPrfCrdLtrOpt AS VARCHAR(1)
    )

AS
    SET NOCOUNT ON
    SELECT DISTINCT
            IC.LicenseNumber AS [License Nbr] ,
            RM.City AS City ,
            RM.State AS state ,
            RM.Zip AS zip ,
            Insured ,
            IC.GroupName AS GroupFirmName ,
            SUBSTRING(IC.Policy, 1, CHARINDEX('-', IC.Policy)) AS PolPrefix ,
            RM.Addr1 ,
            RM.Addr2 ,
            RM.Addr3 ,
            RM.Fax ,
            InsuredName = CASE WHEN RM.FirstName = ' ' THEN RM.LastName
                               ELSE RM.FirstName + ' '
                                    + CASE WHEN ISNULL(RM.MiddleInitial, ' ') = ' '
                                           THEN RM.LastName + ' '
                                           ELSE RM.MiddleInitial + ' '
                                                + RM.LastName + ' '
                                      END
                                    + CASE WHEN ISNULL(RM.Title, ' ') = ' '
                                           THEN RM.Designation
                                           ELSE RM.Title + ' '
                                                + RM.Designation
                                      END
                          END
    FROM    InsuredCov_All IC
            INNER JOIN wellandp.ReferMaster RM ON 'A' + IC.LicenseNumber = RM.ReferKey
    WHERE   ( LEFT(RM.ReferKey, 1) = 'A' )
            AND ( IC.SlotType <> 'S' )
            AND ( dbo.IsTail(IC.RiskType) = 'N' )
            AND ( @gbloptState LIKE '%99%'
                  OR SUBSTRING(IC.Policy, 1, CHARINDEX('-', IC.Policy)) = @gbloptState
                )
            AND ( ( @InsPrfCrdLtrOpt = '1' )
                  OR ( @InsPrfCrdLtrOpt = '2'
                       AND dbo.IsEPLI(IC.RiskType) = 'N'
                     )
                )
    ORDER BY insured
0
Comment
Question by:mburk1968
1 Comment
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40309910
Just set the value that you want in the parameter definition:

ALTER PROCEDURE [dbo].[InsProfile_CredLetter_Parm]
     (
       @gbloptState AS VARCHAR(4)  = '4',
       @InsPrfCrdLtrOpt AS VARCHAR(1) = '1'

.
.
.

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

863 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

Need Help in Real-Time?

Connect with top rated Experts

29 Experts available now in Live!

Get 1:1 Help Now