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,
@Tabname NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(max);
if(@NOC=1)
BEGIN
DECLARE @COL1NAME NVARCHAR(MAX);
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
END
if(@NOC=3)
BEGIN
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.