如何在我的存储过程中传递值以阻止级别变量？ [英] How Do I Pass Values To Block Level Variables In My Stored Procedure?

问题描述

我对此做了一些研究，但没有得到我正在寻找的确切解决方案。

基本上我想要做的是，将动态值插入到动态列中动态表。我找到了两种方法。



案例1：

静态声明插入所需的变量数量，所以这里为插入定义的列数将是预先定义的。

这很好，没有问题。



case2：

但是我甚至想要动态地定义这个插入列数。

这会导致问题，因为我无法从运行时传递块级变量。



我不想使用案例1，我更喜欢案例2，因为我只能写一个存储过程而不是'N'数字。



以下是这两种情况的代码：



案例1：

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

案例2：

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

两种情况的执行情况1& 2：

案例1：

EXECUTION OF BOTH CASE 1 & 2:
CASE 1:

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

- >工作正常没问题。



案例2：

->works fine no issues.

CASE 2:

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

- >这会引发错误。

过程或函数DYN_SP_INSERT指定的参数太多。

因为在main.it中只声明了2个变量不是块级变量。



那么如何将值传递给块级变量。[@ COL1NAME，@ COL1VALUE] ..？

和

这种存储过程是否存在性能问题。？



提前致谢。

-> 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.

推荐答案

您好，



请参考这里



http://social.msdn.microsoft.com/Forums/en-US/2175febd-a6b1-4acd-a431-a71beeea1329/variable-number-of-arguments-in-a-stored-procedure?forum=transactsql [ ^ ]



我相信这就是你要找的东西。



如果有帮助，请告诉我。

Hi,

Please refer here

http://social.msdn.microsoft.com/Forums/en-US/2175febd-a6b1-4acd-a431-a71beeea1329/variable-number-of-arguments-in-a-stored-procedure?forum=transactsql[^]

I believe this is what you are looking for.

Please let me know if that helps you.

是的，它是！



查看SP的声明：

Yes, it is!

Have a look at SP's declaration:

CREATE PROCEDURE [dbo].[DYN_SP_INSERT]
  
  @NOC int, --[FYI-no. of columns to be inserted based on runtime value-]
  @Tabname NVARCHAR(255), -- destination table
  @Cols NVARCHAR(MAX), -- the names of coulmns
  @Vals NVARCHAR(MAX)  -- values to be inserted
AS
BEGIN
  SET NOCOUNT ON;

  -- here comes the body of SP

END

如何拨打上述SP？

How to call above SP?

EXEC DYN_SP_INSERT 2, 'DYN_TEST_TABLE', 'Col1,Col2,Col3,Col4', 'PROJCODE,ASTR998,RUMBA,CHACHA'

基本思路是使用常用表格表达式 [ ^ ]从第三和第四个输入参数中拆分列名和值。有关详细信息，请参阅：这组答案 [ ^ ]



试试吧！遇到困难时，请回到这里询问详细问题。

The basic idea is to use Common Table Expression[^] to split column names and values from third and fourth input parameters. For further information, please see: this set of answers[^]

Try! When you get stuck, please come back here and ask detailed question.

试试这个

Try this

EXEC DYN_SP_INSERT @NOC=1,@col1=DYN_TEST_TABLE,@col2=PROJCODE,@col3=ASTR998

这篇关于如何在我的存储过程中传递值以阻止级别变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！