Is it possible to place an "IF" condition on the means to the inner joins and other conditions of a where in SQL Server?

Navigation
1

I have a procedure that takes a parameter that can be 0, 1 or 2. When it is 1 or 0, it needs to consider the line where it has "and bol_portfolio = @eh_port", but when it is 2, it can not enter it condition, just bypassing this code. It is possible? I just wanted a simple condition that would cause the bank to ignore this line if the parameter is 2.

    
asked by anonymous 01.06.2016 / 18:50

4 answers

1

You can use a CASE 

and bol_portfolio = CASE WHEN @eh_port = 1 or @eh_port = 0
                         THEN @eh_port
                         ELSE bol_portfolio END

When 2 goes to the else and will not consider its parameter.

    
01.06.2016 / 20:12
1

Simplest way: 

 and (bol_portfolio = @eh_port or @eh_port = 2)

In this case, you do not need to validate it, since when the parameter is two, the comparison with bol_portfolio will be ignored.

Edited

Detail: Oracle sql validates from right to left, that is, if @eh_port is 2, it will not even compare to bol_portifolio

    
01.06.2016 / 19:33
0

Take a look at the Transact-SQL IF command. Example: 

IF LEN(LTRIM(RTRIM(@REV)))=0 BEGIN
     <Seu codigo SQL>
END
    
01.06.2016 / 19:05
0

One of the ways to do this is to create a String dynamically and use Stored Procedure sp_executesql to execute SQL that is in that String.

See an example: 

DECLARE @SQL NVARCHAR(4000)
DECLARE @EH_PORT INT
DECLARE @ID INT

SET @EH_PORT = 2
SET @ID = 15

SET @SQL = 'SELECT * FROM TABELA '

IF @EH_PORT = 2 
BEGIN
    SET @SQL = @SQL + 'WHERE ID = ' + CAST(@ID AS VARCHAR(10))
END

print @SQL

exec sp_executesql @SQL, N'@ID int',
                   @ID

This was a long time ago in SQL Server 2005. I do not know if newer versions have easier ways to do it.

I also used this for cases where the Stored Procedure should run into separate databases (albeit on the same instance). Thus, even the name of the bank could be parameterized. See example: 

'FROM [' + @DATABASE + '].[dbo].[' + @TABELA_CT2 + '] CT2,  [' + @DATABASE + '].[dbo].[CTS020] CTS'
    
01.06.2016 / 19:14
Validate PHP login Function does not work [closed]