Return rows in stored procedure

Question

I am trying to develop an application in ASP.NET with blogs and categories in two languages. One of the functionalities is returning the name of a category in a particular language.

Not all categories have two languages. Some have English, others have Italian, others have both. Here's the function that performs the select I was talking about.

DECLARE @Name NVARCHAR(250)

IF EXISTS(SELECT Name = Coalesce(BlogCategoryTranslation.Name, ' ')
          FROM BlogCategoryTranslation 
          WHERE BlogCategoryID = @CategoryID AND LanguageID = @LanguageID)
BEGIN   
    SET @Name = (SELECT Name
                 FROM BlogCategoryTranslation 
                 WHERE BlogCategoryID = @CategoryID 
                   AND LanguageID = @LanguageID)    
END 
ELSE
BEGIN
    SET @Name = (SELECT TOP 1 Name 
                 FROM BlogCategoryTranslation 
                 WHERE BlogCategoryID = @CategoryID 
                   AND LanguageID = @LanguageID);
END

RETURN @Name

Below there is a stored procedure that returns the category name for a given language. What I want is to always return a category. If it doesn't exist in a particular language, I want to display the record in the other language.

Is this possible and if it is, what would be the suggestions in order to do that?

ALTER PROCEDURE [dbo].[BlogCategoryLanguage]
    @BlogCategoryID INT,
    @LanguageID INT
AS
BEGIN
    SELECT 
        BlogCategoryID AS 'CategoryID',
        Language.Name AS 'Language',
        dbo.BlogNameCategoryByLanguage(1,1) as 'Name',
        dbo.Blog_PublishedInCategory(1,BlogCategory.ID) AS 'Published'
    FROM 
        BlogCategoryTranslation
    INNER JOIN 
        BlogCategory ON BlogCategory.ID = BlogCategoryTranslation.BlogCategoryID
    INNER JOIN 
        Language ON Language.ID = BlogCategoryTranslation.LanguageID
    WHERE 
        BlogCategoryID = 1
END

I would really appreciate a few tips. It's the first time I have posted a question here and I'm not quite sure how this works. If this is a repost somehow,sorry about that.

Would I be correct in summarizing that the code in the first block represents the code of BlogNameCategoryByLanguage and that represents the problem? If so, the easiest way is to skip the check, and do a top 1 on the set ordered first on being the right language. A second order by would probably be preferable as well, e.g. on default language. — Me.Name
– Me.Name, Commented Jun 29, 2015 at 10:04
Thank you for your answer. Yes, the first block represents the function ALTER FUNCTION [dbo].[BlogNameCategoryByLanguage] (@CategoryID INT, @LanguageID INT) RETURNS NVARCHAR(250). You're suggesting that I could select the correct language first. And how do I display the row if the language doesn''t correspond? — Nadia-Miroslava Popescu
– Nadia-Miroslava Popescu, Commented Jun 29, 2015 at 10:12

ughai · Accepted Answer · 2015-06-29 10:32:21Z

You can use CASE based ORDER BY with TOP 1 like this.

ORDER BY CASE WHEN LanguageID = @LanguageID THEN 1 ELSE 2 END ASC will give preference to the language with LanguageID = @LanguageID. If that Language is not available for the current @Category, it will pick a random language available for that category.

Something like this.

DECLARE @Name NVARCHAR(250)
SELECT @Name = TOP 1 Name
FROM BlogCategoryTranslation
WHERE BlogCategoryID = @CategoryID
ORDER BY CASE WHEN LanguageID = @LanguageID THEN 1 ELSE 2 END ASC;

RETURN @Name

EDIT

You can use the same ORDER BY and TOP 1 in your main stored procedure like this.

SELECT TOP 1 
    BlogCategoryID AS 'CategoryID',
    Language.Name AS 'Language',
    dbo.BlogNameCategoryByLanguage(1,1) as 'Name',
    dbo.Blog_PublishedInCategory(1,BlogCategory.ID) AS 'Published'
FROM 
    BlogCategoryTranslation
INNER JOIN 
    BlogCategory ON BlogCategory.ID = BlogCategoryTranslation.BlogCategoryID
INNER JOIN 
    Language ON Language.ID = BlogCategoryTranslation.LanguageID
WHERE 
    BlogCategoryID = 1
ORDER BY CASE WHEN Language.LanguageID = @LanguageID THEN 1 ELSE 2 END ASC

Thank you very much @ughai. Say we have a category on both languages, the procedure returns both entries for that, even if I mentioned SET @Name = (SELECT TOP 1 Name FROM BlogCategoryTranslation WHERE BlogCategoryID = @CategoryID ORDER BY CASE WHEN LanguageID = @LanguageID THEN 1 ELSE 2 END ASC); How do I select only one of the results?
Thank you for your time and help. It actually does exactly that. This is great.

Collectives™ on Stack Overflow

Return rows in stored procedure

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related