Stored Procedure set column value from parameter

  • brianconner

    Old Hand

    Points: 370

    All,

    I have a stored procedure on SQL Server with 3 parameters and one of the Parameters @user I want to make the value of a column (see code below towards bottom)

    I am executing the stored procedure from ms access and passing the username as the value for @user (when the stored procedure executes I want all records to populate with the @user.

    Any help is greatly appreciated....

     

    Example including the error message.

    EXEC PI_Generate_Alternative_Schedule_List 'AD42870',,'UCLA'
    Msg 102, Level 15, State 1, Line 92
    Incorrect syntax near ','.

     

    ALTER PROCEDURE dbo.PI_Generate_Alternative_Schedule_List


    /*Created by Brian Conner on 7-19-2019*/

    @User varchar(20),
    @TIN varchar(10),
    @ParentGroupName varchar(250)

    AS



    /*Generate schedule list and ommit Targets previously existing in a schedule list*/
    INSERT INTO dbo.PI_Alternative_Schedule_List
    (
    [TIN]
    ,[GROUP_NAME]
    ,[PARENT_GROUP_NAME]
    ,[INCENTIVIZED]
    ,[DATE_INCENTIVIZED]
    ,[INCENTIVE_ADDRESS1]
    ,[INCENTIVE_ADDRESS2]
    ,[INCENTIVE_CITY]
    ,[INCENTIVE_STATE]
    ,[INCENTIVE_ZIP]
    ,[NPI]
    ,[NPI_LAST]
    ,[NPI_FRST]
    ,[CUR_ACTIVE]
    ,[MCID]
    ,[MBR_KEY]
    ,[MEMBER_ID]
    ,[MBR_LAST]
    ,[MBR_FRST]
    ,[BRTH_DT]
    ,[GNDR]
    ,[MBR_PHONE]
    ,[EFFDT]
    ,[TRMDT]
    ,[TGTD_TOT_CNT]
    ,[TGTD_CLSD_CNT]
    ,[PREV_CODED_CLSD_CNT]
    ,[HCC_01]
    ,[HCC_01_NM]
    ,[HCC_02]
    ,[HCC_02_NM]
    ,[HCC_03]
    ,[HCC_03_NM]
    ,[HCC_04]
    ,[HCC_04_NM]
    ,[HCC_05]
    ,[HCC_05_NM]
    ,[HCC_06]
    ,[HCC_06_NM]
    ,[HCC_07]
    ,[HCC_07_NM]
    ,[HCC_08]
    ,[HCC_08_NM]
    ,[HCC_09]
    ,[HCC_09_NM]
    ,[HCC_10]
    ,[HCC_10_NM]
    ,[HCC_11]
    ,[HCC_11_NM]
    ,[HCC_12]
    ,[HCC_12_NM]
    ,[HCC_13]
    ,[HCC_13_NM]
    ,[HCC_14]
    ,[HCC_14_NM]
    ,[HCC_15]
    ,[HCC_15_NM]
    ,[HCC_16]
    ,[HCC_16_NM]
    ,[HCC_17]
    ,[HCC_17_NM]
    ,[HCC_18]
    ,[HCC_18_NM]
    ,[HCC_19]
    ,[HCC_19_NM]
    ,[HCC_20]
    ,[HCC_20_NM]
    ,[HCC_21]
    ,[HCC_21_NM]
    ,[HCC_22]
    ,[HCC_22_NM]
    ,[HCC_23]
    ,[HCC_23_NM]
    ,[HCC_24]
    ,[HCC_24_NM]
    ,[HCC_25]
    ,[HCC_25_NM]
    ,[TGTD_OPEN]
    ,[SOAP_COMP]
    ,[SEGMENT]
    ,[PRFX]
    ,[OUTREACH]
    ,[EF_DATES]
    ,[DERIVED_EF_DATE]
    ,[PROGRAM_YEAR]
    ,[STATE]
    ,[Schedule_Date]
    ,[User]
    )

    SELECT
    T1.[TIN]
    ,[GROUP_NAME]
    ,[PARENT_GROUP_NAME]
    ,[INCENTIVIZED]
    ,[DATE_INCENTIVIZED]
    ,[INCENTIVE_ADDRESS1]
    ,[INCENTIVE_ADDRESS2]
    ,[INCENTIVE_CITY]
    ,[INCENTIVE_STATE]
    ,[INCENTIVE_ZIP]
    ,T1.[NPI]
    ,[NPI_LAST]
    ,[NPI_FRST]
    ,[CUR_ACTIVE]
    ,[MCID]
    ,[MBR_KEY]
    ,T1.[MEMBER_ID]
    ,[MBR_LAST]
    ,[MBR_FRST]
    ,[BRTH_DT]
    ,[GNDR]
    ,[MBR_PHONE]
    ,[EFFDT]
    ,[TRMDT]
    ,[TGTD_TOT_CNT]
    ,[TGTD_CLSD_CNT]
    ,[PREV_CODED_CLSD_CNT]
    ,[HCC_01]
    ,[HCC_01_NM]
    ,[HCC_02]
    ,[HCC_02_NM]
    ,[HCC_03]
    ,[HCC_03_NM]
    ,[HCC_04]
    ,[HCC_04_NM]
    ,[HCC_05]
    ,[HCC_05_NM]
    ,[HCC_06]
    ,[HCC_06_NM]
    ,[HCC_07]
    ,[HCC_07_NM]
    ,[HCC_08]
    ,[HCC_08_NM]
    ,[HCC_09]
    ,[HCC_09_NM]
    ,[HCC_10]
    ,[HCC_10_NM]
    ,[HCC_11]
    ,[HCC_11_NM]
    ,[HCC_12]
    ,[HCC_12_NM]
    ,[HCC_13]
    ,[HCC_13_NM]
    ,[HCC_14]
    ,[HCC_14_NM]
    ,[HCC_15]
    ,[HCC_15_NM]
    ,[HCC_16]
    ,[HCC_16_NM]
    ,[HCC_17]
    ,[HCC_17_NM]
    ,[HCC_18]
    ,[HCC_18_NM]
    ,[HCC_19]
    ,[HCC_19_NM]
    ,[HCC_20]
    ,[HCC_20_NM]
    ,[HCC_21]
    ,[HCC_21_NM]
    ,[HCC_22]
    ,[HCC_22_NM]
    ,[HCC_23]
    ,[HCC_23_NM]
    ,[HCC_24]
    ,[HCC_24_NM]
    ,[HCC_25]
    ,[HCC_25_NM]
    ,[TGTD_OPEN]
    ,[SOAP_COMP]
    ,[SEGMENT]
    ,[PRFX]
    ,[OUTREACH]
    ,T1.[EF_DATES]
    ,[DERIVED_EF_DATE]
    ,[PROGRAM_YEAR]
    ,[STATE]
    ,[Schedule_Date]
    ,@User AS [User]
    FROM [dbo].[PI_PEC_Alternatives] T1
    LEFT JOIN (SELECT DISTINCT TIN, NPI, MEMBER_ID, EF_DATES FROM [dbo].[PI_Alternative_Schedule_List]) T2 ON T2.[TIN] = T1.[TIN] AND T1.[NPI] = T2.[NPI] AND T2.[MEMBER_ID] = T1.[MEMBER_ID] AND T2.[EF_DATES] = T1.[EF_DATES]

    WHERE ((T1.[TIN] = @TIN OR T1.[PARENT_GROUP_NAME] = @ParentGroupName) AND T2.[MEMBER_ID] IS NULL) /*Filter Out Duplicates*/

  • drew.allen

    SSC Guru

    Points: 76492

    brianconner wrote:

    EXEC PI_Generate_Alternative_Schedule_List 'AD42870',,'UCLA'
    Msg 102, Level 15, State 1, Line 92
    Incorrect syntax near ','.

    This is the source of your error.  The procedure requires three parameters.  You've only supplied two.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • brianconner

    Old Hand

    Points: 370

    Hi Drew,

    In my where clause I have it accepting @TIN or @ParentGroupName is this the correct way to handle this?

    WHERE ((T1.[TIN] = @TIN OR T1.[PARENT_GROUP_NAME] = @ParentGroupName) AND T2.[MEMBER_ID] IS NULL)
  • brianconner

    Old Hand

    Points: 370

    I made the below change to my code in access (added single quotes around parameters) and it worked.

    Thanks Drew you were right I needed to submit all three parameters

     

    strSQL = "EXEC PI_Generate_Alternative_Schedule_List '" & gstrLogin & "','" & txt_Tin & "','" & strGroupName & "'"
    db.QueryDefs("PassThru_Alternative_Incentives").SQL = strSQL
    db.Execute ("PassThru_Alternative_Incentives")
  • This was removed by the editor as SPAM

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

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