具有动态查询的存储过程 [英] Stored Procedure with Dynamic Query

问题描述

我正在使用动态SQL编写存储过程。

I am writing a stored procedure using dynamic SQL.

在我的过程中，我说了大约10个相似列的表。

In my procedure, I have say some 10 tables of similar columns.

例如，如果我考虑名称& 部门表，名称表包含以下列：

For example if I consider Designation & Department tables, Designation table has these columns:

Designation, Code, EntryBy, EntryOn, ModifiedBy, ModifiedOn 

部门表包含以下列：

Department, Code, EntryBy, EntryOn, ModifiedBy, ModifiedOn

，类似地，我还有其他八张表。

and similarly I have some eight other tables.

在存储过程中，我需要更新数据并将其插入所有表中。因此，与其编写update&对于每个表插入语句，我正在使用一个存储过程，该存储过程接受表名作为参数并检查该表中是否已存在该行。

In my stored procedure, I need to update and insert data into all the tables. So, instead of writing update & insert statements for each table, I am using a stored procedure which accepts the table name as a parameter and checks if the row already exists in that table.

如果存在该行，则该记录将被更新，否则该记录将被插入相应的表中。

If that row is present, then that record will be updated otherwise that record will be inserted into the respective table.

    ALTER PROC UpdateMasterItems
    (
        @MasterTypeTmp  varchar(50),
        @NameTmp    varchar(50),
        @CodeTmp    varchar(10))
    AS
    BEGIN   
       DECLARE @CntTmp numeric(2,0)

       EXEC('select count(*)' + @CntTmp + ' from ' + @MasterTypeTmp + ' where ' + @MasterTypeTmp  + ' = ' + @NameTmp)

       IF(@CntTmp > 1)
       BEGIN
          EXEC('UPDATE ' + @MasterTypeTmp + ' SET ' + 'Code = ' + @CodeTmp + ', ModifiedBy = CURRENT_USER, MOdifiedOn = CURRENT_TIMESTAMP WHERE' + @MasterTypeTmp + ' = ' +  @NameTmp)
          RETURN 10
       END      
       ELSE
       BEGIN    
          EXEC('INSERT INTO ' + @MasterTypeTmp + '(' + @MasterTypeTmp + ', Code, EntryBy, EntryOn, ModifiedBy, ModifiedOn )  VALUES (' + @NameTmp + ',' + @CodeTmp + ',' + 'CURRENT_USER, CURRENT_TIMESTAMP, CURRENT_USER, CURRENT_TIMESTAMP )') 
          RETURN 11
       END
END

其中 @MasterTypeTmp 是表名（可以是部门 / 名称 .....）

where @MasterTypeTmp is the table name(can be Department/Designation.....)

执行该程序时出现错误：

I am getting an error while executing the procedure:

Exec语句：

EXEC UpdateMasterItems 'Designation', 'TestName', 'TestCode'

错误语句：

无效的列名'TestName'

无效的列名 TestCode。

但是 TestName & TestCode 不是列名。这些是列的值。请让我知道我的动态查询是否错误或问题出在哪里！

But TestName & TestCode are not column names. Those are the values for the columns. Please let me know if my dynamic queries are wrong or where the problem is!

在此先感谢

Mounika

推荐答案

我认为错误消息是因为您用'（即名称 是错误的）。应该是指定

I think the error message is because you have wrapped column names with ' (ie; 'Designation' is wrong). Should be Designation

但是还有其他问题。

1. 我认为您不能在动态sql外部定义变量并将其分配到内部。因为动态sql在另一个会话中运行，所以在范围外定义的变量是未知的。 （即 @CntTmp ）

1. I think you cannot define variables out side dynamic sql and assign them inside. Because dynamic sql runs in a different session and therefore variables defined outside the scope are unknown. (ie; @CntTmp)

即使您正在检查 @CntTmp > 1 您并没有真正为其分配count（*）值（由于原因1仍然无法使用）

Even thought you are checking @CntTmp > 1 you are not really assigning count(*) value to it (won't work anyway due to reason 1)

您的如果记录计数> 99， @CntTmp 将会溢出（如果您没有错误的数据，这不是真正的问题）

Your @CntTmp will overflow if the record count > 99 (not really an issue if you don't have bad data)

如果需要按照描述的方式进行这项工作，则必须声明变量，检查记录的存在，然后更新/插入 全部都在同一个动态查询中 。您可以使用不存在变量（如果存在）（选择...。）更新...否则插入...

If you need to get this work the way you have described, you have to declare variables, check the existance of records and then update/insert all within the same dynamic query. You could do without a variable using if exists (select ....) update ... else insert...

这篇关于具有动态查询的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！