Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 storedproce

Author  Topic 

miaaa00
Starting Member

2 Posts

Posted - 2007-04-03 : 16:27:07
THANXS .

miaaa00
Starting Member

2 Posts

Posted - 2007-04-03 : 16:32:25
I want SELECT @companyid = SCOPE_IDENTITY()
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-03 : 16:53:20
small world ha...

INSERT INTO companymaster ( companyname, address1 )
VALUES (@companyname,@address1)
SELECT @companyid = SCOPE_IDENTITY()



************************
Life is short. Enjoy it.
************************
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 17:14:14
SELECT @returnid = SCOPE_IDENTITY()


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 00:38:28
quote:
Originally posted by miaaa00

THANXS .


You should give your thanks as new reply and should not replace it with your question

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 00:45:16
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.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 00:50:01
How did you find his OQ?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 00:52:03
It is posted all over.
At Wrox. At dbForums...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 00:56:35
quote:
Originally posted by Peso

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


Peter Larsson
Helsingborg, Sweden


Probably they will be replaced with Thanx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-04 : 01:04:58
quote:
Originally posted by Peso

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


Peter Larsson
Helsingborg, Sweden



and ASP.NET forums too...I can do the replae over there

************************
Life is short. Enjoy it.
************************
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 01:08:40
What do you do when poster cross post in different site ?
You cross reply


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 01:09:10
quote:
Originally posted by dinakar

and ASP.NET forums too...

Is this a new all-time high?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 01:32:16
For more information, read here

http://forums.asp.net/thread/1649666.aspx
http://www.dbforums.com/showthread.php?t=1616847
http://p2p.wrox.com/topic.asp?TOPIC_ID=58578

Ps. I am bored! Need something else to do.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 03:17:15
It is common for some newbies

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -