Database

SELECT	PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID, 
	        PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
        (SELECT COUNT(*) FROM PD_Articles WHERE  (PD_Users.UserName = UserName)) AS ArticleCount,
        (SELECT COUNT(*) FROM PD_News WHERE  (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
        (SELECT COUNT(*) FROM PD_News WHERE  (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
        (SELECT COUNT(*) FROM  PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
        CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
		ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
			
	FROM	aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
                      PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
    WHERE (@LastName = PD_Users.LastName  OR (@LastName = '' AND 1=1))    
          AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
          AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
          AND (@UserStatus = (CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))                
     ORDER BY PD_Users.FirstName DESC     

INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 0, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 0
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 1, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 1
INSERT INTO prices (created_user, price_level_id, item_hour, item_category, item_code, item_description, item_price) SELECT @created_user, @price_level_identity, 2, item_category, item_code, item_description, item_price FROM prices WHERE price_level_id = @price_level_id AND item_hour = 2

UPDATE prices SET item_guid = ONE_GUID_FOR-ALL where item_code = SAME_ITEM_CODE and price_level_id = WHAT_VER_ID_i_PASS

ALTER procedure Villas_proc_Insert_EnquiryBookingDetail
(@BId int ,@VId int,@FromDate datetime,@Todate datetime)
As
BEGIN TRAN AAA123
WHILE (@FromDate < @Todate)
BEGIN
insert into Villas_EnquiryBookingDetail(BId,VId,BookedDate)
values(@BId,@VId,@FromDate)
SET @FromDate = dateadd(day,1,@FromDate)
END
update Villas_Enquiry set status= 'Confirm' where BId= @BId
if @@error =0
BEGIN
COMMIT TRAN AAA123
END 
ELSE
BEGIN
ROLLBACK TRAN AAA123
END<pre>

exe Villas_proc_Insert_EnquiryBookingDetail 5,2,'03/02/2010','03/07/2010';
exe Villas_proc_Insert_EnquiryBookingDetail 5,2,'03/07/2010','03/10/2010'