Showing posts from July, 2015

Copy SQL Table With Child Table Relaionships

dbo.RoomOccupancyRate has many dbo.OccupancyChildRate records.

How to copy them?

Following stored procedure takes care of it. It even has the option to copy only the active items.

ALTER PROCEDURE [dbo].[sp_CopyHotelContractRoomOccupancyRate]@NewHotelContractIDint,@SelectedHotelContractIDint,@UserName nvarchar(20),@ActiveItemsOnly bit,@OldDateBandIdint,@NewDateBandIdint,@OldRoomOccupancyIdint,@NewRoomOccupancyIdint AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON; IF (EXISTS(SELECT TOP 1 cr.[RoomOccupancyRateID] FROM [dbo].[RoomOccupancyRate] cr INNER JOIN [dbo].[HotelRoomOccupancy] rc ON cr.[RoomOccupancyID]= rc.[RoomOccupancyID] WHERE rc.[RoomOccupancyID]=@OldRoomOccupancyId)) BEGIN PRINT '----[dbo].[RoomOccupancyRate] table should be copied'

Conditional Filtering in SQL

Assume I need to select a set of data based on the following criteria.

If the @Active-Items-Only flag is set, then select only the active itemsotherwise select all active and non-active items.DECLARE@ActiveOnly bit =1;-- select only activeSELECT[HotelContractID],[RoomTypeID],[TravelStart],[TravelEnd],[ReleaseTypeID],[ReleaseValue],[Active]FROM[dbo].[RoomAllocation]WHERE[HotelContractID]=124AND(CASE-- if active only selected, -- check only active = 1 rows in the table and return 1WHEN@ActiveOnly =1AND(Active =1)THEN1-- if active and inactive both selected, -- check for both active = 1 and active = 0 rows in the table and return 1WHEN<