[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SP String concatenation, incorrect syntax error message

Posted on 2006-05-03
4
Medium Priority
?
446 Views
Last Modified: 2011-09-20
Declare @case_id varchar(25)
Declare @plcy_ref_no_num int
Declare @fndc_id_within_plcy int
Declare @offr_id_within_plcy int

SELECT @plcy_ref_no_num = PLCY_REF_NO_NUM FROM PLCY_POLICY WHERE PLCY_ID = @plcy_id
SELECT @fndc_id_within_plcy = FNDC_ID_WITHIN_PLCY FROM FNDC_FUNDING_COMMITMENT WHERE FNDC_ID=@fndc_id
SELECT @offr_id_within_plcy = OFFR_ID_WITHIN_PLCY FROM OFFR_OFFER WHERE OFFR_ID = @offr_id

-- The above three return 7188, 2, and 2 with no problems.

-- I am trying to get @case_id to be '7188-P/ 2/ 2' to meet goofy business requirements, and it is not concatenating correctly.
SELECT @case_id = CAST(@plcy_ref_no_num + '-P/' + SPACE(1) + @fndc_id_within_plcy '/' + SPACE(1) + @offr_id_within_plcy as varchar(25))

Resulting error message in SQL Query Analyzer:  Line 24: Incorrect syntax near '/'.

Thanks in advance.
-Jim
0
Comment
Question by:Jim Horn
  • 2
4 Comments
 
LVL 4

Assisted Solution

by:g_johnson
g_johnson earned 400 total points
ID: 16597176
am trying to get @case_id to be '7188-P/ 2/ 2' to meet goofy business requirements, and it is not concatenating correctly.
SELECT @case_id = CAST(@plcy_ref_no_num + '-P/' + SPACE(1) + @fndc_id_within_plcy '/' + SPACE(1) + @offr_id_within_plcy as varchar(25))

you are missing a + sign between plcy                  +    '/'
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1600 total points
ID: 16597189
You need to convert the int  to varchar


SELECT @case_id = CAST(@plcy_ref_no_num as varchar(8)) + '-P/' + SPACE(1) + CAST(@fndc_id_within_plcy as varchar(8))+ '/' + SPACE(1) + CAST(@offr_id_within_plcy as varchar(8))


0
 
LVL 66

Author Comment

by:Jim Horn
ID: 16597200
Thanks for the response.  I corrected it, and now have the following:

SELECT @case_id = CAST(@plcy_ref_no_num + '-P/' + SPACE(1) + @fndc_id_within_plcy + '/' + SPACE(1) + @offr_id_within_plcy as varchar(25))

Syntax error converting the varchar value '-P/' to a column of data type int.
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 16597219
That worked, returning '7188-P /2 /2'

SELECT @case_id = CAST(@plcy_ref_no_num as varchar(8)) + '-P/' + SPACE(1) + CAST(@fndc_id_within_plcy as varchar(8)) + '/' + SPACE(1) + CAST(@offr_id_within_plcy as varchar(8))

Thanks guys.
-Jim
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question