Cursor Replacement : By creating dynamic query.

We have a situation where we have to store product details in Product table and Product features in ProductFeatures Table using single screen.

So We'll insert Product details in Product table and get its product to insert product features in
ProductFeatures table.

We will use two Procedures and one Function to achieve this.

We will use dynamic query :

DECLARE @ExecQuery NVARCHAR(MAX);
  SELECT @ExecQuery  = COALESCE(@ExecQuery +';' ,'') + 'EXEC [dbo].[Usp_AddProductFeatures] '+CAST(@PId AS VARCHAR)+', '''+ CAST(GETDATE() AS VARCHAR) +''', ''' + Element+''', ''+|+'''
  FROM dbo.func_split(@ProductFeatures, '*|*');

  EXECUTE sp_executesql @ExecQuery;

Example : Passing some values to @ProductFeatures and Lets see output using print.

  DECLARE @ExecQuery NVARCHAR(MAX);
  SELECT @ExecQuery  = COALESCE(@ExecQuery +';' ,'') + 'EXEC [dbo].[Usp_AddProductFeatures] '+CAST(1 AS VARCHAR)+', '''+ CAST(GETDATE() AS VARCHAR) +''', ''' + Element+''', ''+|+'''
  FROM dbo.func_split('Feature 01*|*Feature 02*|*Feature 03', '*|*');

  print @ExecQuery

Output:

  EXEC [dbo].[Usp_AddProductFeatures] 1, 'Sep  9 2017  3:48PM', 'Feature 01', '+|+';
  EXEC [dbo].[Usp_AddProductFeatures] 1, 'Sep  9 2017  3:48PM', 'Feature 02', '+|+';
  EXEC [dbo].[Usp_AddProductFeatures] 1, 'Sep  9 2017  3:48PM', 'Feature 03', '+|+'

Result : If we execute @ExecQuery, all queries will be executed one by one.

Practical Example 1 : With Only one value to be inserted into ProductFeatures table.

Step 1 : Create function

CREATE FUNCTION [dbo].[func_Split]
    (  
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100)
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

Step 2 : Create first procedure to insert product details in product table and pass values to insert product features to productfeatures table using dynamic query.


CREATE PROC [dbo].[Usp_InsertProductAndFeatures]
@ProductName NVARCHAR(MAX),
@CategoryId INT,
@ProductFeatures VARCHAR(MAX)
AS
BEGIN
   DECLARE @PId INT
   DECLARE @Feature VARCHAR(MAX)

   INSERT Products
   (
   ProductName,
   CategoryID,
   CreatedOn
    )
   VALUES
   (
   @ProductName,
   @CategoryId,
   GETDATE()
    );

  SET @PId= @@IDENTITY;

  DECLARE @ExecQuery NVARCHAR(MAX);
  SELECT @ExecQuery  = COALESCE(@ExecQuery +';' ,'') + 'EXEC [dbo].[Usp_AddProductFeatures] '+CAST(@PId AS VARCHAR)+', '''+ CAST(GETDATE() AS VARCHAR) +''', ''' + Element+''', ''+|+'''
  FROM dbo.func_split(@ProductFeatures, '*|*');

  EXECUTE sp_executesql @ExecQuery;

END


Step 3 : Create second procedure to insert product features.

CREATE PROCEDURE [dbo].[Usp_AddProductFeatures]
     (
   @PId INT,
   @CreatedDate DATETIME,
   @element    VARCHAR(MAX),
   @delimiter VARCHAR(MAX)
     )
AS
BEGIN
  DECLARE @result BIT = 0;

  ;WITH prodFeature AS(
   SELECT * FROM dbo.func_split(@element, '+|+')
  )

     INSERT INTO ProductFeatures
  (
   ProductId,
   CreatedOn,
   ProductFeature
  )
    VALUES
    (
     @PId,
     @createdDate,
     (select Element from prodFeature where elementId = 1)
    )
     RETURN @result

END

==================================================================
Practical Example 2 : With multiple value to be inserted into ProductFeatures table.

For multiple values we'll pass all values to @ProductFeature separating them with delimiter +|+ like : If we have to pass product feature, its type and it made in details then we can pass in single string separated by +|+ as shown below :

ProductFeature+|+Type+|+Made In Details

Example : 

'Feature 01+|+New+|+Made In India*|*Feature 02+|+Old+|+Made In China*|*Feature 03+|+New+|+Made In India'

Note the output of the following query :

Select * from  dbo.func_split('Feature 01+|+New+|+Made In India*|*Feature 02+|+Old+|+Made In China*|*Feature 03+|+New+|+Made In India','*|*')




Note the output of the following query :

DECLARE @ExecQuery NVARCHAR(MAX);
SELECT @ExecQuery  = COALESCE(@ExecQuery +';' ,'') + 'EXEC [dbo].[Usp_AddProductFeatures] '+CAST(1 AS VARCHAR)+', '''+ CAST(GETDATE() AS VARCHAR) +''', ''' + Element+''', ''+|+'''
  FROM dbo.func_split('Feature 01+|+New+|+Made In India*|*Feature 02+|+Old+|+Made In China*|*Feature 03+|+New+|+Made In India', '*|*');

  print @ExecQuery

Output : 

EXEC [dbo].[Usp_AddProductFeatures] 1, 'Sep  9 2017  9:48PM', 'Feature 01+|+New+|+Made In India', '+|+';
EXEC [dbo].[Usp_AddProductFeatures] 1, 'Sep  9 2017  9:48PM', 'Feature 02+|+Old+|+Made In China', '+|+';

EXEC [dbo].[Usp_AddProductFeatures] 1, 'Sep  9 2017  9:48PM', 'Feature 03+|+New+|+Made In India', '+|+'

So we'll use this way as :

DECLARE @ExecQuery NVARCHAR(MAX);
SELECT @ExecQuery  = COALESCE(@ExecQuery +';' ,'') + 'EXEC [dbo].[Usp_AddProductFeatures] '+CAST(@PId AS VARCHAR)+', '''+ CAST(GETDATE() AS VARCHAR) +''', ''' + Element+''', ''+|+'''
  FROM dbo.func_split(@ProductFeatures, '*|*');

  EXECUTE sp_executesql @ExecQuery;

Example Steps : 

Step 1 : We have already created function [dbo].[func_Split].

Step 2 : We have already created first procedure [dbo].[Usp_InsertProductAndFeatures] to insert product details in product table and pass values to insert product features to productfeatures table using dynamic query.

Step 3 :  Create second procedure to insert product features with slight change as :

CREATE PROCEDURE [dbo].[Usp_AddProductFeatures]
     (
   @PId INT,
   @CreatedDate DATETIME,
   @element    VARCHAR(MAX),
   @delimiter VARCHAR(MAX)
     )
AS
BEGIN
  DECLARE @result BIT = 0;

  ;WITH prodFeature AS(
   SELECT * FROM dbo.func_split(@element, '+|+')
  )

     INSERT INTO ProductFeatures
  (
   ProductId,
   CreatedOn,
   ProductFeature,
   Type,
   MadeInDetail
  )
    VALUES
    (
     @PId,
     @createdDate,
     (select Element from prodFeature where elementId = 1),
     (select Element from prodFeature where elementId = 2),
     (select Element from prodFeature where elementId = 3)
    )
     RETURN @result

END

Note the output of CTE prodFeature we have used in our last step.

;WITH prodFeature AS(
   SELECT * FROM dbo.func_split('Feature 01+|+New+|+Made In India', '+|+')
  )

  Select * from prodFeature