473,883 Members | 1,809 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

stored procedure help pls

16 New Member
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,u pdate 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 CompanyStoredPr ocedure
2 @uspcompanyid int,
3 @uspcompanyname varchar(20),
4 @uspaddress1 varchar(30),
5 @frmErrorMessag e 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 @frmErrorMessag e = 'The Operation Mode Has Not Been Specified'
23 return -9
24 end
25
26
27
28 begin
29 --validation...
30 if (@uspcompanynam e is Null or @uspcompanyname = '')
31 begin
32 Select @RETURNVALUE = -9
33 select @frmErrorMessag e = 'Company Name is empty'
34 return -9
35 end
36
37 if exists (select companyid from companymaster
38 where upper(companyna me) = upper(cast(@usp companyname as varchar(20))))
39 begin
40 select @companyid = companyid from companymaster
41 where upper(companyna me)=upper(cast( @uspcompanyname as varchar(20) ) )
42 end
43 else
44
45 select @companyname = cast (@uspcompanynam e as varchar(20))
46 select @address1 = cast(@uspaddres s1 as varchar(30))
47 select @companyid = isnull(max(comp anyid),0) + 1 from companymaster
48
49 IF exists(SELECT * from companymaster where companyname=@co mpanyname)
50 begin
51 Select @frmErrorMessag e = '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=@co mpanyname,
73 address1=@addre ss1
74 WHERE companyid =cast(@uspcompa nyid as int)
75
76 select @RETURNVALUE = 0
77 select @RETURNID = cast(@uspcompan yid 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.
Apr 3 '07 #1
17 4023
iburyak
1,017 Recognized Expert Top Contributor
Try this:
[PHP]
ALTER PROCEDURE CompanyStoredPr ocedure
@uspcompanyid int,
@uspcompanyname varchar(20),
@uspaddress1 varchar(30),
@Action varchar(1) = '' -- add this variable to know what to do in case of delete pass D to it.
@frmErrorMessag e as varchar(256) OUTPUT,
@RETURNVALUE as int OUTPUT,
@RETURNID as int OUTPUT
AS
declare @companyid int

BEGIN

If isnull(@uspcomp anyname,'') = ''
begin
Select @RETURNVALUE = -9
select @frmErrorMessag e = 'Company Name is empty'
return -9
end

-- Delete if requested
IF @Action = 'D'
begin
DELETE companymaster WHERE companyid = @uspcompanyid
IF @@Rowcount = 0
begin
select @RETURNVALUE = -9
select @frmErrorMessag e = 'Company ' + @uspcompanyname + ' was not found.'
Return -9
end
Else
begin
select @RETURNVALUE = 0
Return 0
end
end

-- Check if this companyid already exists but the name is not belonging to any other id.
if exists (select companyid from companymaster where companyid = @uspcompanyid)
if not exists(select * from companymaster where companyname = @uspcompanyname and companyid <> @uspcompanyid)
UPDATE companymaster
SET companyname = @uspcompanyname ,
address1=@addre ss1
WHERE companyid = @uspcompanyid

else
begin
select @RETURNVALUE = -9
select @frmErrorMessag e = 'Can not change company name ' + companyname + ' to ' + @uspcompanyname + '. This name already exists.'
from companymaster where companyid = @uspcompanyid
Return -9
end
else
begin
begin transaction
select @companyid = isnull(max(comp anyid),0) + 1 from companymaster

INSERT INTO companymaster(c ompanyid, companyname, address1)
VALUES (@companyid, @companyname,@a ddress1)
commit transaction

select @RETURNVALUE = 0
select @RETURNID = @companyid
end

END [/PHP]


I started to correct line by line but it wasn't possible too many things I've changed.
You better ask me why I did some things differently if you didn't understand from code. Or why I removed or moved things.

Hope it works. As you understand I don’t know all your business requirements and wasn’t able to test my code.

Good Luck.
Apr 3 '07 #2
iburyak
1,017 Recognized Expert Top Contributor
correction to my procedure

Change this at the end

[PHP] select @RETURNVALUE = 0
select @RETURNID = @companyid
end [/PHP]


to this:
[PHP] end
select @RETURNVALUE = 0
select @RETURNID = @companyid [/PHP]
Apr 3 '07 #3
Riaaaa
16 New Member
correction to my procedure

Change this at the end

[PHP] select @RETURNVALUE = 0
select @RETURNID = @companyid
end [/PHP]


to this:
[PHP] end
select @RETURNVALUE = 0
select @RETURNID = @companyid [/PHP]
thanxs very much for ur reply and correcting my errors taking ur important time.
I have not tasted the code yet..but hope that it works...

I will ask if there's some problem pls look to that.
Apr 3 '07 #4
Riaaaa
16 New Member
thanxs
I will ask if there's some problem pls look to that.
It gives me the error of
1 ) Must declare the scalar variable @companyname and @address1
2) Incorrect near the syntax keyword 'else'
3) There was no comma after @Action varchar(1) = ' ' , that i have solved

There's no need of declaration :
DECLARE @companyid INT,
@companyname VARCHAR(20),
@address1 VARCHAR(30)
if i add the above code then there are errors at the time of running application...

Expand|Select|Wrap|Line Numbers
  1. Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'CompanyStoredProcedure' expects parameter '@uspcompanyid', which was not supplied.
I don't get now what's the mistake...
Apr 3 '07 #5
iburyak
1,017 Recognized Expert Top Contributor
Change this:

[PHP]UPDATE companymaster
SET companyname = @uspcompanyname ,
address1=@addre ss1
WHERE companyid = @uspcompanyid [/PHP]
to this:

[PHP] UPDATE companymaster
SET companyname = @uspcompanyname ,
address1= @uspaddress1
WHERE companyid = @uspcompanyid [/PHP]
Apr 3 '07 #6
iburyak
1,017 Recognized Expert Top Contributor
Change this:


[PHP]INSERT INTO companymaster(c ompanyid, companyname, address1)
VALUES (@companyid, @companyname,@a ddress1)[/PHP]

to this:


[PHP] INSERT INTO companymaster(c ompanyid, companyname, address1)
VALUES (@companyid, @uspcompanyname , @uspaddress1)[/PHP]
Apr 3 '07 #7
Riaaaa
16 New Member
My requirements ::

i want to do some checks for any existing data on either INSERT or UPDATE accordingly.

Is there any need to keep the differrent parameters that says 1= INSERT, 2 = UPDATE, 3 = DELETE?
I don't get that will there be any problem without parameters that what procedure query should be checked..You have told for DELETE it should be 'D' so just asking...

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.

Also with this can u please help me more ::

Can anyone please help me to generate the sequence alphanumeric autogenerated id's in sql server in the above insert stored procedure.

For E.g.

Table 1 : companydetails

company id - P.K.

companyname

address1

Table 2 : itemdetails

itemid - P.K.

companyid - F.K.

itemname

description

Now, for companyid it should be C001,C002,C003. .....C999

itemid it should be I001,I002,I003. ....I999......w hat should be next ??

thanxs
Apr 3 '07 #8
iburyak
1,017 Recognized Expert Top Contributor
In stored proc I check


[PHP]if exists (select companyid from companymaster where companyid = @uspcompanyid)[/PHP]

This means that record already exist and need an update
In Else condition I understand that there is no such record and proceed to insert.

Change this:

[PHP]else
begin
begin transaction
select @companyid = isnull(max(comp anyid),0) + 1 from companymaster

INSERT INTO companymaster(c ompanyid, companyname, address1)
VALUES (@companyid, @companyname,@a ddress1)
commit transaction

select @RETURNVALUE = 0
select @RETURNID = @companyid
end [/PHP]

to this:
[PHP]else

begin
if exists(select * from companymaster where companyname = @uspcompanyname )
begin
select @RETURNVALUE = -9

select @frmErrorMessag e = 'Company name ' + @uspcompanyname + ' already exists.'

Return -9
end

begin transaction

select @companyid = isnull(max(comp anyid),0) + 1 from companymaster



INSERT INTO companymaster(c ompanyid, companyname, address1)

VALUES (@companyid, @companyname,@a ddress1)

commit transaction


end

select @RETURNVALUE = 0

select @RETURNID = @companyid [/PHP]

in my changed code I check again if this company name already exists.


2. You want to autogenerate ID. It is possible if ID column is numeric only.


[PHP]companyid int identity(1,1)[/PHP]


In this case you don't have to insert new ID and insert should be changed to this:


begin transaction


INSERT INTO companymaster( companyname, address1)

VALUES ( @companyname,@a ddress1)

commit transaction
Apr 3 '07 #9
Riaaaa
16 New Member
thanxs again very much...i will try now.

For the autogenerate id like C001,C002...... ....
is it possible if i take the datatype as char instead of int.

OR is there any way to declare the 'C' as static and increament the int value as
'C' (001+001=002).
Apr 4 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
22156
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my application server can talk to the database. I've determined the failure occurs when the the following statement is executed: cstmt.execute(); (due to the failure of println statements placed afterwards). I get the following error after trying to...
0
6710
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft Visual Basic .NET version of this article, see 308049. For a Microsoft Visual C++ .NET version of this article, see 310071. For a Microsoft Visual J# .NET version of this article, see 320627. This article refers to the following Microsoft .NET...
3
2811
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default" when I launched the IBM Distributed Debugger via D:\IBMDebug>idebug.exe -qdaemon -quiport=8000,8001 First, a bit of background. I am running DB2 V7.2 with Fixpack 9 applied on Windows XP Professional (all critical service applied). I've written...
4
3199
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the Throwable as an OUT parameter and what datatype should I use in the CREATE PROCEDURE and DROP PROCEDURE statements? Here's what I tried: - the method signature for the stored procedure included: Throwable throwable
8
7952
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have created a temporary database with a tables space. Verified that DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER); INSERT INTO SESSION.TEMP VALUES(10); SELECT A FROM SESSION.TEMP; works from a query tool.
2
5470
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
0
2661
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how to call them from an ASP.Net page Every modern database system has a stored procedure language. SQL Server is no different and has a relatively sophisticated and easy to use system. This article will not attempt to go into depth in explaining...
3
3483
by: kd | last post by:
Hi All, How to debug a stored procedure? Thanks, kd
7
3473
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant' expects parameter '@EMail', which was not supplied. The field value was null in the database and not changed in the FormView so is null going back into the stored procedure. I'm stumped and would greatly appreciate any suggestions.
2
4114
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10743
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10416
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9574
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7971
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5797
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4612
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3233
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.