需要一些存储过程帮助 [英] Need help with some stored procedure

问题描述

该存储过程：

ALTER PROC [Admin].[sp_Ques]    
(
 @QuesID bigint
 )
AS
BEGIN
 IF @QuesID = 0
  SET @QuesID =NULL
SELECT     FQ.QuesID, FQ.Ques,QuesAns 
FROM         Admin.Ques FQ

WHERE   FQ.QuesID = Coalesce(@QuesID,QuesID)

SELECT Language FROM Admin.Language WHERE LanguageID=FQ.LanguageID

END

在第二个SELECT语句：

In the second Select statement:

SELECT Language FROM Admin.Language WHERE LanguageID=FQ.LanguageID

在此声明，我想从第一个select语句FQ.LanguageID的价值，所以我写了这篇文章： -

In this statement, I want the value of "FQ.LanguageID" from 1st select statement, so I wrote this:-

LanguageID=FQ.LanguageID

显然没有奏效。它说：多部分组成的标识符FQ.LanguageID无法约束。

Apparently didn't work. It says "The multi-part identifier "FQ.LanguageID" could not be bound."

我需要这个通过 LanguageID 来存储过程作为参数，然后用它作为： -

Do I need to pass this LanguageID to the stored procedure as a parameter and then use it as:-

SELECT Language FROM Admin.Language WHERE LanguageID=@LanguageID

我怎样才能让这个 LanguageID = FQ.LanguageID 工作，如果我不希望传递 LanguageID 作为第二个参数的存储过程？有什么办法？

How can I make this LanguageID=FQ.LanguageID work if I don't want to pass LanguageID as the second argument to the stored procedure? Is there a way?

推荐答案

也许创建一个局部变量来保存的检索的LanguageID。在previous SELECT 中值分配给它。加入 TOP 1 只是确保如果/当你曾经有在第一个查询（多个匹配确实你就当 @Ques 是零或空！），只有一个值，该查询返回，从而使单个值到您的变量中。

Perhaps create a local variable to hold the LanguageID that's being retrieved. Assign a value to it during the previous SELECT. The addition of TOP 1 simply ensures that if/when you ever have multiple matches in the first query (indeed you will when @Ques is zero or null!), only one value is returned in that query, thereby allowing a single value into your variable.

DECLARE @Lang int  --whatever datatype your QuesID is.

SELECT   TOP 1
           FQ.QuesID, FQ.Ques,QuesAns as QuesAns,
           FQ.QuesAns[Answers], FQT.QuesType ,
           FQ.QuesTypeID, FQ.QuesParentID, FQ.Active, FQ.AdminLanguageID
          ,@Lang = FQ.AdminLanguageID
FROM         Admin.Ques FQ
LEFT OUTER JOIN Admin.QuesTypes FQT ON FQT.QuesTypeID=FQ.QuesTypeID
WHERE   FQ.QuesID = Coalesce(@QuesID,QuesID)

SELECT TelerikLanguage FROM Admin.Language 
WHERE AdminLanguageID=@Lang 

这篇关于需要一些存储过程帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！