need to limit data inserting

Arun Eddy

8y847

this is my procedure i want to limit the data inserting on give time like i want to insert only 12 member in a give time if the user is reach the number of value which i want to get is show the message else insert the value.

my procedure is doing the first part its tack the number of value which i want if the number of value is reach the number 12 than its show me the message but when i m change the time its also show me the same magess plz check what i m doing worng and if u have any solution about this plz provide me

create procedure SP_insert_test

@name varchar(20),@emailid varchar(20),

@trainer_name varchar(50),@training_date varchar(50),@training_time varchar(50),@gymname varchar(50)

,@success int out

begin

if(select count(id) from Add_Booking_Fitness_Training where training_time=@training_time)>11

print N'Number of Booking Is Complete for this time and date plz book other time';

else

insert into Add_Booking_Fitness_Training(memeber_name,member_emailid,trainer_name,training_date,training_time,gymname)

values(@name,@emailid,@trainer_name,@training_date,@training_time,@gymname)

SELECT SCOPE_IDENTITY()

set @success=1;

end

begin

set @success=0;

end

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Patel8y ago
Accepted answer
Hi,

try this
create procedure SP_insert_test
@name varchar(20),@emailid varchar(20),
@trainer_name varchar(50),@training_date varchar(50),@training_time varchar(50),@gymname varchar(50)
,@success int out
as
begin
if(select count(id) from Add_Booking_Fitness_Training where training_time=@training_time)>11
print N'Number of Booking Is Complete for this time and date plz book other time';
RETURN;
else
insert into Add_Booking_Fitness_Training(memeber_name,member_emailid,trainer_name,training_date,training_time,gymname)
values(@name,@emailid,@trainer_name,@training_date,@training_time,@gymname)
SELECT SCOPE_IDENTITY()
set @success=1;
end
begin
set @success=0;
end
Shahriar Nikkhah8y ago
You can use TOP 12 in your select statement
or COUNT your destination before insert
lots of ways you can do that
Arun Eddy8y ago
Yes i do the same things but i need insert only 12 number of recorde into table for the given time and date like i have to insert data for the time of 6:00am and date is 9/5/2018 so in that time and date only insert 12 number of data into table when number of insert reach 12 its give me a message that number of inserting data is full plz try other time
Shahriar Nikkhah8y ago
The problem is that your SP is not readable
i am sure that you know what to do just organize your code more and your can easily see the issues

try this
CREATE PROCEDURE SP_insert_test
        @name           VarChar(20)
    ,   @emailid        VarChar(20)
    ,   @trainer_name   VarChar(50)
    ,   @training_date  VarChar(50)
    ,   @training_time  VarChar(50)
    ,   @gymname        VarChar(50)
    ,   @success        Int OUT
AS
BEGIN
    SET @success = 0 ;

    IF( SELECT COUNT(id)
        FROM Add_Booking_Fitness_Training
        WHERE training_time=@training_time) >11
        PRINT N'Number of Booking Is Complete for this time and date plz book other time';
    ELSE
        BEGIN
            INSERT INTO Add_Booking_Fitness_Training
                    (       memeber_name
                        ,   member_emailid
                        ,   trainer_name
                        ,   training_date
                        ,   training_time
                        ,   gymname
                    )
            VALUES  (       @name
                        ,   @emailid
                        ,   @trainer_name
                        ,   @training_date
                        ,   @training_time
                        ,   @gymname
                    )
            --SELECT SCOPE_IDENTITY()
            SET @success=1;
        END
END