CODEWITHSUNDEEP
c#asp.netsql-server
Serenity
Serenity

Reputation: 5098

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:-

LanguageID=FQ.LanguageID

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?

Upvotes: 0

Views: 93

Answers (3)

p.campbell
p.campbell

Reputation: 100657

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

Upvotes: 2

Mayo
Mayo

Reputation: 10812

The scope of FQ is limited to the first select statement.

Your options include:

  1. Passing AdminLanguageID as a parameter as you have suggested
  2. Retrieving AdminLanguageID in a prior statement (select @AdminLanguageID = AdminLanguageID from...)
  3. Joining Admin.Language with Admin.Ques
  4. Using a subquery (select ... from Admin.Language where AdminLanguageID in (select AdminLanguageID from Admin.Ques where ...)

Upvotes: 2

The Scrum Meister
The Scrum Meister

Reputation: 30141

Why not just join them into 1 select?

ALTER PROC [Admin].[sp_Ques]    
(
 @QuesID bigint
 )
AS
BEGIN
 IF @QuesID = 0
  SET @QuesID =NULL
SELECT     FQ.QuesID, FQ.Ques,QuesAns as QuesAns,FQ.QuesAns[Answers], FQT.QuesType ,FQ.QuesTypeID, FQ.QuesParentID, FQ.Active,FQ.AdminLanguageID, AL.TelerikLanguage
FROM         Admin.Ques FQ
LEFT OUTER JOIN Admin.QuesTypes FQT ON FQT.QuesTypeID=FQ.QuesTypeID
    LEFT JOIN Admin.Language AL ON AL.AdminLanguageID=FQ.AdminLanguageID
WHERE   FQ.QuesID = QuesID OR @QuesID IS NULL

END

Upvotes: 1

Related Questions