SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenate variables... What am I missing?!


Concatenate variables... What am I missing?!

Author
Message
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
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
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
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.'
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16852 Visits: 19557
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
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16852 Visits: 19557
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
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16852 Visits: 19557

--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
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
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.'
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16852 Visits: 19557
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search