SQL Server Stored Procedure select

Question

I can't figure out how to set the values if Power, ChangeRate, and Type are null. I still want it to return rest of the values even if those are not in the database.

ALTER PROCEDURE dbo.up_getTestData
(@Chip int)
AS
   DECLARE @Type char(2), @ChangeRate real, @Power bit

   SELECT @Type = Type  
   FROM Spectrum 
   WHERE ChipID = @Chip

   IF(@Type is null)
      //SET @Type = 'NA'
      //or Go to the bottom statement ??
      SELECT @ChangeRate = ChangeRate 
      FROM TempCycle 
      WHERE ChipID = @Chip

   IF(@ChangeRate is null)
      //Go to the bottom statement ??
      //SET  @ChangeRate = '0.0'
      SELECT @Power = Power 
      FROM TempCycle 
      WHERE ChipID = @Chip

   IF(@Power is null)
        // I want it to still go the bottom statement. I put just return 
        // but that's not right since it wouldn't return anything than??
        // or I did SET @Power = 'false'
   ELSE
      SELECT 
          Chips.Chip_ID AS ChipID, 
          Chip_Number, Test_Module.ModuleTypeID, 
          Test_Module.PID, Test_Module.Component1, Test_Module.Component2, 
          Test_Module.Parameter1, Test_Module.Parameter2, Test_Module.Parameter3, 
          Test_Module.Parameter4, Test_Module.Parameter5, 
          Test_Module.Parameter6, Spectrum.Type, TempCycle.Power, TempCycle.ChangeRate 
      FROM 
          Chips 
      INNER JOIN 
          Test_Module ON Chips.Chip_Number = Test_Module.Module_Name 
      INNER JOIN 
          Spectrum ON Test_Module.ModuleSpec_TestID =  Spectrum.TestID 
      INNER JOIN 
          TempCycle ON Test_Module.ModuleTemp_TestID = TempCycle.TestID 
      WHERE 
          Chip_ID = @Chip
RETURN

I don't understand what you are trying to do. What kind of output are you trying to get? What do you want to do with the @Type, @ChangeRate, and @Power variables? — Robert Harris
– Robert Harris, Commented Feb 13, 2014 at 2:48
Could you post the expected out ? what exactly you want to do ? — Krishnraj Rana
– Krishnraj Rana, Commented Feb 13, 2014 at 2:54
@RobertHarris I'm displaying the data on aspx page so if the value is null, I just want to display it as being empty otherwise display the values from database. — user2994144
– user2994144, Commented Feb 13, 2014 at 2:56
@user2994144, my answer below assumes you want to show 'NA', 'false' and '0.0' instead of NULL for Type, Power and ChangeRate respectively. If you'd rather they simply be empty, replace those in my script with empty strings (or double single-quotes). — Zec
– Zec, Commented Feb 13, 2014 at 3:00
@Zec I tried both coalesce and ISNULL but it's still not returning the data I want. I was hoping it would :) — user2994144
– user2994144, Commented Feb 13, 2014 at 3:11

Zec · Accepted Answer · 2014-02-13 03:12:23Z

1

I think all you need is coalesce (or ISNULL):

SELECT 
  Chips.Chip_ID AS ChipID, 
  Chip_Number, 
  Test_Module.ModuleTypeID, 
  Test_Module.PID,     
  Test_Module.Component1, 
  Test_Module.Component2, 
  Test_Module.Parameter1, 
  Test_Module.Parameter2, 
  Test_Module.Parameter3, 
  Test_Module.Parameter4, 
  Test_Module.Parameter5, 
  Test_Module.Parameter6, 
  coalesce(Spectrum.Type, 'NA') as Type, 
  coalesce(TempCycle.Power, 'false') as Power, 
  coalesce(TempCycle.ChangeRate, '0.0') as ChangeRate 
FROM 
  Chips INNER JOIN Test_Module 
    ON Chips.Chip_Number = Test_Module.Module_Name 
  LEFT OUTER JOIN Spectrum 
    ON Test_Module.ModuleSpec_TestID =  Spectrum.TestID 
  LEFT OUTER JOIN TempCycle 
    ON Test_Module.ModuleTemp_TestID = TempCycle.TestID 
WHERE 
  Chip_ID = @Chip

edited Feb 13, 2014 at 3:12

answered Feb 13, 2014 at 2:48

Zec

8534 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Stored Procedure select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related