September 1, 2002 at 3:55 pm
I have a weird problem with one procedure. At the beginning of the procedure I check if a few of the parameters is aldready used. If they are, I create an error message string. If there are no errors, this means the error message string is empty, I insert the values into the database and return the Id of the new record. Well, at least this is what I intented to do.
The problem I'm having is this: The procedure always returns the error message, even if the insert is successful. Check out the following code. I use non-existing values, this means the erro message should be empty and it should insert them and return the identity. The procedure inserts the values successfuly but returns "error1,error2,error3" as output, as if all of the values are already exists in the database. This part confuses to me, if the error message is not empty why does the insert part? Or if the error message is empty how can it return a non-empty error message? I wasn't able to understand the reason and I'm hoping someone could point me what I'm missing.
This is the code:
create procedure ....
@....
as
set nocount on
declare @err_msg varchar(3000)
set @err_msg=''
if exists(select ID1 from Table1 where ...)
begin
set @err_msg=@err_msg+'error 1,'
end
if exists(select ID2 from Table1 where ...)
begin
set @err_msg=@err_msg+'error 2,'
end
if exists(select ID3 from Table1 where ...)
begin
set @err_msg=@err_msg+'error 1'
end
if @err_msg=''
begin
insert into....
declare @newid int
select @newid=@@identity
select @newid
end
else
begin
select @err_msg
end
September 2, 2002 at 12:25 am
Can you post the rest of the code (the things you left out).
From your previous post, I gather that there must be an error in the first part, with the 'IF exists(...)' pieces.
September 2, 2002 at 2:33 am
Sure.
if exists(select name from sysobjects where name='proc_AdvertiserSignup' and type='P')
drop procedure proc_AdvertiserSignup
go
create procedure proc_AdvertiserSignup
@login varchar(100),@password varchar(50),@name varchar(200),@email varchar(200)
as
set nocount on
declare @err_msg varchar(3000)
set @err_msg=''
if exists(select ID from dbo.[Advertisers] where LoginName=@login)
begin
set @err_msg=@err_msg+'This Login Name is being used by another advertiser. Please go back and select another one.<br>'
end
if exists(select ID from dbo.[Advertisers] where FullName=@name)
begin
set @err_msg=@err_msg+'This Name is being used by another advertiser. Please go back and select another one.<br>'
end
if exists(select ID from dbo.[Advertisers] where Email=@email)
begin
set @err_msg=@err_msg+'This E-mail address is being used by another advertiser. Please go back and select another one.<br>'
end
if exists(select ID from dbo.[Advertisers] where Phone=@phone)
begin
set @err_msg=@err_msg+'This Phone Number is being used by another advertiser. Please go back and select another one.<br>'
end
--add
if @err_msg=''
begin
insert into dbo.[Advertisers]
(LoginName,LoginPassword,FullName,Email)
values
(@login,@password,@name,@email)
--ad ID
declare @id int
select @id=@@identity
select @id
end
--error
else
begin
select @err_msg
end
go
September 2, 2002 at 5:06 am
Your procedure works fine for as far as I can tell. No problems inserting or recognising existing records.
(I only removed the reference to @phone, since it is not in the parameters of the SP.)
Are you sure there is no other procedure/trigger/... that could insert the values before (or after) this proc is called? Maybe you should try using Profiler to find the culprit.
September 3, 2002 at 3:54 am
Try this variation, should work nicely without the need for the extra varibale.
Plus if you get a select then it has to be in the exists statement where that value is returned.
You should also consider using raiserror instead of select and have you app trap the error for return.
Select means you have to actually look for an error as opposed to the data.
create procedure proc_AdvertiserSignup
@login varchar(100),
@password varchar(50),
@name varchar(200),
@email varchar(200)
as
set nocount on
if exists(select ID from dbo.[Advertisers] where LoginName=@login)
begin
SELECT 'This Login Name: ' + @login + ' is being used by another advertiser. Please go back and select another one.<br>'
RETURN --Return will end execution here, this will stop further checks as problem already exists.
end
if exists(select ID from dbo.[Advertisers] where FullName=@name)
begin
SELECT 'This Name: ' + @name + ' is being used by another advertiser. Please go back and select another one.<br>'
RETURN
end
if exists(select ID from dbo.[Advertisers] where Email=@email)
begin
SELECT 'This E-mail: ' + @email + ' address is being used by another advertiser. Please go back and select another one.<br>'
RETURN
end
--I don't see an @phone in your inputs???????? This could be tossing a general error.
if exists(select ID from dbo.[Advertisers] where Phone=@phone)
begin
SELECT 'This Phone Number: ' + @login + ' is being used by another advertiser. Please go back and select another one.<br>'
RETURN
end
insert into dbo.[Advertisers]
(LoginName,LoginPassword,FullName,Email)
values
(@login,@password,@name,@email)
--ad ID
select @@identity --Output will be same without extra variable.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply