0

My proc looks like this :

Select pl.Id as Id,
       pl.Name as Name,
       f.[Url] as PrimaryImageUrl,
       up.Id as MemberId,
       up.ProfessionalName,
       up.AvatarUrl,
       (
           select c.Id as Id,
                  c.Name as Name,
                  c.ContentImageUrl as ImageUrl,
                  c.Price as Price,
                  c.BPM as BPM,
                  f.Id as 'File.Id',
                  f.Url as 'File.Name',
                  g.Id as 'Genre.Id',
                  g.Name as 'Genre.Name',
                  kt.Id as 'KeyType.Id',
                  kt.Name as 'KeyType.Name',
                  tt.Id as 'TrackType.Id',
                  tt.Name as 'TrackType.Name',
                  TotalCount = count(c.Id) Over ()
           from dbo.Content c
               inner join dbo.PlayListContents pm
                   on c.Id = pm.ContentId
                      and pm.PlaylistId = pl.Id
               inner join dbo.Files f
                   on c.ContentFileId = f.Id
               inner join dbo.Genres g
                   on c.GenreTypeId = g.Id
               inner join dbo.KeyType kt
                   on c.KeyTypeId = kt.Id
               inner join dbo.TrackType tt
                   on tt.Id = c.TrackTypeId
           Where (NOT EXISTS (
               SELECT b.Bpm
               FROM @Bpm AS b
               WHERE b.Bpm IS NOT NULL)
              OR c.Bpm IN (SELECT * FROM @Bpm)
           )
           for json path
       ) AS Content,
       TotalCount = COUNT(1) OVER ()
from dbo.Playlist pl
    inner join dbo.UserProfiles up
        on pl.UserId = up.UserId
    inner join [dbo].[Files] as f
        ON pl.[PrimaryImageId] = f.[Id]
where (pl.Name LIKE '%' + @searchInput + '%')

Using this proc I sometimes get results with Content column at NULL.

why can't I add WHERE Content IS NOT NULL and have my results come back with no rows with NULL at Content column?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Chaz Lee
  • 21
  • 5
  • Does this answer your question? [Possible to store value of one select column and use it for the next one?](https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one) Use `CROSS APPLY (VALUES` – Charlieface Jul 21 '21 at 00:17
  • By the way: `count(c.Id) Over ()` is the same as `count(1) Over ()` if `c.Id` is non-null. `Where (NOT EXISTS (...) OR c.Bpm IN (...)` looks inefficient, also `NOT EXISTS (SELECT b.Bpm` may as well be `NOT EXISTS (SELECT 1`. – Charlieface Jul 21 '21 at 00:20
  • @Charlieface first post does not answer my question. I just need to be able to get a result set without NULL in contents...... I know Cross apply does that but I don't know how to implement it in this context....... – Chaz Lee Jul 21 '21 at 18:25
  • So you take everything the whole `(SELECT ... FOR JSON...) AS Content` and place it in an apply like this `CROSS APPLY (SELECT (SELECT ... FOR JSON ... ) AS Content) v` then you can filter after that `WHERE v.Content IS NOT NULL` – Charlieface Jul 21 '21 at 20:30

0 Answers0