CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Ramdas
Ramdas

Reputation: 111

Get Boolean Value

I have two tables:

1) BlogAlbum:

Id  AlbumName   CoverPhoto          CreatedDate                    Description     
1   Onam        noImage.jpg     2012-07-05 10:54:46.977            Onam is an ancient festival which still survives in modern times. Kerala's rice harvest festival and the Festival of Rain Flowers, which fall on the Malayalam month of Chingam, celebrates the Asura King Mahabali's annual visit from Patala (the underworld). Onam is unique since Mahabali has been revered by the people of Kerala since prehistory.    According to the legend, Kerala witnessed its golden era during the reign of King Mahabali. The Brahma-Vaivarta Puranam explains that Lord Vishnu wante     
2   Birds       noImage.jpg     2012-07-05 11:02:48.667         
3   Nature    gold-fish.jpg     2012-07-05 11:03:36.503

2. BlogPhoto:

    Id  AlbumId PhotoName                  AddedDate            
    29  1   Photo0364.jpg      2012-07-05 11:01:18.270          
    30  1   Photo0380.jpg      2012-07-05 11:01:35.723         
    31  1   Photo0404.jpg      2012-07-05 11:01:47.717          
    32  1   Photo0404.jpg      2012-07-05 11:02:34.457          
    33  2   f.jpg              2012-07-05 11:03:03.300          
    34  2   g.jpg              2012-07-05 11:03:12.917         
    35  3   image005.jpg       2012-07-05 11:03:45.367          
    36  3   1.jpg              2012-07-05 11:03:57.837          
    37  3   2.jpg              2012-07-05 11:04:05.580          
    38  3   na.jpg             2012-07-05 11:04:17.337          
    39  3   gold-fish.jpg      2012-07-05 11:06:29.453

I wrote a stored procedure to list the details of BlogPhoto:

ALTER PROCEDURE [dbo].[BlogPhotoDetailsById]
    (
        @Id int
    )
    AS
    BEGIN
        SELECT [HRM_BlogPhoto].[Id] 
              ,[HRM_BlogPhoto].[AlbumId] 
              ,[HRM_BlogPhoto].[PhotoName] 
              ,[HRM_BlogPhoto].[AddedDate] 
              ,[HRM_BlogPhoto].[Title] 
              ,[HRM_BlogPhoto].[ModifiedDate]
          FROM [HRM_BlogPhoto] 
        WHERE [HRM_BlogPhoto].[Id] = @Id 

    END

What I want is that if BlogPhoto.PhotoName equals BlogAlbum.CoverPhoto, then in the above storedprocedure I need to display "true" and if not I need to display "false".

How could I modify the above stored procedure?

Upvotes: 1

Views: 99

Answers (1)

StuartLC
StuartLC

Reputation: 107407

The below will display whether or not the BlogPhoto is a CoverPhoto of at least one BlogAlbum (noting that a photo could be on more than one album)

ALTER PROCEDURE [dbo].[BlogPhotoDetailsById] ( @Id int ) AS BEGIN SELECT [HRM_BlogPhoto].[Id] ,[HRM_BlogPhoto].[AlbumId] ,[HRM_BlogPhoto].[PhotoName] ,[HRM_BlogPhoto].[AddedDate] ,[HRM_BlogPhoto].[Title] ,[HRM_BlogPhoto].[ModifiedDate] ,CASE WHEN EXISTS (SELECT * FROM [blogalbum] WHERE [blogalbum].CoverPhoto = [HRM_BlogPhoto].[PhotoName] ) THEN 'true' ELSE 'false' END AS [IsCoverPhoto] FROM [HRM_BlogPhoto] WHERE [HRM_BlogPhoto].[Id] = @Id

Edit

Bennor's point is taken. The below will avoid the nested loop, although note because of potentials for duplicate cover photos, you will need to add the distinct keyword.

ALTER PROCEDURE [dbo].[BlogPhotoDetailsById]
(
    @Id int
)
AS
BEGIN
    SELECT DISTINCT 
          [HRM_BlogPhoto].[Id]
          ,[HRM_BlogPhoto].[AlbumId] 
          ,[HRM_BlogPhoto].[PhotoName] 
          ,[HRM_BlogPhoto].[AddedDate] 
          ,[HRM_BlogPhoto].[Title] 
          ,[HRM_BlogPhoto].[ModifiedDate]
          ,CASE WHEN [blogalbum].CoverPhoto IS NOT NULL
             THEN 'true' 
             ELSE 'false' 
           END AS [IsCoverPhoto]
      FROM [HRM_BlogPhoto] 
            LEFT OUTER JOIN [blogalbum]
            ON [blogalbum].CoverPhoto = [HRM_BlogPhoto].[PhotoName]
    WHERE [HRM_BlogPhoto].[Id] = @Id

One caveat - for performance reasons, you should ensure that you have an index on [blogalbum].CoverPhoto if you go with this approach.

Upvotes: 1

Related Questions