Как редактировать поля прямо в таблице на сайте

В этом примере было реализовано редактирование таких типов полей, как Строка, Многострочный текст, Список, Дата и Переключатель.

Для этого мы:

• создали таблицу
• добавили в неё необходимые колонки
• настроили тип редактируемых полей
• для поля с типов редактирования "Список" настроили процедуру SQL 
• настроили процедуру GetItem
• настроили процедуру UpdateField.

CREATE PROCEDURE [dbo].[crud_watch_editing_category_dict]
	@tableCode nvarchar(32),
	@col nvarchar(32),
	@username nvarchar(32)
as
begin
	select id Value, name Text
	from tst_categories
end

CREATE PROCEDURE [dbo].[crud_watch_editing_getItems]
	@filters CRUDFilterParameter READONLY,
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare   @ids TABLE (id int)

	-- filters...
	declare @filterName nvarchar(128)
	select @filterName = Value from @filters where [Key] = 'name'

	insert into @ids
	select id
	from [tst_products]
	where (isnull(@filterName, '')='' or name like '%'+@filterName+'%')

	-- SELECT 1
	select
		id id,
		isnull(name, '') name,
        isnull(price, 0) price,
		isnull([desc], '') [desc],
        isnull(try_convert(datetime, created, 104), '')created,
        isnull(isVisible, 0) isVisible,
        (select name from tst_categories where id = (select categoryID from tst_categoryProducts where productID = p.id)) category
	from tst_products p
	where id in (select id from @ids)
	order by
		case when @sort = ''  then id end asc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'price' and @direction = 'up' then price end asc,
        case when @sort = 'price' and @direction = 'down' then price end desc,
        case when @sort = 'desc' and @direction = 'up' then [desc] end asc,
        case when @sort = 'desc' and @direction = 'down' then [desc] end desc,
        case when @sort = 'created' and @direction = 'up' then created end asc,
        case when @sort = 'created' and @direction = 'down' then created end desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

	-- SELECT 2
	select count(*) from @ids

	-- SELECT 3
	Select  '  ' Title, 1 HideTitleCount
END

CREATE PROCEDURE [dbo].[crud_watch_editing_updateField]
	@itemID int,
	@field nvarchar(64),
	@value nvarchar(max),
	@username nvarchar(64)
AS
BEGIN

if(@field = 'name') begin
	update tst_products set name = @value where id = @itemID
end else if(@field = 'price') begin
	update tst_products set price = try_cast(@value as decimal(18,2)) where id = @itemID
end else if(@field = 'category') begin
	update tst_categoryProducts set categoryID = try_cast(@value as int) where productID = @itemID
end else  if(@field = 'desc') begin
	update tst_products set [desc] = @value where id = @itemID
end else if(@field = 'created') begin
	update tst_products set created = try_convert(datetime, @value, 104) where id = @itemID
end else if(@field = 'isVisible') begin
	update tst_products set isVisible = try_cast(@value as bit) where id = @itemID
end else begin
	select 'Невалидный код свойства' Msg, 0 Result
	return
end
select '' Msg, 1 Result
END