Must declare the scalar variable @Stringkey

Question

I am trying to create a SQL script that fills a table with a new primary key.

what i currently have from internet searches is the following

DECLARE @IntVariable int;
DECLARE @Stringkey varchar(2);
DECLARE @SQLString nvarchar(500);
DECLARE @Intupdater nvarchar(500);

SET @IntVariable = 00000001;
SET @Stringkey = 'CD';

SET @SQLString = N'UPDATE Tri_Damen_Live.trimergo.calendar_details SET details_key = @Stringkey + @IntVariable';
SET @Intupdater = N'@IntVariable = @IntVariable+1'

EXECUTE sp_executesql @SQLString , N'@IntVariable INT' , N'@Stringkey VARCHAR(2)', @IntVariable, @Stringkey;

The errors I get with this are

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Stringkey".

I have a few questions about my problem and hope someone might be able to help me

First question : it seems my int increaser is wrong. is there a way to make a update variable statement for the int?

Second : I declared the int as 00000001. But I doubt it stays like this. The reason I do this is because the key needs to be a set 10 characters long in total (example 'CD00000001' etc) does this work like this or is there another way?

Third question : as the topic stated. The 'Must declare the scalar variable "@Stringkey".' error. I have removed the stringkey part but then it says the same error about the Int variable. I declared them at the top and set them as well. What am I missing?

Side information :

this function will be used for multiple databases with varying table sizes. So I need this kind of function to account that it does not have a set amount of update statements.

The primary key that is created here must be a string (Varchar(10)) that is ALWAYS 10 characters long. Seems like a silly requirement but it has been chosen to use this system.

I am using Microsoft SQL Server 2012 to run this in.

After SET @IntVariable = 00000001; the value of that variable (of type int) is just 1. Leading zeroes have no meaning for a number. — Hans Keﬆing
Yeah i was like 95% sure this was not going to work like I hoped. but figured i might as wel give it a shot to see. — Thomas van Schooten
If the primary key is always 10 characters long - make it a CHAR(10) and save yourself 2 bytes (per entry) of unnecessary overhead! That's quite important if your primary key is also the clustering key — marc_s

podiluska podiluska · Accepted Answer · 2013-09-16T08:29:25

1 You can't add an int to a string like that. You need to convert the int to a string

   @Stringkey + convert(varchar(10),@IntVariable)

2 An int is a number. If you want it to be formatted, you need to do that yourself.

   SET details_key = @Stringkey + right(''0000000000''+convert(varchar(8),@IntVariable),8)

3 Your variable declaration in sp_executesql should be

N'@IntVariable INT, @Stringkey VARCHAR(2)'

ie

EXECUTE sp_executesql @SQLString , N'@IntVariable INT, @Stringkey VARCHAR(2)', @IntVariable, @Stringkey;

Alternatively, you might consider using a SEQUENCE - see http://raresql.com/2013/06/21/sql-server-2012-how-to-generate-a-varchar-sequence-number-using-sequence-object/

Must declare the scalar variable @Stringkey

3 Answers