September 9, 2009 at 6:18 am
Hi all,
I am getting error when running the following script
if object_id('dbo.tbl_photos') is not null
drop table dbo.tbl_photos
go
create table dbo.tbl_photos
(
photoId int identity(1,1) Constraint Pk_PhotoID Primary Key,
CandID int Constraint Fk_Registration_Photos Foreign Key References Tbl_Registration_master(StudentID),
InstID int Constraint Fk_Institution_Photos Foreign Key References Tbl_Institution_Master(Inst_ID),
PhotoName varchar(100),
PhotoPath varchar(200),
Photo image,
CreatedBy int not null,
CreatedOn datetime not null,
ModifiedBy int,
ModifiedOn datetime
)
go
if object_id('dbo.usp_insert_photos') is not null
drop procedure dbo.usp_insert_photos
go
create procedure dbo.usp_insert_photos
(
@p_CandID int,
@p_InstID int,
@p_PhotoName varchar(100),
@p_PhotoPath varchar(200),
@p_Photo image,
@p_CreatedBy int,
@p_Output int output
)
as
begin try
begin tran
if not exists(select 1 from tbl_Registration_Master where StudentID=@p_CandID and Inst_ID=@p_InstID)
begin
rollback tran
set @p_Output=-1
return
end
if exists(select 1 from tbl_Photos where CandID=@p_CandID and InstID=@p_InstID)
begin
rollback tran
set @p_Output=-2
return
end
if exists(select 1 from tbl_Photos where PhotoName=@p_PhotoName and InstID=@p_InstID)
begin
rollback tran
set @p_Output=-3
return
end
if exists(select 1 from tbl_Photos where InstID=@p_InstID and datalength(photo) = datalength(@p_photo) or substring(photo, 1, 8000) = substring(@p_photo, 1, 8000))
begin
rollback tran
set @p_output=-4 --ur try to insert same photo
return
end
insert into Tbl_Photos(CandID,InstID,PhotoName,PhotoPath,Photo,CreatedBy,CreatedOn) values
(@p_CandID,@p_InstID,@p_PhotoName,@p_PhotoPath,@p_Photo,@p_CreatedBy,getdate())
commit tran
set @p_output=1
end try
begin catch
set @p_output=error_number()
return
end catch
go
if object_id('dbo.usp_delete_photos') is not null
drop procedure dbo.usp_delete_photos
go
create procedure dbo.usp_delete_photos
(
@p_PhotoID int,
@p_Output int output
)
as
begin try
begin tran
if not exists(select 1 from tbl_Photos where PhotoID=@p_PhotoId)
begin
rollback tran
set @p_output=-1
return
end
delete from tbl_Photos where PhotoID=@p_PhotoID
commit tran
set @p_Output=1
end try
begin catch
rollback tran
set @p_Output=error_number()
end catch
go
September 9, 2009 at 6:25 am
What is the specific error ?
September 9, 2009 at 7:08 am
As you've posted this in the SQL Server 7,2000 forum, your error may be that BEGIN TRY... isn't supported in either of those versions of SQL Server.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply