tim dai tim dai - 1 month ago 5
SQL Question

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

it's normally that to use '?' like below (Java).

CallableStatement cstmt = con.prepareCall("{call employee_jobid(?)}");
String lname = "Brown";
cstmt.setString(1, lname);
Resultset rs = cstmt.executeQuery();
rs.close();
cstmt.close();


how should I mark 'N' on 'lname' ?

Answer

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

Comments