在 SQL 多部分标识符中连接数据库名称 [英] Concatenate the database name in a SQL multipart identifier
问题描述
我正在编写一个存储过程,我试图在 FROM
之后在多部分标识符中连接数据库名称,但没有成功.这是我尝试过的一种方法,但没有用
I'm writing a stored procedure and I'm trying to concatenate the database name in the multipart identifier after FROM
without success. This is one way that I tried but it didn't work
INSERT INTO CONTROL_LOGISTICA.dbo.ITEMS_TRAMITE (NRODCTO, TIPODCTO, PRODUCTO, EMPRESA, CODUSUARIO, CODUBICA, CANTIDAD, CCHECK_HIJO)
SELECT
@pNrodcto, @pTipodcto,
M.PRODUCTO, @pEmpresa, @pUsuario,
M.CODUBICA, M.CANTIDAD, 0
FROM
"@mEmpresa".dbo.MVTRADE M WITH(NOLOCK) -- Here I'm stuck
INNER JOIN
Dbo.vReporteMercia_ESP P ON P.PRODUCTO = M.PRODUCTO
WHERE
M.CANTIDAD <> 0 AND M.Origen = 'FAC'
AND M.NRODCTO = @pNrodcto AND M.TIPODCTO = @pTipodcto
我直接从存储过程中获取变量
I get the variable directly from the stored procedure
@pEmpresa AS char(20)
我知道我可以做一个 IF
之类的
I know that I can do an IF
like
IF (@pEmpresa = 'John Doe')
BEGIN
--..... and the query here
END
是否可以使用变量连接数据库名称?如何?还是我应该只使用 IF
?
Is it possible to concatenate the database name using the variable? How? Or should I just use the IF
?
推荐答案
你将不得不使用动态 sql 来做这样的事情.
You would have to use dynamic sql to do something like that.
declare @sql nvarchar(max);
declare @params nvarchar(max);
set @params= N'@pNrodcto int, @pTipodcto int, @pUsuario int, @pEmpresa char(20)';
set @sql = N'INSERT INTO CONTROL_LOGISTICA.dbo.ITEMS_TRAMITE (NRODCTO, TIPODCTO, PRODUCTO, EMPRESA, CODUSUARIO, CODUBICA, CANTIDAD, CCHECK_HIJO)
SELECT @pNrodcto,@pTipodcto,M.PRODUCTO,@pEmpresa,@pUsuario,M.CODUBICA,M.CANTIDAD,0
FROM '+db_name(db_id(@pEmpresa))+'.dbo.MVTRADE M WITH(NOLOCK) --Here im stuck
INNER JOIN Dbo.vReporteMercia_ESP P ON P.PRODUCTO = M.PRODUCTO
WHERE M.CANTIDAD <> 0 AND M.Origen = ''FAC''
AND M.NRODCTO = @pNrodcto AND M.TIPODCTO = @pTipodcto';
exec sp_executesql @sql, @params, @pNrodcto, @pTipodcto, @pUsuario, @pEmpresa;
为了避免将参数直接连接到已执行的 sql 字符串,我将参数包装在对 db_name()
和 db_id()
的调用中.这将为无效的数据库名称返回 null,但不会阻止某人引用您不希望他们引用的数据库.考虑将参数值与白名单进行比较.
To avoid directly concatenating a parameter to an executed sql string, I wrapped the parameter in calls to db_name()
and db_id()
. This would return null for an invalid database name, but wouldn't stop someone from referencing a database you do not want them to. Consider comparing the parameter value against a white list.
参考:
这篇关于在 SQL 多部分标识符中连接数据库名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!