I am looking to write a stored procedure which received a database name along with other parameters, and the stored procedure needs to work on the Database which it received any thoughts please
我期待编写一个存储过程,它接收一个数据库名称和其他参数,并且存储过程需要在数据库上工作,它收到任何想法请
1 个解决方案
#1
0
Something like the following should work, as long as correct permissions are setup:
只要设置了正确的权限,就可以使用以下内容:
CREATE PROCEDURE dbo.sptest
@DB VARCHAR(50)
AS
BEGIN
DECLARE @sqlstmt VARCHAR(MAX)
SET @sqlstmt='SELECT TOP 10 * FROM ' + @DB + '.dbo.YourTableName'
sp_executesql @sqlstmt
END
GO
As mentioned, be very careful when using dynamic SQL like this- only use with trusted sources because of the ability to wreck havoc on your DB. At a minimum, you should add some checking of the value of @DB passed in to make sure it matches a limited list of database names that it will work with.
如上所述,在使用像这样的动态SQL时要非常小心 - 因为能够破坏数据库的破坏,所以只能使用受信任的源。至少,您应该添加对传入的@DB值的一些检查,以确保它匹配将使用的有限数据库名称列表。
#1
0
Something like the following should work, as long as correct permissions are setup:
只要设置了正确的权限,就可以使用以下内容:
CREATE PROCEDURE dbo.sptest
@DB VARCHAR(50)
AS
BEGIN
DECLARE @sqlstmt VARCHAR(MAX)
SET @sqlstmt='SELECT TOP 10 * FROM ' + @DB + '.dbo.YourTableName'
sp_executesql @sqlstmt
END
GO
As mentioned, be very careful when using dynamic SQL like this- only use with trusted sources because of the ability to wreck havoc on your DB. At a minimum, you should add some checking of the value of @DB passed in to make sure it matches a limited list of database names that it will work with.
如上所述,在使用像这样的动态SQL时要非常小心 - 因为能够破坏数据库的破坏,所以只能使用受信任的源。至少,您应该添加对传入的@DB值的一些检查,以确保它匹配将使用的有限数据库名称列表。