存储过程帮助请 [英] stored procedure help pls

问题描述

请检查我的代码以查找我为公司详细信息创建的存储过程，包括


companyid P.K.不是空的int（4），


companyname非零varchar（20），


地址varchar（30）


其中companyid是主键，它应该是自动生成的。


我也希望它应该检查名称是否退出。它还应该检查输入字段并且不保留为null。如果它为null则应返回错误消息。


我想在单个存储中编写查询选择，插入，更新和删除程序。


我如何在存储过程中单独区分所有查询。


选择和插入查询在单击按钮上完成事件，而更新，删除查询在gridview链接事件中执行。


请帮助我并相应地修改我的代码，你的建议非常了解存储过程。首先阅读我想要的内容然后给予回复。


等待回复和更正。


编码在sql server 2005和asp.net中使用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声明

10 @companyid int，

11 @companyname varchar（20），

12 @ address1 varchar（30）

13

14 BEGIN

15

16开始

17选择@RETURNVALUE = -9

18返回-9

19结束


21开始

22选择@frmErrorMessage =''未指定操作模式''

23返回-9

24结束

25

26

27

28开始

29 - 验证...

30 if（@uspcompanyname为Null或@uspcompanyname =''''）

31开始

32选择@RETURNVALUE = -9

33选择@frmErrorMessage =''公司名称为空''

34返回-9

35结束

36

37如果存在（从companymaster选择companyid

38其中upper（companyname）= upper（ cast（@uspcompanyname as varchar（20））））

39开始

40从companymaster选择@companyid = companyid

41其中upper（companyname）= upper（cast（@uspcompanyname as varchar（20）））

42 end

43 else

44

45选择@companyname = cast（@uspcompanyname as varchar（20））

46 select @ address1 = cast（@ uspaddress1 as varchar（30））

47选择@companyid = isnull（max（companyid），0）+ 1来自companymaster

48

49 IF存在（SELECT * from companymaster where companyname = @ companyname ）

50开始

51选择@frmErrorMessage =''以公司名称记录''

52 + @companyname +''已经存在对于公司名称''

53返回-9

54结束

55

56 - 以下代码插入

57开始交易

58 INSERT INTO companymaster

59（companyname，地址1）

60 VALUES（@ companyname，@ address1）

61提交交易

62

63 select @ RETURNVALUE = 0

64选择@RETURNID = @companyid

65

66结束

67

68

69 - 以下代码编辑/更新

70开始

71 UPDATE companymaster

72 SET companyname = @ companyname，

73 address1 = @ address1

74 WHERE companyid = cast（@uspcompanyid as int）

75

76选择@RETURNVALUE = 0

77选择@RETURNID = cast（@uspcompanyid as int）

78结束

79 - 以下代码删除

80开始

81 DELETE companymaster WHERE（companyid = @companyid）

82结束

83

84结束

85



请帮助帮助我并相应地修改我的代码，你的建议非常了解存储过程。首先阅读我想要的内容然后给予回复。

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.

推荐答案

试试这个：

[PHP]

ALTER PROCEDURE CompanyStoredProcedure

@uspcompanyid int，

@uspcompanyname varchar（20），

@ uspaddress1 varchar（30），

@Action varchar（1）='''' - 添加此变量以了解删除传递给它时要做什么。

@frmErrorMessage as varchar（256）OUTPUT，

@RETURNVALUE as int OUTPUT，

@RETURNID as int OUTPUT

AS

声明@companyid int


BEGIN


如果isnull（@uspcompanyname，''''） =''''

开始

选择@RETURNVALUE = -9

选择@frmErrorMessage =''公司名称为em pty''

返回-9

结束


- 如果需要，请删除

IF @动作=''D''

开始

DELETE companymaster WHERE companyid = @uspcompanyid

IF @@ Rowcount = 0

开始

选择@RETURNVALUE = -9

选择@frmErrorMessage =''公司''+ @uspcompanyname +''未找到。''

返回-9

结束

否则

开始

选择@RETURNVALUE = 0

返回0

结束

结束


- 检查此公司是否已存在，但名称是不属于任何其他身份证。

如果存在（从companymaster选择companyid，其中companyid = @uspcompanyid）

如果不存在（从companymaster中选择* companyname = @uspcompanyname和companyid<> @uspcompanyid ）

UPDATE companymaster

SET companyname = @uspcompanyname，

address1 = @ address1

WHERE companyid = @uspcompanyid


其他

开始

选择@RETURNVALUE = -9

选择@frmErrorMessage =''无法将公司名称''+ companyname +''更改为''+ @uspcompanyname +''。这个名字已经存在。''

来自companymaster，其中companyid = @uspcompanyid

返回-9

结束

否则

开始

开始交易

选择@companyid = isnull（max（companyid），0）+ 1 from companymaster


INSERT INTO companymaster（companyid，companyname，address1）

VALUES（@ company，@ companyname，@ address1）

提交交易


选择@RETURNVALUE = 0

选择@RETURNID = @companyid

结束


结束[ / PHP]


我开始逐行纠正但是我不可能改变太多东西。

如果你对代码不了解，你最好问我为什么我会做一些不同的事情。或者为什么我搬走或移动东西。


希望它有效。如您所知，我不了解您的所有业务需求，也无法测试我的代码。


祝你好运。

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.

纠正我的程序


最后更改


[PHP]选择@RETURNVALUE = 0

选择@RETURNID = @companyid

结束[/ PHP]



到此：

[PHP]结束

选择@RETURNVALUE = 0

选择@RETURNID = @companyid [/ PHP]

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]

更正我的程序


最后更改此内容


[PHP]选择@RETURNVALUE = 0

选择@RETURNID = @companyid

end [/ PHP]



到此：

[PHP]结束

选择@RETURNVALUE = 0

选择@RETURNID = @companyid [/ PHP]

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非常适合你回复并纠正我的错误，占用你的重要时间。

我有尚未尝试过代码......但希望它能运作s ...


我会问是否存在一些问题。

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.

这篇关于存储过程帮助请的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！