Need help with some stored procedure
The 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
In the second Select statement:
SELECT Language FROM Admin.Language WHERE LanguageID=FQ.LanguageID
In this statement, I want the value of "FQ.LanguageID" from 1st select statement, so I wrote this:-
Apparently didn't work. It says "The multi-part identifier "FQ.LanguageID" could not be bound."
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
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?
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
The scope of FQ is limited to the first select statement.
Your options include:
- Passing AdminLanguageID as a parameter as you have suggested
- Retrieving AdminLanguageID in a prior statement (select @AdminLanguageID = AdminLanguageID from...)
- Joining Admin.Language with Admin.Ques
- Using a subquery (select ... from Admin.Language where AdminLanguageID in (select AdminLanguageID from Admin.Ques where ...)