CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Tomas Kanok
Tomas Kanok

Reputation: 135

SQL procedure select

I would like to write a procedure to database which will return select all data from database Tournaments plus bool parameter. If user is registered, it will return true.

Call:

exec TournamentsWithLoggedUser @user = 'asd123'

Procedure:

CREATE PROCEDURE [dbo].[TournamentsWithLoggedUser]
    @user  nvarchar(128)
AS
    SELECT 
        t.Id, t.Info, BIT(r.Id) 
    FROM  
        Tournaments AS t
    LEFT JOIN 
        Registrations AS r ON  t.Id = r.TournamentId
    WHERE
        r.UserId IS NULL OR r.UserId = @user

    RETURN

it mean something like

1, 'some info', true //1
2, 'some info2', false //2

Upvotes: 1

Views: 83

Answers (4)

Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 281

SELECT t.Id, t.Info, 
    -- this works in SQL Server
    CAST ((CASE WHEN r.UserId IS NOT NULL THEN 1 ELSE 0 END) AS BIT) AS IsRegistered 
FROM Tournaments as t
   LEFT JOIN Registrations as r ON t.Id = r.TournamentId
where (r.UserId = '' OR r.UserId = @user)

-- i think this one is help for you...

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

You are looking for this query

SELECT t.id, 
       t.info, 
       Cast (CASE 
               WHEN r.userid IS NOT NULL THEN 1 
               ELSE 0 
             END AS BIT) AS IsRegistered 
FROM   tournaments AS t 
       LEFT JOIN registrations AS r 
              ON t.id = r.tournamentid 
                 AND r.userid = @user

Upvotes: 0

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

You should clarify what SQL language you are actually using, but an answer can be provided anyway:

CREATE PROCEDURE [dbo].[TournamentsWithLoggedUser]
    @user nvarchar(128)
AS
BEGIN
    SELECT t.Id, t.Info, 
        -- this works in SQL Server
        CAST ((CASE WHEN r.UserId IS NOT NULL THEN 1 ELSE 0 END) AS BIT) AS IsRegistered 
    FROM Tournaments as t
       LEFT JOIN Registrations as r ON t.Id = r.TournamentId
    where r.UserId IS NULL OR r.UserId = @user

    -- this should not be required
    RETURN

END

However, there is a problem with the logic:

@user is not nullable, so your procedure gives the impression that it looks data for a single user. However, your OR operator allows to select all records from unregistered users united with the record for the particular provided user (if exists).

Upvotes: 0

Munir
Munir

Reputation: 3612

Why not just use a case statement?

CASE WHEN r.Id IS NULL THEN 0 ELSE 1 END

Change the 0 and 1 to whatever you want for false and true.

Upvotes: 1

Related Questions