如何在存储过程中将参数传递给use语句？ [英] How to pass a parameter to use statement in a stored procedure?

问题描述

我有一个选择sys_databases文件的存储过程。现在在我的存储过程中，我想在此过程中传递用户指定的数据库名称，这是一个动态值。

I have a stored procedure that selects the sys_databases files. Now in my stored procedure, I wanted to pass the user specified database name which is a dynamic value here in this procedure.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[GetAllPhysicalPath]    Script Date: 06/18/2014 15:11:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetAllPhysicalPath]
@txtDatabaseName VARCHAR(50)
AS
BEGIN
SELECT name,filename FROM SYSALTFILES Where name=@txtDatabaseName


///wanted something like this statement : use [@txtDatabaseName]
END

是否可能。？？

Is it possible.??

推荐答案

是...有点。

你有什么do将SQL组合为字符串，并执行字符串：

Yes...sort of.
What you have to do is assemble the SQL as a string, and execute the string:

EXEC('SELECT name,filename FROM SYSALTFILES Where name=' + @txtDatabaseName)

试试这段代码

try this code

DECLARE @txtDatabaseName NVARCHAR(50) = N'TestDb ' 
DECLARE @Query NVARCHAR(100)= 'SELECT EID,ENAME FROM Employee'  
DECLARE @MyQuery NVARCHAR(500) =''
SET @MyQuery = 'USE '+ @txtDatabaseName + @Query 

SELECT @MyQuery

EXEC sp_executesql @MyQuery

USE 关键字不是查询语言的一部分，因此您无法使用它在存储过程中......

即使你能做到这一点也无济于事。系统表属于master数据库，因此为了能够从中检索数据，你应该像这样编写你的行：

USE keyword is not part of the query language so you cant use it in stored procedure...
And even you could that will not help you. System tables are belong to master database so to be able to retrieve data from them you should write your line like this:

SELECT name, filename FROM master..SYSALTFILES Where name = @txtDatabaseName

如果您拥有读取主数据库的正确权限，那将为您提供数据...

That will bring you the data if you have the proper rights to read master db...

这篇关于如何在存储过程中将参数传递给use语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！