locked
Stored procedure not working(DELETE and EDIT) RRS feed

  • Question

  • USE [WSS_Custom__08_29_2015]
    GO
    /****** Object:  StoredProcedure [dbo].[InsertUpdateDeleteHotels]    Script Date: 09/07/2015 13:04:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    --InsertUpdateDeleteHotels '','',0,'','',1,0
    -- =============================================
    ALTER PROCEDURE [dbo].[InsertUpdateDeleteHotels]
    -- Add the parameters for the stored procedure here
    @hotelName varchar(50)='',
    @hotelDesc varchar(MAX)='',
    @cityId int=0,
    @caretakerName varchar(50)='',
    @caretakerContact varchar(50)='',
    @Id int=0,
    @update int=0,
    @cnt int=0,
    @hotelemail varchar(100) = ''


    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        -- Insert statements for procedure here
        set @cnt=(select COUNT(room_id) Present from room_master where hotel_id=@Id and room_deleted=0)
        if @cnt=0
        
     BEGIN
     
    if @Id=0
     insert into hotels(hotel_name,hotel_desc,city_id,caretaker_name,caretaker_contact,hotel_email_id)
      values(@hotelName,@hotelDesc,@cityId,@caretakerName,@caretakerContact,@hotelemail)
      
    else
    begin
      If @update=0
      update hotels set hotel_deleted=1 where hotel_id=@Id
      else
    update hotels set hotel_name=@hotelName,
      hotel_desc=@hotelDesc,
      city_id=@cityId,
      caretaker_name=@caretakerName,
      caretaker_contact=@caretakerContact,
      hotel_email_id = @hotelemail
     where hotel_id=@Id
      
    end  

    END
    else
    if @update=1
     update hotels set hotel_name=@hotelName,
      hotel_desc=@hotelDesc,
      city_id=@cityId,
      caretaker_name=@caretakerName,
      caretaker_contact=@caretakerContact,
      hotel_email_id = @hotelemail
     where hotel_id=@Id
    select @cnt present
    END

    Vishal

    • Moved by Bob Beauchemin Monday, September 7, 2015 6:56 PM Moving to the appropriate forum for best results
    Monday, September 7, 2015 7:39 AM

All replies

  • the SP looks good, what is the problem? any error message?
    Tuesday, September 8, 2015 6:55 AM
  • Hi Vishal,

    Your issue is not clear here, Please post the error.


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Tuesday, September 8, 2015 7:04 AM
  • I think its better to have individual stored procs to INSERT and DELETE. INSERT PROC could do a UPDATE operation too, but I dont think its good idea to have the DELETE too in the same SP.

    Are you getting any error message here ? 

     

    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, September 8, 2015 7:50 AM