How Do I Pass Values To Block Level Variables In My Stored Procedure?

See more:

SQL

SQL-Server

SQL-Server-2008

I did some research on this but didn't get the exact solution that i am looking for.
Basically what i am trying to do is, insert a dynamic value to a dynamic column of the dynamic table. I came across 2 ways for doing that.

case 1:
Static Declaration of the number of variables needed for the insertion, So here "No. of Columns defined for the insertion" will be pre-defined.
This works well , no issues.

case2:
But again i even want to define this "No. of Columns for the insertion" DYNAMICALLY.
This causes the problem, since i cannot pass the block level variables from runtime.

I do not want to go with case 1, i prefer case 2 since i can write only one stored procedure instead of 'N' numbers.

Below is the code for both cases:

CASE 1:

CREATE PROCEDURE [dbo].[DYN_SP_INSERT]
  
  @Tabname NVARCHAR(511),
  @COL1NAME NVARCHAR(MAX),
  @COL1VALUE NVARCHAR(MAX)
  
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);
  DECLARE @params NVARCHAR(max);

BEGIN
  SET @sql = 'INSERT INTO ' + @Tabname + ' 
  ('+QUOTENAME(@COL1NAME)+') 
  VALUES 
  (@C1V)';
  set @params ='@C1V NVARCHAR(MAX)'

  EXEC sp_executesql @sql,@params,@C1V=@COL1VALUE;
  
  END
END

CASE 2:

CREATE PROCEDURE [dbo].[DYN_SP_INSERT]
  
  @NOC int, --[FYI-no. of columns to be inserted based on runtime value-]
  @Tabname NVARCHAR(511)
  
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);
  DECLARE @params NVARCHAR(max);

if(@NOC=1)--this is for to define the "No. of Columns for Insertion @runtime"
BEGIN
  DECLARE @COL1NAME NVARCHAR(MAX);--block level variables
  DECLARE @COL1VALUE NVARCHAR(MAX);

  SET @sql = 'INSERT INTO ' + @Tabname + ' 
  ('+QUOTENAME(@COL1NAME)+') 
  VALUES 
  (@C1V)';
  set @params ='@C1V NVARCHAR(MAX)'

  EXEC sp_executesql @sql,@params,@C1V=@COL1VALUE;
  
  END
if(@NOC=2)
BEGIN
--HERE WILL BE GOING TO INSERT 2 VALUES FOR 2 DYNAMIC COLUMNS..
END
if(@NOC=3)
BEGIN
--HERE WILL BE GOING TO INSERT 3 VALUES FOR 3 DYNAMIC COLUMNS..SO ON..
END
END

EXECUTION OF BOTH CASE 1 & 2:
CASE 1:

EXEC DYN_SP_INSERT 'DYN_TEST_TABLE','PROJCODE','ASTR998'

->works fine no issues.

CASE 2:

EXEC DYN_SP_INSERT '1','DYN_TEST_TABLE','PROJCODE','ASTR998'

-> THIS throws an error.
"Procedure or function DYN_SP_INSERT has too many arguments specified."
Since only 2 variables declared in the main.it is not conisdering block level variables.

So how do i pass values to block level variables.[@COL1NAME ,@COL1VALUE ]..?
And
Is there any performance issues with this kind of stored procedure.?

Thanks in advance.