Concatenate variables... What am I missing?!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Concatenate variables... What am I missing?!

12 posts, Page 1 of 2

1 2 »»

Concatenate variables... What am I missing?!

Rate Topic

Display Mode

Topic Options

Author

Message

Plateau

Posted Wednesday, March 06, 2013 3:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 9:51 AM
Points: 33, Visits: 39

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

Plateau

Posted Wednesday, March 06, 2013 3:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 9:51 AM
Points: 33, Visits: 39

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

ChrisM@Work

Posted Wednesday, March 06, 2013 3:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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

Plateau

Posted Wednesday, March 06, 2013 3:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 9:51 AM
Points: 33, Visits: 39

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

ChrisM@Work

Posted Wednesday, March 06, 2013 4:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

Plateau (3/6/2013)

ChrisM@Work (3/6/2013)

You have single quoted a variable, making it a string-type constant: '@Date_Last_Payment'

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}

Post #1427283

Plateau

Posted Wednesday, March 06, 2013 4:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 9:51 AM
Points: 33, Visits: 39

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'

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

ChrisM@Work

Posted Wednesday, March 06, 2013 4:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

--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}

Post #1427300

Plateau

Posted Wednesday, March 06, 2013 4:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 9:51 AM
Points: 33, Visits: 39

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

Plateau

Posted Wednesday, March 06, 2013 4:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 9:51 AM
Points: 33, Visits: 39

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

ChrisM@Work

Posted Wednesday, March 06, 2013 4:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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}

Post #1427309

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions