如何将架构作为参数传递给SQL Server中的存储过程?

问题描述

我有一个存储过程，可以根据两个表选择一个数据列表.第一个表是固定表:CO.Country.但是第二个表可以是多个表之一.表本身的名称是相同的:Location.但是，表的架构不同:ABD.Location, CGA.Location, GBN.Location.

I have a stored procedure to select a list of data based on two tables. The first table is a fixed one: CO.Country. But the second table can be one of a number of tables. The name of the table itself is the same: Location. But, the schema of the tables are different: ABD.Location, CGA.Location, GBN.Location.

用户将从应用程序中选择模式，然后将所选模式作为参数传递给存储过程.

The user will select the schema from the application, then the schema chosen will be passed to the stored procedure as a parameter.

但是在创建存储过程时解析存储过程会出错.

But there's an error when I parse the stored procedure while creating it.

反正有没有将架构名称作为参数传递?

Is there anyway to pass the schema name as a parameter?

推荐答案

使用 DynamicSql

尝试这样

CREATE PROCEDURE proc_name
   @schema VARCHAR(25)
AS

DECLARE @Query VARCHAR(1000)
SET @query='SELECT * FROM' +@schema +'.Location'
EXECUTE(@query)

这篇关于如何将架构作为参数传递给SQL Server中的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！