如何在存储过程中将参数传递给use语句？

本文介绍了如何在存储过程中将参数传递给use语句？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个选择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...