Is it possible to put an "IF" condition on media to Internet joins and other Where conditions in SQL Server?

Asked

Viewed 739 times

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 cannot enter this condition, just ignoring this code. Is that possible? I just wanted a simple condition that would make the database ignore this line if the parameter is 2.

sql sql-server

  • You’re using Stored Procedures?

  • Do not forget to mark the question as answered if you found any alternative the was satisfactory

  • 1

    @Jefissu doesn’t even vote on the answer he voted, imagine choose the answer.

4 answers

1

Simplest way:

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

In this case you wouldn’t even need to validate it, because when the parameter is worth two the comparison with the bol_portfolio will be ignored.

Edited

Detail: Oracle sql validates from right to left, that is, if @eh_port is 2, it won’t even compare with bol_portifolio

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 it’s 2 go to Else and will not consider your parameter.

0

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

IF LEN(LTRIM(RTRIM(@REV)))=0 BEGIN
     <Seu codigo SQL>
END

0

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

Take 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 I was doing a long time ago in SQL Server 2005. I don’t know if in newer versions there are easier ways to do.

I also used this for cases where the Stored Procedure should run in separate databases (although in the same instance). Thus, even the name of the bank was possible to parameterize. See example:

'FROM [' + @DATABASE + '].[dbo].[' + @TABELA_CT2 + '] CT2,  [' + @DATABASE + '].[dbo].[CTS020] CTS'

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.