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)
as
begin
   EXEC(N'insert into language(@lang, isactive) values(@data, 'true')') 
end

Getting problem in exec

Answers


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)
as 
begin
   declare @sql NVARCHAR(MAX) = N'insert into language(' + @lang + ',isactive) values(@data,''true'')';
   exec sp_executesql  @sql, N'@data nvarchar(50)', @data=@data
end

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:

Language

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:

LanguageType

ID(PK) Language
EN     English
FR     French
RU     Russian

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

LanguageData

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...