CODEWITHSUNDEEP
sqlsql-server
joijo
joijo

Reputation: 21

how to use N with "insert into" command which have "?" as variable

it's normally that to use N like below.

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test'),(1001,N'test2')

but how to implement N in commend like

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (?,?),(?,?)

Upvotes: 1

Views: 681

Answers (1)

Igor
Igor

Reputation: 62213

SQL Server does not use ? for parameters, this is something that you usually find in Ole data sources (see OLE DB Source), they are usually also position specific (ordinal).

For SQL Server use names prefixed with @ as parameters. These are always named and the names should be unique. Each parameter must also be of a specific type. For parameters that contain Unicode text use the SQL Server type NVarChar (N'text' usually indicates NVarChar) and also specify the appropriate length. As the parameters are named defining the parameter and the position(s) the parameter occupies in a statement are not related in that the 2nd defined parameter could be used before the first defined parameter in a statement.

Your example refactored:

-- define your parameters
DECLARE @p1 INT, @p3 INT
DECLARE @p2 nvarchar(1000), @p4 nvarchar(1000)

-- set your parameter values (note: this could be done on the same line as the declaration)
SET @p1 = 1
SET @p2 = N'some text'
-- etc.

INSERT INTO [MyDB].[dbo].[MyTable] ([FieldID], [Description])
VALUES (@p1, @p2), (@p3, @p4)

See also

Upvotes: 3

Related Questions