SQL 2000表名作为存储过程中的变量 [英] SQL 2000 Table Name as variable in stored procedure

问题描述

表名称:
RM_master

Table Name :
RM_master

字段:
cust_no
acct_no
acct_code

Fields :
cust_no
acct_no
acct_code

问题是，我想使表RM_master作为存储过程中的参数中的变量吗?

Question is, I want to make the table RM_master as a variable in the parameters in the stored procedure?

这没有语法错误，但是当我在查询分析器中通过右键单击存储过程名称来执行此操作时，未标识变量表名称(RM_master)
这是我的存储过程；

This has no syntax error but when I execute this in the query analyzer by right clicking on the stored procedure name the variable table name (RM_master) is not identified
Here is my stored procedure ;

CREATE PROCEDURE RMQUERY

  @cusnumber  nvarchar (255) = '' ,
  @acctnumber nvarchar (255) = '' ,
  @master nvarchar (255) = ''

AS

BEGIN

SET @CUSNUMBER = @CUSNUMBER
DECLARE @RMRM AS NVARCHAR (255)
SET @RMRM =n'SELECT * FROM' + @MASTER + 'WHERE ACCT_NO =' + @ACCTNUMBER

EXEC RMQUERY2 
END

推荐答案

不建议这样做，因为您只是在存储的proc中创建动态sql.这将打开sql注入后门，因为您没有关于输入创建的sql的概览:参数值绝不能用作查询元素本身，而只能用作查询中的值(可以动态创建，尽管始终应使用参数) ).

It's not recommended, as you simply are creating dynamic sql inside a stored proc. This opens up sql injection backdoors as you have no overview about what sql is created by the input: parameter values should never be used as query elements themselves, but only as values in a query (which can be dynamically created, though always should use parameters).

尽管必须这样做，但您应该使用外部存储的proc sp_executesql并将sql馈送到该proc.

Though if you must, you should use the external stored proc sp_executesql and feed the sql to that proc.

这篇关于SQL 2000表名作为存储过程中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！