如何在sql中使用服务器实例名作为变量名 [英] How to use server instance name as variable name in sql

问题描述

如何在用于链接服务器的select语句中放置服务器实例名称动态，下面是我的声明

  SELECT  *  FROM  [HOME-PC] .SampleDB.dbo.Records  WHERE  Col_1_1 = 62 和 Col_1_2 = 1 

现在我想要下面

 声明  @ instancename   nvarchar （< span class =code-digit> 50 ）
  set  @ instancename = '  Home-PC' 
  SELECT  *  FROM  [ @ instancename ]。SampleDB.dbo.Records  WHERE  Col_1_1 = 62 和 Col_1_2 = 1 

请建议我..

解决方案

构建你的sql字符串，最后用sp_executesql执行它

check这篇文章： http://stackoverflow.com/questions/803211 / how-to-sp-executionq-result-into-a-variable [ ^ ]

请参阅此处给出的两个解决方案： http://stackoverflow.com/questions/10170791/linked-server-query-dynamic-sql [<一个href =http://stackoverflow.com/questions/10170791/linked-server-query-dynamic-sqltarget =_ blanktitle =New Window> ^ ]。您可以使用同义词 [ ^ ]和/或动态查询。

我通过连接字符串解决了它然后执行

< pre lang =SQL> CREATE procedure [dbo]。[GetRemoteData]
（
@ District nvarchar （ 10 ），
@ Vdc nvarchar （ 10 ），
@ instanceName nvarchar （max），
@ dbname nvarchar （max）
）
as
开始
声明 @ conInstanceName nvarchar （max）
声明 @ tablename nvarchar （max）
set @ tablename = ' dbo.Records'
set @ conInstanceName = ' [' + @ instanceName + ' ]' + ' 。' + @ dbname + ' 。' + @ tablename
声明 @ SQLQUERY nvarchar （ 500 ）
set @ SQLQUERY = ' 从'<中选择前10 * / span> + @ conInstanceName
EXEC sp_executesql @ SQLQUERY

end

How to put server instance name dynamic in select statement used for linked server, below is my statement

SELECT  * FROM  [HOME-PC].SampleDB.dbo.Records WHERE Col_1_1=62 and Col_1_2=1

Now i want like below

declare @instancename nvarchar(50)
set @instancename='Home-PC'
SELECT  * FROM  [@instancename].SampleDB.dbo.Records WHERE Col_1_1=62 and Col_1_2=1

please suggest me..

解决方案

Build your sql string and finally execute it using sp_executesql
check this post : http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable[^]

See the two solutions given here: http://stackoverflow.com/questions/10170791/linked-server-query-dynamic-sql[^]. You can use a synonym[^] and/or dynamic query.

I solved it by concatenating string and then execute

CREATE procedure [dbo].[GetRemoteData]
(
@District nvarchar(10),
@Vdc nvarchar(10),
@instanceName nvarchar(max),
@dbname nvarchar(max)
)
as
begin
declare @conInstanceName nvarchar(max)
declare @tablename nvarchar(max)
set @tablename='dbo.Records'
set @conInstanceName='['+@instanceName+']'+'.'+@dbname+'.'+@tablename
declare @SQLQUERY nvarchar(500)
set @SQLQUERY='select top 10 * from'+@conInstanceName
EXEC sp_executesql @SQLQUERY

end

这篇关于如何在sql中使用服务器实例名作为变量名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！