TSQL / SP INSERT statement with text & values

Question

HI,

I have an insert statement in a Stored Procedure that requires the value data to be a combination of text and variables/inputs. However my SQL Manager is giving an error on this 'camp' value when trying to compile it.

Here is the statement as it currently exists;

   BEGIN TRAN
   INSERT INTO TBL_DONATIONS ([donations_MemberID], [donation_Ref], [donations_Debit]) VALUES (@tmp_Id, "camp"+@tmp_Id+"/Key"+@tmp_IDENTITY, @tmp_Pledge)
   COMMIT

I imagine its probably a case of me using the wrong format for concatenating the parts forming '"camp"+@tmp_Id+"/Key"+@tmp_IDENTITY' ???????

Any help greatly welcomed.

Thanks!

Answer 1

You need to prepare the values before you stick them into the VALUES clause, or change it to an INSERT INTO ... SELECT ... query. The latter will be easier.

EDIT: And use single-quotes, not double-quotes.

Answer 2

I think you must use ' instead of ""

EDIT: If you want to "" for values you have to

SET QUOTED_IDENTIFIER OFF

SET QUOTED_IDENTIFIER

Answer 3

Use single quotes ' not "

Answer 4

What's the exact error you're getting?

Without further information, you may need to cast the following variables, because the naming convention you use would lead me to believe they are integers, and you are concatenating them with strings:

• @tmp_Id
• @tmp_IDENTITY

Try this: 'camp' + CAST(@tmp_Id as varchar) + '/Key' + CAST(@tmp_IDENTITY as varchar)