CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user2994144
user2994144

Reputation: 167

SQL Server Stored Procedure select

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

Upvotes: 0

Views: 149

Answers (1)

Zec
Zec

Reputation: 843

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

Upvotes: 1

Related Questions