Database

adnanrafiq wrote:

first i m getting these two errors

adnanrafiq wrote:

when i try to call the from my DAL layer i got exception

select id, measurement_01, measurement_02, measurement_03,
      case
         when measurement_01 >= measurement_02 and measurement_01 >= measurement_03
            then measurement_01
         when measurement_02 >= measurement_03
            then measurement_02
         else  measurement_03
      end as maximum
   from #temp_ajh
   order by id

SELECT
    m.ID,
    m.measurement_01,
    m.measurement_02,
    m.measurement_03,
    MAX (FlatTable.measurement_01)
FROM
    MeasurementTable m
INNER JOIN
    (SELECT ID, measurement_01 
     FROM MeasurementTable
    UNION
     SELECT ID, measurement_02 
     FROM MeasurementTable
    UNION
     SELECT ID, measurement_03 
     FROM MeasurementTable) AS FlatTable
    ON (m.ID = FlatTable.ID)
GROUP BY
    m.ID,
    m.measurement_01,
    m.measurement_02,
    m.measurement_03

create procedure MyRecordInsert
@Prefix varchar(3),
@OtherData varchar(20)
as begin
   set nocount on
   declare @PrimaryKey varchar(15), @MaxKey int
   Begin tran

   --Create first part of primary key (prefix + date).
   set @PrimaryKey = @Prefix + Convert(varchar(8), GetDate(), 112)

   --Find the higher number that has been allocated today.
   --Use locking to prevent anyone else inserting until we have finished
   --our transaction.
   select @MaxKey = Convert(Int, Substring(Max(PrimaryKey), 12, 4))
      from MyTable with (tablock, holdlock)
      where PrimaryKey like @PrimaryKey + '%'

   --Increment the highest number, and then append to key.
   set @MaxKey = IsNull(@MaxKey, 0) + 1
   set @PrimaryKey = PrimaryKey +
         Right('0000' + Convert(varchar(4), @MaxKey), 4)

   --Insert record into the database.
   insert into MyTable (PrimaryKey, OtherData)
      values (@PrimaryKey, @OtherData

   Commit tran
end

INSERT INTO myTable(ID, Item1, Item2)
SELECT 'sa0' + CONVERT(DATETIME, GETDATE(), 112) + COUNT(*),
@Item1, @Item2
FROM myTable

UPDATE EQUIPMENT SET REF_ID = ‘B’
   WHERE EQUIPMENTOID IN (
      SELECT CONTRACT_EQUIP_PERIOD.EQUIPMENTOID
      FROM CONTRACT
      LEFT JOIN CONTRACT_PERIOD
      ON CONTRACT.CONTRACT_ID = CONTRACT_PERIOD.CONTRACT_ID
      LEFT JOIN CONTRACT_EQUIP_PERIOD
      ON CONTRACT_PERIOD.CONTRACT_PERIODOID = CONTRACT_EQUIP_PERIOD.CONTRACT_PERIODOID
      WHERE CONTRACT.CONTRACT_ID = 1234
   )
   AND EQUIPMENT.REF_ID= 'A' ;