how to pass column name with parameter in insert s

问题:

how to pass column name with parameter in insert sql statment e.g.

@name='name'
insert into employees (id,@name) values(1,'a')

is this possible?

回答1:

No it isn't possible you would need to build the SQL Statement including the column either in your application or by using dynamic SQL in SQL Server itself.

Edit: RE: "what's dynamic sql". The approach is as follows.

DECLARE @Value nvarchar(100)
DECLARE @InsertString nvarchar(1000)
DECLARE @name sysname

SET @name ='name'
SET @Value = 'a'

SET @InsertString= 'INSERT INTO EMPLOYEES (id,' + @name + ') values(1, @Value)'


EXEC sp_executesql @InsertString, N'@Value nvarchar(100)', @Value 

However there are 2 issues with it. First @name must be SQL Injection proof. For the same reason you would also want to use a parameter for @Value. However the data type for that must be specified in advance meaning that it won't be suitable for all columns. So on reflection you would be better off doing this in your application layer. (NB: The same considerations about SQL injection still apply and your application code will need to add the parameter of the correct type for the column)