CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlstored-procedures
Munklefish
Munklefish

Reputation: 393

TSQL / SP INSERT statement with text & values

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!

Upvotes: 0

Views: 449

Answers (4)

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21660

I think you must use ' instead of ""

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

SET QUOTED_IDENTIFIER OFF

SET QUOTED_IDENTIFIER

Upvotes: 3

Yoav
Yoav

Reputation: 170

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)

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166416

Use single quotes ' not "

Upvotes: 2

user114600
user114600

Reputation:

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.

Upvotes: 1

Related Questions