如何使用mssql 2014存储过程在查询中动态使用数据库名称？ [英] How to use database name dynamically in query using mssql 2014 stored procedure?

问题描述

嗨
我需要从multipe数据库到analyiner的数据进入单表进行分析。



我的代码

Hi I need data from multipe database to compaine into single table for analysis purpose.

My code

Declare @dbname varchar(50),@sqlCommand varchar(1000)
Set @dbname ='dbname'
set @sqlCommand = '
	SELECT  RegDate,  SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity,  Loan, TRStatus 
        FROM' + @dbname +  'dbo.MemberRecord '

EXEC (@sqlCommand)

错误是

消息102，等级15，状态1，行15

'。'附近的语法不正确。



如果我使用下面的代码，工作正常

Error is
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '.'.

if i using the below code, working fine

SELECT  RegDate,  SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity,  Loan, TRStatus 
	FROM  [dbname].dbo.[MemberRecord]  ORDER BY SID 

< br $> b $ b

请给我建议

谢谢



Maideen



我尝试了什么：

Pls advice me
Thank you

Maideen

What I have tried:

Declare @dbname varchar(50),@sqlCommand varchar(1000)
Set @dbname ='dbname'
set @sqlCommand = '
	SELECT  RegDate,  SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity,  Loan, TRStatus 
        FROM' + @dbname +  'dbo.MemberRecord '

EXEC (@sqlCommand)

推荐答案

试试这个。我将下面的代码完全按照cp页面内容的副本粘贴到剪贴板上：

Try this. I've pasted your code below exactly as it appears in a copy of cp page content to my clipboard:

Declare @dbname varchar(50),@sqlCommand varchar(1000)
Set @dbname ='dbname'
set @sqlCommand = '
	SELECT  RegDate,  SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity,  Loan, TRStatus 
        FROM' + @dbname +  'dbo.MemberRecord '

EXEC (@sqlCommand)

好的。所以现在插入这个语句，（在SET之后和EXEC之前）：

Ok. So insert this statement now, (right after the SET and before the EXEC):

PRINT @sqlCommand

并且，注释掉这句话：

And, comment out this statement:

EXEC(@sqlCommand)

运行如上所述的整个语句...

Run the whole statement altered as above ...

SELECT  RegDate,  SID, Name, CAddress, TelEM1, TelEM2, Relation, Disablity,  Loan, TRStatus
    FROMdbnamedbo.MemberRecord

看看发生了什么？单词FROM和@dbname变量的指定值'dbname'之间没有空格，也没有指定模式名称和数据库之间划分的点（。）。使用散布在代码中的PRINT语句是一种非常有用的调试方法，因为它可以很容易地被注释掉来进行连续的测试。



那个。你错过了必须成为你连接的一部分：

See what happened? There's no space between the word FROM and the @dbname variable's assigned value of 'dbname' nor is there a "dot" (.) designating the division between the schema name and the database. Usg the PRINT statement interspersed in your code is one method of debugging which is quite useful because it can be commented out easily to do successive tests.

That "." you're missing has to become part of your concatenation:

FROM ' + @dbname + '.' + 'dbo.MemberRecord'

谢谢



现已解决



Maideen

Thank you

now Solved

Maideen

这篇关于如何使用mssql 2014存储过程在查询中动态使用数据库名称？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！