?
Solved

how to assign default value to a parameter

Posted on 2014-09-08
1
Medium Priority
?
150 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
[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
1 Comment
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

765 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