quote:

---

Originally posted by visakh16

not sure about error message. but your posted sp has couple of errors. it should be

create proc sp_GetMovieInfo
@Title varchar(100) = null,
@GenreID int = null,
@ReleaseYear int= null

if exists (select * from Movies where Title like '%'+@Title+ '%')
begin
select Title,Genre.Genre,Rating.Rating,ReleaseYear,TotalInStock
from Movies
inner join Genre
on Movies.GenreID = Genre.GenreID
inner join Rating
on Movies.RatingID = Rating.RatingID

where Title like '%'+@Title+ '%'
return 0
end

Also learn to use ANSI joins as other syntax wont be supported in future versions of SQL Server

---

quote:

---

Originally posted by visakh16

ok. between whats the purpose of @ReleaseYear & @GenreID? i cant see where they are used. If not needed, remove them.

---

create proc sp_GetMovieInfo
@Title varchar(100) = null,
@GenreID int = null,
@ReleaseYear int= null

if exists (select * from Movies where Title like '%'+@Title+ '%')
begin
select Title,Genre.Genre,Rating.Rating,ReleaseYear,TotalInStock
from Movies
inner join Genre
on Movies.GenreID = Genre.GenreID
inner join Rating
on Movies.RatingID = Rating.RatingID

where (Title like '%'+@Title+ '%' or @Title is null)
and (Movies.GenreID = @GenreID or @GenreID is null)
and (Movies.ReleaseYear = @ReleaseYear or @ReleaseYear is null)
return 0
end

quote:

---

Originally posted by visakh16

there's no need of this if statements you need only the below

create proc sp_GetMovieInfo
@Title varchar(100) = null,
@GenreID int = null,
@ReleaseYear int= null

if exists (select * from Movies where Title like '%'+@Title+ '%')
begin
select Title,Genre.Genre,Rating.Rating,ReleaseYear,TotalInStock
from Movies
inner join Genre
on Movies.GenreID = Genre.GenreID
inner join Rating
on Movies.RatingID = Rating.RatingID

where (Title like '%'+@Title+ '%' or @Title is null)
and (Movies.GenreID = @GenreID or @GenreID is null)
and (Movies.ReleaseYear = @ReleaseYear or @ReleaseYear is null)
return 0
end

---

create proc sp_GetMovieInfo
@Title varchar(100) = null,
@GenreID int = null,
@ReleaseYear int= null
begin
select Title,Genre.Genre,Rating.Rating,ReleaseYear,TotalInStock
from Movies
inner join Genre
on Movies.GenreID = Genre.GenreID
inner join Rating
on Movies.RatingID = Rating.RatingID

where (Title like '%'+@Title+ '%' or @Title is null)
and (Movies.GenreID = @GenreID or @GenreID is null)
and (Movies.ReleaseYear = @ReleaseYear or @ReleaseYear is null)
return 0
end

quote:

---

Originally posted by visakh16

ah...i forgot to remove a bit. try this now and see

create proc sp_GetMovieInfo
@Title varchar(100) = null,
@GenreID int = null,
@ReleaseYear int= null
begin
select Title,Genre.Genre,Rating.Rating,ReleaseYear,TotalInStock
from Movies
inner join Genre
on Movies.GenreID = Genre.GenreID
inner join Rating
on Movies.RatingID = Rating.RatingID

where (Title like '%'+@Title+ '%' or @Title is null)
and (Movies.GenreID = @GenreID or @GenreID is null)
and (Movies.ReleaseYear = @ReleaseYear or @ReleaseYear is null)
return 0
end

---

create proc [dbo].[sp_UpdateRentalRecord]
/* input parameters */
@MemberID int,
@VideoID int


as
begin

-- validate video id
if exists (select 1 from Videos where VideoID = @VideoID)
	begin

	
		--impend a late fee charge if video is returned after due date
		--the late fee is $1.99 * the number of days after the due date
		update Customers
		set Balance = Balance + (1.99*DATEDIFF(dd,rr.DateDue,GETDATE()))
		FROM Customers c
		INNER JOIN RentalRecord rr
		ON rr.MemberID = c.MemberID 
		and rr.DateReturned is null
		where c.MemberID = @MemberID
		AND DateDue < GETDATE()

		print 'Video is overdue'

		
		--add the return date of the video
		update RentalRecord
		set DateReturned = GETDATE()
		where VideoID = @VideoID



		update Movies
		set TotalInStock = TotalInStock + 1
		from Movies, RentalRecord, Videos, Customers
		where RentalRecord.VideoID = @VideoID and RentalRecord.MemberID = @MemberID and RentalRecord.VideoID = Videos.VideoID and Videos.MovieID = Movies.MovieID



		update Videos
		set Videos.VideoStatusID = 1
		from Videos, RentalRecord, Movies, Customers
		where RentalRecord.VideoID = @VideoID and RentalRecord.MemberID = @MemberID and RentalRecord.VideoID = Videos.VideoID and Videos.MovieID = Movies.MovieID


	end

else
	begin
		select 'No video id exists'
	end


end

quote:

---

Originally posted by visakh16

yup you need another if as datedue is from another table

---

create proc [dbo].[sp_AddRentalRecord]
--input parameters 
(
@MemberID int,
@VideoID int
) 
as
begin

if exists (select 1 from RentalRecord where VideoID = @VideoID and DateDue is null)
	begin
		select 'Video id is already rented'
		return
	end


begin transaction

	declare @Total money
	set @Total = (select Price*1.05 from Videos where VideoID = @VideoID)


	insert into RentalRecord 
	values (@MemberID,@VideoID,ROUND(@Total,2),GETDATE(), DATEADD(dd,5,GETDATE()),null)

	-- transaction error handling; if error number is not zero then there's a failure, rollback transaction, and print message
	if @@error <> 0 
		begin
			rollback transaction
			select 'Insert Failed'
			return
		end

	else
		begin
			select 'Rental Record Added Successfully'
		end 

commit transaction

end

create proc [dbo].[sp_AddRentalRecord]
--input parameters 
(
@MemberID int,
@VideoID int
) 
as
begin

if exists (select 1 from RentalRecord where VideoID = @VideoID and DateDue is null)
	begin
		select 'Video id is already rented'
		return
	end

else
begin
begin transaction

	declare @Total money
	set @Total = (select Price*1.05 from Videos where VideoID = @VideoID)


	insert into RentalRecord 
	values (@MemberID,@VideoID,ROUND(@Total,2),GETDATE(), DATEADD(dd,5,GETDATE()),null)

	-- transaction error handling; if error number is not zero then there's a failure, rollback transaction, and print message
	if @@error <> 0 
		begin
			rollback transaction
			select 'Insert Failed'
			return
		end

	else
		begin
			select 'Rental Record Added Successfully'
		end 

commit transaction
end
end

quote:

---

Originally posted by visakh16

put it in else

create proc [dbo].[sp_AddRentalRecord]
--input parameters 
(
@MemberID int,
@VideoID int
) 
as
begin

if exists (select 1 from RentalRecord where VideoID = @VideoID and DateDue is null)
	begin
		select 'Video id is already rented'
		return
	end

else
begin
begin transaction

	declare @Total money
	set @Total = (select Price*1.05 from Videos where VideoID = @VideoID)


	insert into RentalRecord 
	values (@MemberID,@VideoID,ROUND(@Total,2),GETDATE(), DATEADD(dd,5,GETDATE()),null)

	-- transaction error handling; if error number is not zero then there's a failure, rollback transaction, and print message
	if @@error <> 0 
		begin
			rollback transaction
			select 'Insert Failed'
			return
		end

	else
		begin
			select 'Rental Record Added Successfully'
		end 

commit transaction
end
end

---

create trigger trg_i_customer
on Customers
for insert
as
begin



if exists(select 1 from inserted i
          inner join Customers c
          on  c.Phone = i.Phone)
	begin
        if @@TRANCOUNT > 0
                rollback transaction
	Raiseerror 'Customer with phone number already exists',10,1
		

	end

end

quote:

---

Originally posted by visakh16

it should be like below
dont use variables to store values from inserted. inserted may not always contain a single row.

create trigger trg_i_customer
on Customers
for insert
as
begin



if exists(select 1 from inserted i
          inner join Customers c
          on  c.Phone = i.Phone)
	begin
        if @@TRANCOUNT > 0
                rollback transaction
	Raiseerror 'Customer with phone number already exists',10,1
		

	end

end

---

quote:

---

Originally posted by visakh16

it should be like below
dont use variables to store values from inserted. inserted may not always contain a single row.

create trigger trg_i_customer
on Customers
for insert
as
begin



if exists(select 1 from inserted i
          inner join Customers c
          on  c.Phone = i.Phone)
	begin
        if @@TRANCOUNT > 0
                rollback transaction
	Raiseerror 'Customer with phone number already exists',10,1
		

	end

end

---