quote:

---

Originally posted by miaaa00

THANXS .

---

quote:

---

Originally posted by miaaa00 aka kajal aka pinku aka riaaa

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,

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.

---

quote:

---

Originally posted by Peso

It is posted all over.
At Wrox. At dbForums...


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by Peso

It is posted all over.
At Wrox. At dbForums...


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by dinakar

and ASP.NET forums too...

---