Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Concatenate variables... What am I missing?! Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 3:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:25 AM
Points: 44, Visits: 68
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
Post #1427267
Posted Wednesday, March 6, 2013 3:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:25 AM
Points: 44, Visits: 68
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 @Product AS VARCHAR (100)
DECLARE @Agent_URN AS VARCHAR (100)
DECLARE @Insurer AS VARCHAR (100)
DECLARE @Transaction_Counter AS VARCHAR (100)
DECLARE @Agent_Introducer_URN AS VARCHAR (100)
DECLARE @Company AS 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.'
Post #1427268
Posted Wednesday, March 6, 2013 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,861, Visits: 14,160
You have single quoted a variable, making it a string-type constant: '@Date_Last_Payment'

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427275
Posted Wednesday, March 6, 2013 3:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:25 AM
Points: 44, Visits: 68
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
Post #1427278
Posted Wednesday, March 6, 2013 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,861, Visits: 14,160
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?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427283
Posted Wednesday, March 6, 2013 4:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:25 AM
Points: 44, Visits: 68
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 @Product AS VARCHAR (100)
DECLARE @Agent_URN AS VARCHAR (100)
DECLARE @Insurer AS VARCHAR (100)
DECLARE @Transaction_Counter AS VARCHAR (100)
DECLARE @Agent_Introducer_URN AS VARCHAR (100)
DECLARE @Company AS 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

Post #1427297
Posted Wednesday, March 6, 2013 4:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,861, Visits: 14,160
--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





“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427300
Posted Wednesday, March 6, 2013 4:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:25 AM
Points: 44, Visits: 68
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 @Product AS VARCHAR (100)
DECLARE @Agent_URN AS VARCHAR (100)
DECLARE @Insurer AS VARCHAR (100)
DECLARE @Transaction_Counter AS VARCHAR (100)
DECLARE @Agent_Introducer_URN AS VARCHAR (100)
DECLARE @Company AS 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.'
Post #1427303
Posted Wednesday, March 6, 2013 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:25 AM
Points: 44, Visits: 68
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 @Product AS VARCHAR (100)
DECLARE @Agent_URN AS VARCHAR (100)
DECLARE @Insurer AS VARCHAR (100)
DECLARE @Transaction_Counter AS VARCHAR (100)
DECLARE @Agent_Introducer_URN AS VARCHAR (100)
DECLARE @Company AS 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

Post #1427304
Posted Wednesday, March 6, 2013 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,861, Visits: 14,160
You have a BEGIN at the top of the sproc - does it have a corresponding END?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427309
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse