July 19, 2019 at 7:56 pm
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*/
July 19, 2019 at 8:20 pm
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
July 19, 2019 at 8:45 pm
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)
July 19, 2019 at 8:54 pm
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")
July 21, 2019 at 12:13 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply