How to use an And-Or as a variable

Question

I'm actually trying to get the data from a table but I wanted that "And/Or" changes based on the table. I'm using SQL Server.

I'm into something like this:

DECLARE @TURNOS TABLE (ID INT IDENTITY(1, 1),
                       INITURNO INT, 
                       ENDTURNO INT, 
                       ANDOR VARCHAR(3)
                      );

INSERT INTO @TURNOS 
VALUES (23, 7, 'OR'), (7, 15, 'AND'), (15, 23, 'AND')

And I'm trying to do something like this:

WHILE (@count <= (SELECT COUNT(ID) FROM @TURNOS))
BEGIN
    SET @initurno = SELECT INITURNO FROM @TURNOS WHERE ID = @count
    SET @endturno = SELECT ENDTURNO FROM @TURNOS WHERE ID = @count
    SET @andor = SELECT ANDOR FROM @TURNOS WHERE ID = @count

    INSERT INTO @AnotherTable
        SELECT * 
        FROM dbo.TableA 
        WHERE DATES BETWEEN DATEPART(hh, DATES) >= @initurno @andor DATEPART(hh, DATES) < @enturno
END

Is there any way that I can use a variable And/Or like I tried with @andor?

Thanks in advance :)

score 1 · Answer 1 · answered Nov 30 '17 at 18:32

You are looking for Dynamic SQL. You can try like this;

declare @query nvarchar(max)
while(@count <= (select count(ID) from @TURNOS))
begin
    SET @num1 = select INITURNO from @TURNOS  where ID = @count
    SET @num2 = select ENDTURNO from @TURNOS  where ID = @count
    SET @andor = select ANDOR  from @TURNOS where ID = @count

    set @query = 'insert into @AnotherTable
    select * from dbo.TableA where DATES between Datepart(hh,DATES)>= '+cast(@INITURNO as nvarchar(5))+' '+@andor+' Datepart(hh,DATES)< '+cast(@ENDTURNO as nvarchar(5))+''
    EXECUTE sp_executesql @query
end

score 1 · Accepted Answer · answered Nov 30 '17 at 18:37

You could use dynamic SQL if you really need the operator to be a variable. But if you just need the query to be conditional, you just include both conditions and switch them based on the variable.

insert into @AnotherTable
select * from dbo.TableA 
where 
/*First condition */
(
@andor = 'AND'
AND DATES between Datepart(hh,DATES)>= @INITURNO 
AND Datepart(hh,DATES)< @ENDTURNO
)

OR
/* Second condition */
(
@andor = 'OR'
AND (
     DATES between Datepart(hh,DATES)>= @INITURNO 
     OR Datepart(hh,DATES)< @ENDTURNO
     )
)

Oh, and I didn't even notice the use of the BETWEEN operator, which GSazheniuk noted (and corrected). — Dave Chapman, Nov 30 '17 at 19:22

score 0 · Answer 3 · answered Nov 30 '17 at 18:49

I am not a big fan of Dynamic SQL, but even if I was, I believe this is not right syntax for BETWEEN keyword?

So here is what my solution would look like, and I will not copy the whole query, but only select part of it:

    select * from dbo.TableA 
    where (@andor = 'AND' AND Datepart(hh,DATES) BETWEEN @initurno AND @enturno) 
    OR (@andor = 'OR' AND (Datepart(hh,DATES) >= @initurno OR Datepart(hh,DATES) <= @enturno))

Please note, that I used <= with @enturno, because that's how BETWEEN works, it is right-limit inclusive. If you don't want to include @enturno than you would need to rewrite upper query to use >= and < for both cases:

    select * from dbo.TableA 
    where (@andor = 'AND' AND Datepart(hh,DATES) >= @initurno AND Datepart(hh,DATES) < @enturno) 
    OR (@andor = 'OR' AND (Datepart(hh,DATES) >= @initurno OR Datepart(hh,DATES) < @enturno))

How to use an And-Or as a variable

3 Answers3