IF EXISTS (SELECT 1 FROM syscolumns WHERE id=object_id('[DiscountRequirement]') and NAME='RestrictedProductVariantIds')
BEGIN
DECLARE @entity_id int
DECLARE cur_existing_entity CURSOR FOR
SELECT [Id]
FROM [DiscountRequirement]
WHERE [DiscountRequirementRuleSystemName] = N'DiscountRequirement.HasAllProducts'
or [DiscountRequirementRuleSystemName] = N'DiscountRequirement.HasOneProduct'
or [DiscountRequirementRuleSystemName] = N'DiscountRequirement.PurchasedAllProducts'
or [DiscountRequirementRuleSystemName] = N'DiscountRequirement.PurchasedOneProduct'
OPEN cur_existing_entity
FETCH NEXT FROM cur_existing_entity INTO @entity_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @settingname nvarchar(1000)
SET @settingname = N'DiscountRequirement.RestrictedProductVariantIds-' + CAST(@entity_id AS nvarchar(max))
DECLARE @RestrictedProductVariantIds nvarchar(MAX)
SET @RestrictedProductVariantIds = 0
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT @RestrictedProductVariantIds = [RestrictedProductVariantIds] FROM [DiscountRequirement] WHERE [Id] = ' + ISNULL(CAST(@entity_id AS nvarchar(max)), '0')
EXEC sp_executesql @sql,N'@RestrictedProductVariantIds int OUTPUT',@RestrictedProductVariantIds OUTPUT
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = @settingname)
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (@settingname, @RestrictedProductVariantIds)
END
--fetch next identifier
FETCH NEXT FROM cur_existing_entity INTO @entity_id
END
CLOSE cur_existing_entity
DEALLOCATE cur_existing_entity
--drop RestrictedProductVariantIds column
EXEC('ALTER TABLE [DiscountRequirement] DROP COLUMN [RestrictedProductVariantIds]')
END
Msg 245, Level 16, State 1, Line number 1
Conversion failed when converting the nvarchar value '87,88' to data type int.
What am I doing wrong? I have changed nothing in the original DB...