SQL Server存储过程将参数传递给变量

I have a big query which works and I want to write a stored procedure for it. I'm getting this error:

我有一个大的查询工作，我想为它编写一个存储过程。我收到这个错误：

the OLE DB provider SQLNCLI11" for linked server "theServer" does not contain the table ""@dbName"."dbo"."tableName"

OLE DB提供程序SQLNCLI11“用于链接服务器”theServer“不包含表”“@ dbName”。“dbo”。“tableName”

What I am trying to do:

我想做什么：

create PROCEDURE [sys.sp_myProcedure] 
(
 @dbName varchar(30) output, 
 @rid varchar (10) output,
 @mdate output
 )
AS
BEGIN
declare @prt varchar(12)
declare @pid int
declare @cid int
--declare @rid int
declare @aid int

    SET NOCOUNT ON;
set @cid= (select CID from  theServer.[@dbName].dbo.tableName where RID= @rid)
set @pid= (select PID from  theServer.[@dbName].dbo.tableName where RID= @rid)
set @aid= (select aid from  theServer.[@dbName].dbo.tableName where RID= @rid)
--then my query begins

theServer.[@dbName].dbo.tablename is a linked server.

theServer。[@ dbName] .dbo.tablename是一个链接服务器。

What I want to do is:

我想做的是：

execute [sys.sp_myProcedure] 'someDbname', '123', '2012-03-03'

and the parameters passed here would set/update the variables @dbName, @rid, @mdate at runtime. ( @mdate I have it further away in the query, it's too big to adapt it with myTable and to change all the sensitive data).

这里传递的参数将在运行时设置/更新变量@dbName，@rid，@ mdate。（@mdate我在查询中有更远的地方，它太大了，无法使用myTable进行调整并更改所有敏感数据）。

How can I do this ?? (using SQL Server 2012)

我怎样才能做到这一点？？（使用SQL Server 2012）

edit (based on the comments and answers):

编辑（根据评论和答案）：

so, it's @thatString = '--insert the query here ' . Then, in my case how can i set those variables according to the parameters inside the query? Should i do it with replace? like this: set @thatString= replace(@thatString, dbName, @dbname) ?

所以，它是@thatString =' - 插入查询'。那么，在我的情况下，我如何根据查询中的参数设置这些变量？我应该用替换吗？像这样：set @ thatString = replace（@thatString，dbName，@ dbname）？

edit 2

set @sql = '
use [someDbName];
use [123];
use [2012-03-03];

select ... '
set @sql = replace (@sql, 'someDbName', @dbName)
set @sql = replace (@sql, '123', @rid)
set @sql = replace (@sql, '2012-03-03', @mdate)
execute @sql
end

Did i get it right? is the execute @sql in the right place?

我做对了吗？是在正确的位置执行@sql？

I'm asking cause it doesnt work. i'm getting the name ' --part of my query here' is not a valid identifier

我问因为它不起作用。我得到的名字' - 我的查询部分'不是有效的标识符

1 个解决方案

#1

Names of databases or other objects cannot be specified dynamically from variables. The workaround is to compose a dynamic SQL query in a string, into which you concatenate the required names, and then execute (@thatString).

无法从变量动态指定数据库或其他对象的名称。解决方法是在字符串中组成动态SQL查询，在其中连接所需的名称，然后执行（@thatString）。

(You might think you can employ use, but it is scoped such that you would have to include the rest of your query within the same executed string.)

（您可能认为可以使用use，但它的作用域使得您必须在同一个执行的字符串中包含其余查询。）

Edit with more info as requested. You can compose the string however you like. If you need any more guidance, there are plenty of pages that discuss dynamic T-SQL. But hey, two ideas:

根据要求编辑更多信息。你可以随意编写字符串。如果您需要更多指导，有很多页面讨论动态T-SQL。但是，嘿，两个想法：

set @myDynamicQuery =
'
    use [' + @myDynamicDatabase + '];

    select BLAH from WHOM where DATA = ''what'';
';

or if you will be using the name a lot, you could reduce the hassle caused by breaking in and out of single quotes as follows - though I personally never use this as I don't like how it looks:

或者如果你将大量使用这个名字，你可以减少因打入和退出单引号而造成的麻烦，如下所示 - 尽管我个人从不使用它，因为我不喜欢它的样子：

set @myDynamicQuery =
'
    use [A_RARE_PLACEHOLDER];

    select BLAH from WHOM where DATA = ''what'';

    -- lots more uses of A_RARE_PLACEHOLDER
';

set @myDynamicQuery = replace(
    @myDynamicQuery,
    'A_RARE_PLACEHOLDER',
    @myDynamicDatabase
);

Then execute (@myDynamicQuery);

然后执行（@myDynamicQuery）;

#1