SQL SERVER Select multiple fields from If Exists

Question

I have to do an SQL Server Statement that have to return an empty row when is null, and data otherwhise.

I am trying to do a Select from (if exisits) but have an error on parent table.

I Simplify it. But the meaning, is to retrieve a couple of fields when condition is null and other fields when it is not null.

It Works fine when I do not clouse it in another select.... I need to retrieve it as a table to do an inner Join with other clouse.

How can i resolved it?

Here is my code..

select * from
 (
       if exists(select isnull(SECTOR_ID_DESTINO_BAD,-1) 
   from workflow_Compras_detalle w
   where w.id=2)
   begin  
       select null as Sector,null as sector_id_origen
   end
   else
   begin
    select top 1 isnull(ws.sector,'') sector,  wd.sector_id_origen
    from workflow_Compras_detalle wd 
    where orden < 10
       end 
 )Table

Isn't that what a left outer join would do? – Bob Vale Jun 28 '17 at 18:20 — Bob Vale, Jun 28 '17 at 18:20

score 1 · Accepted Answer · answered Jun 28 '17 at 18:22

you should try to insert the data into a temporary table or Table Variable, then get the data from that table, here is an example with a Table Variable, if you need something more persistent you may use a #Temp Table, i recommend you take a look to this: difference between var table and #Temp Table

    DECLARE @VAR_TABLE AS TABLE(
Sector varchar(25),
sector_id_origen int
)

if exists(select isnull(SECTOR_ID_DESTINO_BAD,-1) 
            from workflow_Compras_detalle w
            where w.id=2)
    begin       
                INSERT INTO @VAR_TABLE
                Select null as Sector,null as sector_id_origen 
    End
Else
    begin

        INSERT INTO @VAR_TABLE
        select top 1 isnull(ws.sector,'') sector,  wd.sector_id_origen
        from workflow_Compras_detalle wd 
        where orden <   10
End 

SELECT * FROM @VAR_TABLE

It would be interesting to add CTEs vs Table variables vs Temporary Tables to the [comparison](http://www.dotnettricks.com/learn/sqlserver/difference-between-cte-and-temp-table-and-table-variable) — Pastor Cortes, Jun 28 '17 at 18:44

SQL SERVER Select multiple fields from If Exists

1 Answers1