SP String concatenation, incorrect syntax error message

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
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
 
g_johnsonConnect With a Mentor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.