如何将数据库名称作为参数传递给Sql存储过程？ [英] How Do I Pass Database Name As Parameter Is Sql Stored Procedure?

问题描述

Hello House;

我有以下sql脚本：

  SELECT  pp.title，pp.surname，pp.firstname，pp.phone，pp.emai，rcd.reg_code，pp.genCode 
  FROM  SchoolAngelPortalDB.dbo.primaryparent pp 
  LEFT   JOIN  Newschoolcore.dbo .registration_codes rcd  ON  pp.genCode = rcd.reg_code 
  WHERE  rcd.name =  @ schoolName  
）

我如何使用

 SchoolAngelPortalDB 

和

 Newschoolcore 

作为参数并传递C＃中的参数值，以便我可以接受db名称为来自用户的输入。



提前致谢。

解决方案

首先请参阅此链接有用： -

http://forums.databasejournal.com/showthread.php?36588-Passing-database-name-to -a-stored-procedure [ ^ ]



你可以试试这样 -

  set   quoted_identifier   off  
  go  
 创建 程序 usp_accessdb  @ dbname1   varchar （ 128 ），  @ dbname2   varchar （ 128 ），< span class =code-sdkkeyword> @ schoolName   varchar （ 100 ）
  as  
 
 声明  @ query   varchar （ 1000 ）
  set   @查询 =   SELECT pp.title，pp.surname，pp.firstname，pp.phone，pp。 emai，rcd.reg_code，pp.genCode 
 FROM @ dbname1.dbo.primaryparent pp 
 LEFT JOIN @ dbname2.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code 
 WHERE rcd.name = @schoolName 
  exec （ @ query ）
 < span class =code-keyword> go  

Hi


请检查此网址。





http://itknowledgeexchange.techtarget.com/itanswers/passing-a-database-name-as-a-stored-procedure-parameter/ [ ^ ]





问候

Dominic

在sql中使用动态查询对于.. :)



例如

 声明  @ query   varchar （ 1000 ）， @ dbname   varchar （ 50 ）， @ tableName   varchar （ 50 ）
  set  @ dbname = '  DatabaseName' 
  set  @ tableName = '  TableName' 
  set   @ query  = '  select * from' + @ dbname + ' 。[dbo]。' + @ tableName 
  exec （ @ query ）

将数据库名称传递给存储过程 [ ^ ]

Hello House;
I have have the following sql script:

SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.emai,rcd.reg_code,pp.genCode
    FROM SchoolAngelPortalDB.dbo.primaryparent pp
    LEFT JOIN Newschoolcore.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
    WHERE rcd.name = @schoolName 
)

how do i use

SchoolAngelPortalDB

and

Newschoolcore

as parameter and pass the parameter value in C#, so that i could accept the db name as input from user.

Thanks in advance.

解决方案

Refer this link first it is useful:-
http://forums.databasejournal.com/showthread.php?36588-Passing-database-name-to-a-stored-procedure[^]

you can try like this-

set quoted_identifier off
go
Create procedure usp_accessdb @dbname1 varchar(128),@dbname2 varchar(128),@schoolName varchar(100)
as

declare @query varchar(1000)
set @query = "SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.emai,rcd.reg_code,pp.genCode
    FROM @dbname1.dbo.primaryparent pp
    LEFT JOIN @dbname2.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
    WHERE rcd.name = @schoolName "
exec (@query)
go

Hi
Please check this url.


http://itknowledgeexchange.techtarget.com/itanswers/passing-a-database-name-as-a-stored-procedure-parameter/[^]


Regards
Dominic

Use dynamic query in sql for that.. :)

e.g.

declare @query varchar(1000),@dbname varchar(50),@tableName varchar(50)
set @dbname='DatabaseName'
set @tableName='TableName'
set @query = 'select * from ' +@dbname+'.[dbo].'+@tableName
exec (@query)

Passing database name to a stored procedure[^]

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