Insert data in dynamic column specified by user

I have table of language having column isactive, hindi, english, ....

I am creating a stored procedure for inserting new data like this:

create proc USP_Insertdata
   @lang varchar(20),
   @data nvarchar(50)
   EXEC(N'insert into language(@lang, isactive) values(@data, 'true')') 

Getting problem in exec


You'll need to bind lang dynamically as column names cannot be parameterized, however, @data can be parameterized, using sp_executesql:

create proc USP_Insertdata @lang varchar(20), @data nvarchar(50)
   declare @sql NVARCHAR(MAX) = N'insert into language(' + @lang + ',isactive) values(@data,''true'')';
   exec sp_executesql  @sql, N'@data nvarchar(50)', @data=@data

Sql Fiddle here. Note the comments below with regards to validating that the column name (@lang) is a valid column in the table, to prevent against nasties like Sql Injection. Given that there must be a finite number of column names, I would suggest validating @lang against a white list (i.e. checking @langagainst a permissable list of values)

Edit Resorting to dynamic Sql is often a sign of a smell. I'm not in agreement with the table design, since, after using the above proc to insert data, this will result in sparse data similar to:


English    French    Russian  IsAcive
Yes        NULL      NULL     true
NULL       Oui       NULL     true
NULL       NULL      Da       true

As an alternative, you could choose to unpivot the languages as columns, and instead normalize the data inserts into language, after first creating a simple reference table of available languages:


ID(PK) Language
EN     English
FR     French
RU     Russian

After looking up the appropriate language code, the original Language table would be normalized to:


LanguageId(FK) Data     IsActive
EN             Yes      1
FR             Oui      1
RU             Da       1

One last point - isactive appears to imply a boolean - it is more common to model this as a BIT or CHAR enumeration

Need Your Help

Issue with JSNlog Exception formatting

javascript c# json nlog jsnlog

I am having an issue with JSNlog and Nlog with formatting Exceptions coming from JSNlog. I am writing all my error logs out as JSON but when I throw an exception from javascript the system is enco...