By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,469 Members | 1,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,469 IT Pros & Developers. It's quick & easy.

stored procedure help pls

P: 16
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.
Apr 3 '07 #1
Share this Question
Share on Google+
17 Replies


iburyak
Expert 100+
P: 1,017
Try this:
[PHP]
ALTER PROCEDURE CompanyStoredProcedure
@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.
@frmErrorMessage as varchar(256) OUTPUT,
@RETURNVALUE as int OUTPUT,
@RETURNID as int OUTPUT
AS
declare @companyid int

BEGIN

If isnull(@uspcompanyname,'') = ''
begin
Select @RETURNVALUE = -9
select @frmErrorMessage = '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 @frmErrorMessage = '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=@address1
WHERE companyid = @uspcompanyid

else
begin
select @RETURNVALUE = -9
select @frmErrorMessage = '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(companyid),0) + 1 from companymaster

INSERT INTO companymaster(companyid, companyname, address1)
VALUES (@companyid, @companyname,@address1)
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
Expert 100+
P: 1,017
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

P: 16
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

P: 16
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
Expert 100+
P: 1,017
Change this:

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

[PHP] UPDATE companymaster
SET companyname = @uspcompanyname,
address1= @uspaddress1
WHERE companyid = @uspcompanyid [/PHP]
Apr 3 '07 #6

iburyak
Expert 100+
P: 1,017
Change this:


[PHP]INSERT INTO companymaster(companyid, companyname, address1)
VALUES (@companyid, @companyname,@address1)[/PHP]

to this:


[PHP] INSERT INTO companymaster(companyid, companyname, address1)
VALUES (@companyid, @uspcompanyname, @uspaddress1)[/PHP]
Apr 3 '07 #7

P: 16
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......what should be next ??

thanxs
Apr 3 '07 #8

iburyak
Expert 100+
P: 1,017
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(companyid),0) + 1 from companymaster

INSERT INTO companymaster(companyid, companyname, address1)
VALUES (@companyid, @companyname,@address1)
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 @frmErrorMessage = 'Company name ' + @uspcompanyname + ' already exists.'

Return -9
end

begin transaction

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



INSERT INTO companymaster(companyid, companyname, address1)

VALUES (@companyid, @companyname,@address1)

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,@address1)

commit transaction
Apr 3 '07 #9

P: 16
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

iburyak
Expert 100+
P: 1,017
Autogen is only for integers.
Apr 4 '07 #11

iburyak
Expert 100+
P: 1,017
Now, for companyid it should be C001,C002,C003......C999

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

thanxs
In respond to your question above after C999 should go C1000
If you want 4 characters code only, then it should be D001


If you have alphanumeric ID line bellow will not work in your stored proc.

[PHP]select @companyid = isnull(max(companyid),0) + 1 from companymaster [/PHP]

Maybe you can just add C every time you display company ID anywhere on the front end but on the server you will have it numeric autogenerated.
Apr 4 '07 #12

P: 16
ALTER PROCEDURE dbo.StoredProcedure4

@uspcompanyid int ,
@uspcompanyname varchar(20),
@uspaddress1 varchar(30),
-- add this variable to know what to do in case of delete pass D to it.
@Action varchar(1) = '',
@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
If isnull(@uspcompanyname,'') = ''
begin
Select @RETURNVALUE = -9

select @frmErrorMessage = '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 @frmErrorMessage = '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= @uspaddress1
WHERE companyid = @uspcompanyid


else
begin
select @RETURNVALUE = -9

select @frmErrorMessage = 'Can not change company name ' + companyname + ' to ' + @uspcompanyname + '. This name already exists.'
from companymaster
where companyid = @uspcompanyid

Return -9
end

else
begin

if exists(select * from companymaster where companyname = @uspcompanyname)

begin
select @RETURNVALUE = -9

select @frmErrorMessage = 'Company name ' + @uspcompanyname + ' already exists.'

Return -9
end

begin transaction
select @companyid = isnull(max(companyid),0) + 1 from companymaster

INSERT INTO companymaster( companyname, address1)
VALUES ( @companyname,@address1)
SELECT @companyid = SCOPE_IDENTITY()
SET @frmErrorMessage = 'Company Name and Address are now recorded'
commit transaction

end

select @RETURNVALUE = 0

select @RETURNID = @companyid
SET NOCOUNT OFF
END



Expand|Select|Wrap|Line Numbers
  1. Procedure or Function 'StoredProcedure4' expects parameter '@uspcompanyid', which was not supplied.
  2. Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'StoredProcedure4' expects parameter '@uspcompanyid', which was not supplied.
  3.  
I have changed somewhat accordingly and where the errors were coming.
But,again sorry to disturb and pls take ur some valuable time again to go thru the code.can u pls trace on ur sql server2005 if possible ??
So,that i don't have to disturb again for the same post...
Thanxs for ur replies and response..
Apr 4 '07 #13

iburyak
Expert 100+
P: 1,017
You get this error message when you try to execute stored procedure or just try to compile procedure itself?
Apr 4 '07 #14

iburyak
Expert 100+
P: 1,017
OK I did some changes and testing and it looks like it works

1. Create table

[PHP]Create table companymaster(
companyid int identity(1,1),
companyname varchar(20),
address1 varchar(30))[/PHP]

2. Create procedure

[PHP]create PROCEDURE dbo.StoredProcedure4

@uspcompanyid int = null,
@uspcompanyname varchar(20),
@uspaddress1 varchar(30),
-- add this variable to know what to do in case of delete pass D to it.
@Action varchar(1) = '',
@frmErrorMessage varchar(256) OUTPUT,
@RETURNVALUE int OUTPUT,
@RETURNID int OUTPUT

AS

DECLARE @companyid int

BEGIN
SET NOCOUNT ON
If isnull(@uspcompanyname,'') = ''
begin
Select @RETURNVALUE = -9
select @frmErrorMessage = '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 @frmErrorMessage = '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= @uspaddress1
WHERE companyid = @uspcompanyid


else
begin
select @RETURNVALUE = -9

select @frmErrorMessage = 'Can not change company name ' + companyname + ' to ' + @uspcompanyname + '. This name already exists.'
from companymaster
where companyid = @uspcompanyid

Return -9
end

else
begin

if exists(select * from companymaster where companyname = @uspcompanyname)

begin
select @RETURNVALUE = -9
select @frmErrorMessage = 'Company name ' + @uspcompanyname + ' already exists.'
Return -9
end

begin transaction
select @companyid = isnull(max(companyid),0) + 1 from companymaster

INSERT INTO companymaster( companyname, address1)
VALUES ( @uspcompanyname,@uspaddress1)
SELECT @companyid = SCOPE_IDENTITY()
SET @frmErrorMessage = 'Company Name and Address are now recorded'
commit transaction

end

select @RETURNVALUE = 0

select @RETURNID = @companyid
SET NOCOUNT OFF
END [/PHP]

3. Test procedure with following cases:
A.
[PHP]Declare
@frmErrorMessage as varchar(256),
@RETURNVALUE as int,
@RETURNID as int

exec StoredProcedure4 null, 'company1', 'New York','', @frmErrorMessage output, @RETURNVALUE output, @RETURNID output
select @frmErrorMessage, @RETURNVALUE, @RETURNID[/PHP]

B.
[PHP]Declare
@frmErrorMessage as varchar(256),
@RETURNVALUE as int,
@RETURNID as int

exec StoredProcedure4 1, 'company1', 'New York1','', @frmErrorMessage output, @RETURNVALUE output, @RETURNID output
select @frmErrorMessage, @RETURNVALUE, @RETURNID[/PHP]

C.
[PHP]Declare
@frmErrorMessage as varchar(256),
@RETURNVALUE as int,
@RETURNID as int

exec StoredProcedure4 2, 'company1', 'New York1','', @frmErrorMessage output, @RETURNVALUE output, @RETURNID output
select @frmErrorMessage, @RETURNVALUE, @RETURNID[/PHP]

E.
[PHP]Declare
@frmErrorMessage as varchar(256),
@RETURNVALUE as int,
@RETURNID as int


exec StoredProcedure4 null, 'company2', 'New York2','', @frmErrorMessage output, @RETURNVALUE output, @RETURNID output
select @frmErrorMessage, @RETURNVALUE, @RETURNID[/PHP]

F.

[PHP]Declare
@frmErrorMessage as varchar(256),
@RETURNVALUE as int,
@RETURNID as int

exec StoredProcedure4 2, 'company2', 'New York2','D', @frmErrorMessage output, @RETURNVALUE output, @RETURNID output
select @frmErrorMessage, @RETURNVALUE, @RETURNID[/PHP]

Good Luck.
Apr 4 '07 #15

P: 16
[/quote]Good Luck.[/quote]
Thanxs...
Actually i tried to execute each and every query separately.To my surprised after some changes it run and stored into the database.
Now,let me try urs changed and tested code as u excersized on that...thanxs very much...
Apr 4 '07 #16

iburyak
Expert 100+
P: 1,017
You are always welcome..... :)

Irina.
Apr 4 '07 #17

P: 16
wow..great..it's working fine
Apr 4 '07 #18

Post your reply

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