Pls check my code for the stored procedure which i created for the companydetails including
companyid P.K. Not Null int(4),
companyname Not Null varchar (20),
address varchar(30)
where companyid is the primary key and it should be autogenerate.
I also want that it should check if the name exits or not.It should also check that the field is entered and not kept null.If it's null then should return the error message.
I want to write the queries select,insert,update and delete in the single stored procedure.
How can i differ all the query individually in a stored procedure.
The select and insert query are done on the button click event whereas the update,delete queries are performed in the gridview link event.
Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.
waiting for the reply and with corrections.
The coding is perfomed in sql server 2005 and asp.net with C# 2005,
Code:
1 ALTER PROCEDURE CompanyStoredProcedure
2 @uspcompanyid int,
3 @uspcompanyname varchar(20),
4 @uspaddress1 varchar(30),
5 @frmErrorMessage as varchar(256) OUTPUT,
6 @RETURNVALUE as int OUTPUT,
7 @RETURNID as int OUTPUT
8 AS
9 declare
10 @companyid int,
11 @companyname varchar(20),
12 @address1 varchar(30)
13
14 BEGIN
15
16 begin
17 Select @RETURNVALUE = -9
18 RETURN -9
19 end
20
21 begin
22 Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
23 return -9
24 end
25
26
27
28 begin
29 --validation...
30 if (@uspcompanyname is Null or @uspcompanyname = '')
31 begin
32 Select @RETURNVALUE = -9
33 select @frmErrorMessage = 'Company Name is empty'
34 return -9
35 end
36
37 if exists (select companyid from companymaster
38 where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))
39 begin
40 select @companyid = companyid from companymaster
41 where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )
42 end
43 else
44
45 select @companyname = cast (@uspcompanyname as varchar(20))
46 select @address1 = cast(@uspaddress1 as varchar(30))
47 select @companyid = isnull(max(companyid),0) + 1 from companymaster
48
49 IF exists(SELECT * from companymaster where companyname=@companyname)
50 begin
51 Select @frmErrorMessage = 'Record With Company Name '
52 + @companyname + ' is Already Exisiting For The Company Name '
53 return -9
54 end
55
56 -- the following codes inserts
57 begin transaction
58 INSERT INTO companymaster
59 ( companyname, address1)
60 VALUES (@companyname,@address1)
61 commit transaction
62
63 select @RETURNVALUE = 0
64 select @RETURNID = @companyid
65
66 end
67
68
69 -- the following codes edit/updates
70 begin
71 UPDATE companymaster
72 SET companyname=@companyname,
73 address1=@address1
74 WHERE companyid =cast(@uspcompanyid as int)
75
76 select @RETURNVALUE = 0
77 select @RETURNID = cast(@uspcompanyid as int)
78 end
79 -- the following codes delete
80 begin
81 DELETE companymaster WHERE (companyid = @companyid)
82 end
83
84 END
85
Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.
NEED HELP TO MODIFY STORED PROCEDURE
