如何在SQL查询中使用动态数据库名称 [英] How do I use database name dynamicaly in SQL query
本文介绍了如何在SQL查询中使用动态数据库名称的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这里我有2个数据库,一个是我的FARVISIONDB,另一个是JKUMARJV.Both有相同的表。使用检查组织即前端的数据库。这里我用datanm创建了存储过程作为参数。
我尝试过:
所以我在存储过程中的查询,如
< pre> DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),@ dbnm varchar(1000), @rslt varchar(max)
set @ dbnm ='farvisiondb'
set @cols ='STUFF((SELECT distinct'',''+ QUOTENAME(STATENAME)
from((从'+ @ dbnm +'中选择CODE + M_STATEWISEGSTINACTAG)e内连接
(从'+ @ dbnm +'.. M_STATEMASTER中选择CODE,STATENAME)f在e.code = f.code上)
FOR XML PATH(''''),TYPE).value(''''',''NVARCHAR(MAX)''),1,1,''''''
print @cols
EXEC sp_executesql @cols,N'@ rslt v archar输出',@ rslt OUTPUT
完美打印查询即当我通过farvisiondb作为参数然后它完美
STUFF((SELECT distinct','+ QUOTENAME(STATENAME)
from(
(select CODE)来自farvisiondb..M_STATEWISEGSTINACTAG)e inner join
(从farvisiondb..M_STATEMASTER中选择CODE,STATENAME)f on e.code = f.code
)
FOR XML PATH(''), TYPE).value('。','NVARCHAR(MAX)'),1,1,'')
但它不是执行。
请帮帮我。谢谢。
解决方案
你错过了初始SELECT
。您还可以简化连接以避免嵌套查询。
set @cols = N ' SELECT STUFF((SELECT DISTINCT'',''+ QUOTENAME( f.STATENAME)
FROM' + QUOTENAME( @ dbnm )+ N ' 。dbo.M_STATEWISEGSTINACTAG as INNER JOIN
' + QUOTENAME( @ dbnm )+ N ' 。dbo.M_STATEMASTER as e ON e.code = f.code
FOR XML PATH(''' '),TYPE)。value(''''',''NVARCHAR(MAX)''),1,1,''''''
您需要在动态sql中完全限定表。这需要采用格式[数据库]。[架构]。[table]
。您错过了架构。例如
' FROM' + < span class =code-sdkkeyword> @ dbnm + ' .dbo.M_STATEWISEGSTINACTAG)as STCD'< /跨度>
Hi,
Here i have 2 database one is my FARVISIONDB and another is JKUMARJV.Both have same table.Here use check organisation i.e database from front end.Here i have created stored procedure with datanm as parametere.
What I have tried:
So my query inside stored procedure like <pre>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX),@dbnm varchar(1000),@rslt varchar(max) set @dbnm='farvisiondb' set @cols = 'STUFF((SELECT distinct '','' + QUOTENAME(STATENAME) from ((select CODE from '+@dbnm+'..M_STATEWISEGSTINACTAG) e inner join (select CODE,STATENAME from '+@dbnm+'..M_STATEMASTER) f on e.code=f.code) FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'') ,1,1,'''')' print @cols EXEC sp_executesql @cols, N'@rslt varchar output',@rslt OUTPUT
It prints query perfectly i.e when i pass farvisiondb as parameter then it perfect
STUFF((SELECT distinct ',' + QUOTENAME(STATENAME) from ( (select CODE from farvisiondb..M_STATEWISEGSTINACTAG) e inner join (select CODE,STATENAME from farvisiondb..M_STATEMASTER) f on e.code=f.code ) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
But it not excecute.
Please help me.Thanks in advance.
解决方案
You're missing the initialSELECT
. You can also simplify the join to avoid the nested queries.
set @cols = N'SELECT STUFF((SELECT DISTINCT '','' + QUOTENAME(f.STATENAME) FROM ' + QUOTENAME(@dbnm) + N'.dbo.M_STATEWISEGSTINACTAG As e INNER JOIN ' + QUOTENAME(@dbnm) + N'.dbo.M_STATEMASTER As f ON e.code = f.code FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')'
You need to fully qualify the table in the dynamic sql. That needs to be in the format[database].[schema].[table]
. You are missing out the schema. E.g.
'FROM ' + @dbnm + '.dbo.M_STATEWISEGSTINACTAG) as STCD'
这篇关于如何在SQL查询中使用动态数据库名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文