Post reply

How to use a select statement to get a parameter

  • March 21, 2017 at 11:17 am

    #336167

    Here's the beginning of my sp. I want to be able to supply the AccountNumber when executing the procedure then, the @VisitID will be supplied by the set statement. Is this possible?


    ALTER procedure [dbo].[spRadialAnalyticsDataExtract_ADLAssessment]
    (
    @VisitID varchar(30),
    @AccountNumber varchar(30)
    )
    as
    set @VisitID=(select VisitID from livefdb.dbo.RegAcct_Main where SourceID='BRO' and AccountNumber=@AccountNumber)

    Execute stored procedure...............
    exec spRadialAnalyticsDataExtract_ADLAssessment '2345678'

  • March 21, 2017 at 11:32 am

    #1934202

    Try something like:

    ALTER procedure [dbo].[spRadialAnalyticsDataExtract_ADLAssessment]
    (
    @AccountNumber varchar(30)
    )
    as

    DECLARE @VisitID varchar(30)

    SELECT @VisitID = VisitID from livefdb.dbo.RegAcct_Main where SourceID='BRO' and AccountNumber=@AccountNumber)

    You'd want to keep in mind that you need to make sure you aren't going to have multiple, different VisitIDs with that query.

    Sue

  • March 21, 2017 at 11:49 am

    #1934205

    Wonderful. Thanx.

  • March 21, 2017 at 12:07 pm

    #1934208

    Or, if you want to allow @VisitID to be specified but not to require it:


    ALTER procedure [dbo].[spRadialAnalyticsDataExtract_ADLAssessment]
    (
    @AccountNumber varchar(30),
    @VisitID varchar(30) = NULL
    )
    AS
    IF @VisitID IS NULL
        SET @VisitID=(select VisitID from livefdb.dbo.RegAcct_Main where SourceID='BRO' and AccountNumber=@AccountNumber)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • March 21, 2017 at 2:39 pm

    #1934224

    One rule of thumb I follow is:
    If you want the user to be able to enter a value, include it as a required parameter, but supply a default in case the user leaves it out. Or have coding to alert the user that the parameter is required. - Scott's post.
    If the value is only going to be set by some other code within the stored procedure, then the parameter/variable needs to be within the stored proc - Sue's post.

    So it all depends on how the value can be set.

    -SQLBill

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply