<quote>
As much as I want to help it sounds more like a demand than a request.
But since this is your first post I am assuming you are not yet aware
of the 'Netiquette' and will catch up.
</quote>
No,it was not at all the demand.
I have read on forums that if i mention that i have created my procedure in sql 2005 then they write the code for anyother database or suggest which is irrelevant to my post...
Anyways,thanxs for welcoming me..Ofcourse thanxs for ur all answers.
But,pls do help me for my this post about the stored procedure.The other posts are answered thanxs...
I m new to sql and also to ur forum so help me...
ya,i want to do some checks for any existing data on either INSERT or UPDATE accordingly.
No, there is not anything in the parameters that says 1= INSERT, 2 = UPDATE, 3 = DELETE?
How can i do that in sql ??
No,actually i don't want that For INSERT/UPDATE if the record exists an UPDATE can be done else an INSERT.I want that only the new records are inserted and old records updated.So,i think there should be use of parameters but i don't know how to use with this.I mean how to call that query thenafter in asp.net with C# 2005.As i have told above insert is done on button click event and update is done thru gridview.I have used sqldatasource to retrieve data.
Sorry i have modified somewhat my code but now i don't know what should be done.I have removed line nos."sorry".
OK,i will keep the separate procedure for the delete.
.
ALTER PROCEDURE CompanyStoredProcedure
@uspcompanyid int,
@uspcompanyname varchar(20),
@uspaddress1 varchar(30),
@frmErrorMessage as varchar(256) OUTPUT,
@RETURNVALUE as int OUTPUT,
@RETURNID as int OUTPUT
AS
declare
@companyid int,
@companyname varchar(20),
@address1 varchar(30)
BEGIN
SET NOCOUNT ON
begin
Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
return -9
end
begin
--validation...
if (@uspcompanyname is Null or @uspcompanyname = '')
begin
Select @RETURNVALUE = -9
select @frmErrorMessage = 'Company Name is empty'
return -9
end
if exists (select companyid from companymaster
where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))
begin
select @companyid = companyid from companymaster
where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )
end
else
select @companyname = cast (@uspcompanyname as varchar(20))
select @address1 = cast(@uspaddress1 as varchar(30))
select @companyid = isnull(max(companyid),0) + 1 from companymaster
IF exists(SELECT * from companymaster where companyname=@companyname)
begin
Select @frmErrorMessage = 'Record With Company Name '
+ @companyname + ' is Already Exisiting For The Company Name '
return -9
end
-- the following codes inserts
begin transaction
IF NOT EXISTS( SELECT * FROM companymaster WHERE CompanyName = @companyname AND Address1 = address1)
BEGIN
INSERT INTO companymaster
( companyname, address1)
VALUES (@companyname,@address1)
SELECT @companyid = SCOPE_IDENTITY()
END
commit transaction
select @RETURNVALUE = 0
select @RETURNID = @companyid
end
-- the following codes edit/updates
begin
UPDATE companymaster
SET companyname=@companyname,
address1=@address1
WHERE companyid =cast(@uspcompanyid as int)
select @RETURNVALUE = 0
select @RETURNID = cast(@uspcompanyid as int)
end
SET NOCOUNT OFF
END