March 6, 2013 at 3:35 am
Hi all,
Bit rusty but what I am trying to do looks easy and can't see what the hell I have done wrong...
I am trying to create a stored proc for our developers, the proc will validate data and should return any issue with a record.
I am getting the following ever so useful error...
Server: Msg 170, Level 15, State 1, Procedure STP_Agent_upload, Line 92
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 92: Incorrect syntax near ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'.
Will post up the proc in my reply
March 6, 2013 at 3:35 am
CREATE PROCEDURE [dbo].[STP_Agent_upload]
(
-- List Input Parameters
@Agent_Number VARCHAR(50), --column A
@Date_Last_Payment DATETIME,--column C
@policy_number VARCHAR (50),--column D
@Agent_Payment_Amount SMALLMONEY,-- [Net] -> this is column M
@Row_Number VARCHAR (100),--PASS this from application!
@Return_Message VARCHAR (255) OUTPUT
)
AS BEGIN
--List Variables Here
DECLARE @Record_Count AS VARCHAR (100)--Used to flag whether there is an existing record
DECLARE @productAS VARCHAR (100)
DECLARE @Agent_URNAS VARCHAR (100)
DECLARE @InsurerAS VARCHAR (100)
DECLARE @Transaction_CounterAS VARCHAR (100)
DECLARE @Agent_Introducer_URNAS VARCHAR (100)
DECLARE @CompanyAS VARCHAR (100)
--Initialize the variables
SET @Record_Count =''
SET @product=''
SET @Agent_URN=''
SET @Insurer=''
SET @Transaction_Counter=''
SET @Agent_Introducer_URN=''
SET @Company=''
---------------------------------------------Calculations---------------------------------------------
--1. Find Product
SELECT @product = [Cover type] FROM policytype WHERE [policy prefix] = LEFT (@policy_number, 2)
IF @product = '' SET @Return_Message = @policy_number + ' failed, Policy prefix not found, this was row number ' + @Row_Number
--2. Find Agent
IF @Return_Message <> ''
SELECT @Agent_URN = [Agent URN] FROM [AgentDetails] WHERE [Agent Number] = @Agent_Number
IF @Agent_URN = '' SET @Return_Message = @policy_number +' failed, agent not found, this was row number ' + @Row_Number
--3. Find Insurer
IF @Return_Message <> ''
SELECT @Insurer = MAX ([Transaction date]) FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number
IF @Insurer = '' SET @Return_Message = @policy_number +' failed, insurer not found, this was row number ' + @Row_Number
--4. Find Transaction Counter
IF @Return_Message <> ''
SELECT @Transaction_Counter = [Transaction Counter] FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [policy number] = @policy_number AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')
IF @Transaction_Counter = '' SET @Return_Message = @policy_number + ' failed, Transaction Counter not found, this was row number ' + @Row_Number
--5. Find Agent Introducer
IF @Return_Message <> ''
SELECT @Agent_Introducer_URN = [agents introducer] FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number
IF @Agent_Introducer_URN = '' SET @Return_Message = @policy_number +' failed, agent introducer not found, this was row number ' + @Row_Number
--6. Company
IF @Return_Message <> ''
SELECT @Company = [company name] FROM Accounts WHERE [transaction date] = '@Date_Last_Payment' AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number
IF @Company = '' SET @Return_Message = @policy_number +' failed, company not found, this was row number ' + @Row_Number
----------------------------------------------------------------validation----------------------------------------------------------------------
--1. Agent Paymnt
IF @Agent_Payment_Amount >0 AND @product <> '' and @Agent_URN <> ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: agent payment needs to be a negative amount.'
--2. @Agent_Number
IF @Agent_Number = ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no agent number.'
--3. @Date_Last_Payment
IF @Agent_Number = ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no transaction date.'
--4. @policy_number
IF @Agent_Number = ''
SET @Return_Message = 'Failure this was row number: ' + @Row_Number + ' Reason: there is no policy number.'
--5. @Agent_Payment_Amount
IF @Agent_Number = ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no agent payment amount.'
--6. Search for combination of inputs
IF @Return_Message <> ''
SELECT @Record_Count = COUNT ([Policy number]) FROM accounts WHERE [Policy number] = @policy_number AND [Agent] = @Agent_Number AND [transaction date] = @Date_Last_Payment AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')
IF @Record_Count = '0'
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'
March 6, 2013 at 3:50 am
You have single quoted a variable, making it a string-type constant: '@Date_Last_Payment'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2013 at 3:58 am
ChrisM@Work (3/6/2013)
You have single quoted a variable, making it a string-type constant: '@Date_Last_Payment'
Hi Chris,
I have temporarily changed it to a varchar - in the input parameters bit however I still get the following error
Server: Msg 170, Level 15, State 1, Procedure STP_Agent_upload, Line 92
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 92: Incorrect syntax near ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'.
Thanks
March 6, 2013 at 4:03 am
Plateau (3/6/2013)
ChrisM@Work (3/6/2013)
You have single quoted a variable, making it a string-type constant: '@Date_Last_Payment'Hi Chris,
I have temporarily changed it to a varchar - in the input parameters bit however I still get the following error
Server: Msg 170, Level 15, State 1, Procedure STP_Agent_upload, Line 92
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 92: Incorrect syntax near ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'.
Thanks
Have you removed the single quotes from around the variable - everywhere it appears in the sproc?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2013 at 4:20 am
ChrisM@Work (3/6/2013)
Plateau (3/6/2013)
ChrisM@Work (3/6/2013)
You have single quoted a variable, making it a string-type constant: '@Date_Last_Payment'Hi Chris,
I have temporarily changed it to a varchar - in the input parameters bit however I still get the following error
Server: Msg 170, Level 15, State 1, Procedure STP_Agent_upload, Line 92
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 92: Incorrect syntax near ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'.
Thanks
Have you removed the single quotes from around the variable - everywhere it appears in the sproc?
Hi Chris,
Not sure I understand. Sorry...
The colour of the statement seem to show it is correct?
Or do you mean the initialising bit is wrong?
If I cut my query right down - then I am still getting incorrect syntax
CREATE PROCEDURE [dbo].[STP_Agent_upload]
(
-- List Input Parameters
@Agent_Number VARCHAR(50), --column A
@Date_Last_Payment VARCHAR(50),--column C
@policy_number VARCHAR (50),--column D
@Agent_Payment_Amount VARCHAR (50),-- [Net] -> this is column M
@Row_Number VARCHAR (100),--PASS this from IMS!
@Return_Message VARCHAR (255) OUTPUT
)
AS BEGIN
--List Variables Here
DECLARE @Record_Count AS VARCHAR (100)--Used to flag whether there is an existing record
DECLARE @productAS VARCHAR (100)
DECLARE @Agent_URNAS VARCHAR (100)
DECLARE @InsurerAS VARCHAR (100)
DECLARE @Transaction_CounterAS VARCHAR (100)
DECLARE @Agent_Introducer_URNAS VARCHAR (100)
DECLARE @CompanyAS VARCHAR (100)
---------------------------------------------Calculations---------------------------------------------
--1. Find Product
SELECT @product = [Cover type] FROM policytype WHERE [policy prefix] = LEFT (@policy_number, 2)
IF @product = '' SET @Return_Message = @policy_number
March 6, 2013 at 4:30 am
--4. Find Transaction Counter
IF @Return_Message <> ''
SELECT @Transaction_Counter = [Transaction Counter] FROM Accounts
WHERE [transaction date] = '@Date_Last_Payment' AND [policy number] = @policy_number AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')
IF @Transaction_Counter = '' SET @Return_Message = @policy_number + ' failed, Transaction Counter not found, this was row number ' + @Row_Number
Change to
--4. Find Transaction Counter
IF @Return_Message <> ''
SELECT @Transaction_Counter = [Transaction Counter] FROM Accounts
WHERE [transaction date] = @Date_Last_Payment AND [policy number] = @policy_number AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')
IF @Transaction_Counter = '' SET @Return_Message = @policy_number + ' failed, Transaction Counter not found, this was row number ' + @Row_Number
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2013 at 4:39 am
Thanks Chris!
Oh good god, not sure how I couldn't see that... Well sleep deprivation I guess
I have changed the code to the below but still get an error -
Server: Msg 170, Level 15, State 1, Procedure STP_Agent_upload, Line 92
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 92: Incorrect syntax near ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'.
------------------
CREATE PROCEDURE [dbo].[STP_Agent_upload]
(
-- List Input Parameters
@Agent_Number VARCHAR(50), --column A
@Date_Last_Payment DATETIME,--column C
@policy_number VARCHAR (50),--column D
@Agent_Payment_Amount SMALLMONEY,-- [Net] -> this is column M
@Row_Number VARCHAR (100),--PASS this from IMS!
@Return_Message VARCHAR (255) OUTPUT
)
AS BEGIN
--List Variables Here
DECLARE @Record_Count AS INT--Used to flag whether there is an existing record
DECLARE @productAS VARCHAR (100)
DECLARE @Agent_URNAS VARCHAR (100)
DECLARE @InsurerAS VARCHAR (100)
DECLARE @Transaction_CounterAS VARCHAR (100)
DECLARE @Agent_Introducer_URNAS VARCHAR (100)
DECLARE @CompanyAS VARCHAR (100)
--Initialize the variables
SET @Record_Count =''
SET @product=''
SET @Agent_URN=''
SET @Insurer=''
SET @Transaction_Counter=''
SET @Agent_Introducer_URN=''
SET @Company=''
---------------------------------------------Calculations---------------------------------------------
--1. Find Product
SELECT @product = [Cover type] FROM policytype WHERE [policy prefix] = LEFT (@policy_number, 2)
IF @product = '' SET @Return_Message = @policy_number + ' failed, Policy prefix not found, this was row number ' + @Row_Number
--2. Find Agent
IF @Return_Message <> ''
SELECT @Agent_URN = [Agent URN] FROM [AgentDetails] WHERE [Agent Number] = @Agent_Number
IF @Agent_URN = '' SET @Return_Message = @policy_number + ' failed, agent not found, this was row number ' + @Row_Number
--3. Find Insurer
IF @Return_Message <> ''
SELECT @Insurer = MAX ([Transaction date]) FROM Accounts WHERE [transaction date] = @Date_Last_Payment AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number
IF @Insurer = '' SET @Return_Message = @policy_number +' failed, insurer not found, this was row number ' + @Row_Number
--4. Find Transaction Counter
IF @Return_Message <> ''
SELECT @Transaction_Counter = [Transaction Counter] FROM Accounts WHERE [transaction date] = @Date_Last_Payment AND [policy number] = @policy_number AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')
IF @Transaction_Counter = '' SET @Return_Message = @policy_number + ' failed, Transaction Counter not found, this was row number ' + @Row_Number
--5. Find Agent Introducer
IF @Return_Message <> ''
SELECT @Agent_Introducer_URN = [agents introducer] FROM Accounts WHERE [transaction date] = @Date_Last_Payment AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number
IF @Agent_Introducer_URN = '' SET @Return_Message = @policy_number +' failed, agent introducer not found, this was row number ' + @Row_Number
--6. Company
IF @Return_Message <> ''
SELECT @Company = [company name] FROM Accounts WHERE [transaction date] = @Date_Last_Payment AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation') AND [policy number] = @policy_number
IF @Company = '' SET @Return_Message = @policy_number +' failed, company not found, this was row number ' + @Row_Number
----------------------------------------------------------------validation----------------------------------------------------------------------
--1. Agent Paymnt
IF @Agent_Payment_Amount >0 AND @product <> '' and @Agent_URN <> ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: agent payment needs to be a negative amount.'
--2. @Agent_Number
IF @Agent_Number = ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no agent number.'
--3. @Date_Last_Payment
IF @Agent_Number = ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no transaction date.'
--4. @policy_number
IF @Agent_Number = ''
SET @Return_Message = 'Failure this was row number: ' + @Row_Number + ' Reason: there is no policy number.'
--5. @Agent_Payment_Amount
IF @Agent_Number = ''
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no agent payment amount.'
--6. Search for combination of inputs
IF @Return_Message <> ''
SELECT @Record_Count = COUNT ([Policy number]) FROM accounts WHERE [Policy number] = @policy_number AND [Agent] = @Agent_Number AND [transaction date] = @Date_Last_Payment AND [transaction type] IN ('new business', 'mta', 'renewal', 'Cancellation')
IF @Record_Count = '0'
SET @Return_Message = @policy_number + ' failed, this was row number: ' + @Row_Number + ' Reason: there is no transaction that matches the policy number, agent and transaction date specified.'
March 6, 2013 at 4:40 am
In fact if I strip it down even further I still get issues
Server: Msg 170, Level 15, State 1, Procedure STP_Agent_upload, Line 38
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 38: Incorrect syntax near '@policy_number'.
CREATE PROCEDURE [dbo].[STP_Agent_upload]
(
-- List Input Parameters
@Agent_Number VARCHAR(50), --column A
@Date_Last_Payment DATETIME,--column C
@policy_number VARCHAR (50),--column D
@Agent_Payment_Amount SMALLMONEY,-- [Net] -> this is column M
@Row_Number VARCHAR (100),--PASS this from IMS!
@Return_Message VARCHAR (255) OUTPUT
)
AS BEGIN
--List Variables Here
DECLARE @Record_Count AS INT--Used to flag whether there is an existing record
DECLARE @productAS VARCHAR (100)
DECLARE @Agent_URNAS VARCHAR (100)
DECLARE @InsurerAS VARCHAR (100)
DECLARE @Transaction_CounterAS VARCHAR (100)
DECLARE @Agent_Introducer_URNAS VARCHAR (100)
DECLARE @CompanyAS VARCHAR (100)
--Initialize the variables
SET @Record_Count =''
SET @product=''
SET @Agent_URN=''
SET @Insurer=''
SET @Transaction_Counter=''
SET @Agent_Introducer_URN=''
SET @Company=''
---------------------------------------------Calculations---------------------------------------------
--1. Find Product
SELECT @product = [Cover type] FROM policytype WHERE [policy prefix] = LEFT (@policy_number, 2)
IF @product = '' SET @Return_Message = @policy_number
March 6, 2013 at 4:45 am
You have a BEGIN at the top of the sproc - does it have a corresponding END?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2013 at 4:47 am
ChrisM@Work (3/6/2013)
You have a BEGIN at the top of the sproc - does it have a corresponding END?
That's it, oh god that was so obvious now
Thanks so much Chris
March 6, 2013 at 4:49 am
Plateau (3/6/2013)
ChrisM@Work (3/6/2013)
You have a BEGIN at the top of the sproc - does it have a corresponding END?That's it, oh god that was so obvious now
Thanks so much Chris
You're welcome. Get some sleep 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply