SQL存储过程问题 [英] SQL Stored Procedure Problem
问题描述
亲爱的，
我有两个数据库
1.ta
2.Theorem_Analytics_database。
在 ta 数据库中， Client_Master 表
Client_ID Client_Name Client_Alias
4定理分析ta2
Theorem_Analytics_database ， User_Master 表
User_ID User_Name User_Password电子邮件Client_ID
1管理员user123 admin@gmail.com 4
我有一个存储过程在ta
我将在哪里取来自client_master的客户的所有信息。
如果客户名称不匹配与客户端，则显示无效客户端。
如果客户名称是匹配，然后它将从client_name创建动态数据库，如Theorem_ Analytics_database，
然后它将从Theorem_Analytic中选择来自User_Master的电子邮件s_database，并比较如果电子邮件与admin@gmail.com相同，则消息输出显示'成功'否则'不成功。'
请帮助我。
这是我的程序。
ALTER < span class =code-keyword> PROCEDURE [dbo]。[LoginProcedure]
（
@ Username VARCHAR （ 50 ）
， @ password VARCHAR （ 50 ）
， @ Client varchar （ 50 ）
， @ Response varchar （max）out
）
AS
声明 <跨越ss =code-sdkkeyword> @ Client_ID int ;
声明 @ Client_Name varchar （ 100 ）;
BEGIN TRY
- 从Client_Master获取客户端信息
选择 @ Client_ID = Client_ID，@ Client_Name = Client_Name < span class =code-keyword> FROM Client_Master WHERE Client_Alias = @ Client 或 Client_Name = @ Client;
IF @ Client_ID null
SET @ Response = ' 无效的客户名称
ELSE
BEGIN
声明 @ NewDB varchar （MAX）;
SET @ NewDB =替换（ @ Client_Name ，' '，' _'）+ ' _ database';
set @ NewDB = ' [' + @ NewDB + ' ]。dbo.User_Master';
声明 @sqlQuery varchar （MAX）， @ EMAIL VARCHAR （MAX）
SET @ EMAIL = ' User_Email'
set @sqlQuery = ' select' + @ EMAIL + ' 来自'的AS COL1 + @NewDB + ' 其中User_Name =''admin''';
exec （ @sqlQuery ）;
if @ EMAIL = ' admin @ gmail .com'
SET @ Response = ' success' + @ EMAIL;
else
SET @ Response = ' UNsuccess' + @ EMAIL;
- SET @Response = @EMAIL;
< span class =code-keyword> END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER（） AS ErrorNumber，ERROR_MESSAGE（） as ERRORMSG
END CATCH
而不是使用exec（@SqlQuery）
使用：
执行 sp_executesql @sqlQuery ，N ' @ EMAIL VARCHAR（MAX）OUTPUT'， @EMAIL = @ EMAIL 输出;
ALTER PROCEDURE [dbo]。[LoginProcedure]
@ Username VARCHAR （ 50 ），
@ password VARCHAR （ 50 ），
@ Client varchar （ 50 ），
@ Response varchar （max）out
AS
声明 @ Client_ID int ， @ Client_Name varchar （ 100 ）;
声明 @sqlQuery varchar （MAX）， @ EMAIL VARCHAR （MAX）
声明 @ NewDB varchar （MAX）， @UserEmail varchar （ 100 ）
BEGIN TRY
- 获取客户端来自Client_Master的信息
选择 @ Client_ID = Client_ID，@ Client_Name = Client_Name FROM Client_Master WHERE Client_Alias = @ Client 或 Client_Name = @ Client;
IF @ Client_ID 是 null
SET @ Response = ' 无效的客户名称
ELSE
BEGIN
SET @ NewDB =替换（ @Client_Name ，' '，' _'）+ ' _ database';
set @ NewDB = ' [' + @ NewDB + ' ]。dbo.User_Master';
SET @ EMAIL = ' User_Email'
set @sqlQuery = ' 选择' + @ EMAIL + ' AS COL1来自' + @ NewDB + ' 其中User_Name =''admin''';
执行 sp_executesql @sqlQuery ，N ' @ EMAIL VARCHAR（MAX）OUTPUT'， @ EMAIL = @ EMAIL 输出;
如果 @ EMAIL = ' admin@gmail.com'
SET @ Response = ' success' + @ EMAIL;
其他
SET @ EMAIL = ' UNsuccess' + @ EMAIL;
- SET @Response = @EMAIL;
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER（） AS ErrorNumber，ERROR_MESSAGE（） as ERRORMSG
END CATCH
希望这有助于...
Dear All,
I have two Database
1.ta
2.Theorem_Analytics_database.
In ta Database, Client_Master Table
Client_ID Client_Name Client_Alias
4 Theorem Analytics ta2
In Theorem_Analytics_database , User_Master Table
User_ID User_Name User_Password Email Client_ID
1 Admin user123 admin@gmail.com 4
I have a stored Procedure in ta
Where I will fetch all information of client from client_master.
If client name is not match with Client ,display invalid client.
If client name is match ,then it will create dynamically database from client_name like Theorem_ Analytics_database,
Then it will select Email from User_Master from Theorem_Analytics_database, and compare if email is equal with admin@gmail.com, a messageas output display’Success’ else ‘unsuccess.’
PLEASE Help me.
This is my procedure.
ALTER PROCEDURE [dbo].[LoginProcedure]
(
@Username VARCHAR(50)
,@password VARCHAR(50)
,@Client varchar(50)
,@Response varchar(max) out
)
AS
declare @Client_ID int;
declare @Client_Name varchar(100);
BEGIN TRY
--Get Client Information from Client_Master
Select @Client_ID=Client_ID,@Client_Name=Client_Name FROM Client_Master WHERE Client_Alias= @Client or Client_Name=@Client;
IF @Client_ID is null
SET @Response='Invalid Client Name'
ELSE
BEGIN
declare @NewDB varchar(MAX);
SET @NewDB=Replace(@Client_Name,' ','_')+'_database';
set @NewDB='['+@NewDB+'].dbo.User_Master';
declare @sqlQuery varchar(MAX),@EMAIL VARCHAR(MAX)
SET @EMAIL='User_Email'
set @sqlQuery='select '+@EMAIL+' AS COL1 from '+ @NewDB+' where User_Name=''admin''';
exec (@sqlQuery);
if @EMAIL='admin@gmail.com'
SET @Response = 'success'+@EMAIL;
else
SET @Response = 'UNsuccess'+@EMAIL;
--SET @Response = @EMAIL;
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() as ERRORMSG
END CATCHInstead of Usingexec(@SqlQuery)
Use this:
execute sp_executesql @sqlQuery, N'@EMAIL VARCHAR(MAX) OUTPUT', @EMAIL = @EMAIL output;
ALTER PROCEDURE [dbo].[LoginProcedure] @Username VARCHAR(50), @password VARCHAR(50), @Client varchar(50), @Response varchar(max) out AS Declare @Client_ID int, @Client_Name varchar(100); declare @sqlQuery varchar(MAX),@EMAIL VARCHAR(MAX) declare @NewDB varchar(MAX),@UserEmail varchar(100) BEGIN TRY --Get Client Information from Client_Master Select @Client_ID=Client_ID,@Client_Name=Client_Name FROM Client_Master WHERE Client_Alias= @Client or Client_Name=@Client; IF @Client_ID is null SET @Response='Invalid Client Name' ELSE BEGIN SET @NewDB=Replace(@Client_Name,' ','_')+'_database'; set @NewDB='['+@NewDB+'].dbo.User_Master'; SET @EMAIL='User_Email' set @sqlQuery='select '+@EMAIL+' AS COL1 from '+ @NewDB+' where User_Name=''admin'''; Execute sp_executesql @sqlQuery, N'@EMAIL VARCHAR(MAX) OUTPUT', @EMAIL = @EMAIL output; If @EMAIL='admin@gmail.com' SET @Response = 'success'+@EMAIL; Else SET @EMAIL = 'UNsuccess'+@EMAIL; --SET @Response = @EMAIL; END END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() as ERRORMSG END CATCH
Hope this Helps...
这篇关于SQL存储过程问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
