存储过程就是程序,不知你说的调用是什么意思?在分析器中直接执行?在vb中调用?在C#中调用?给你个例子
CREATE PROCEDURE [update_date]
AS
declare @data char(8),
@date char(4),
@next_data smalldatetime
select @data=(select top 1date from T_as)
select @next_data=cast(('20'+@data) as smalldatetime)
select @next_data=dateadd(month,1,@next_data)
select @date=substring(cast(year(@next_data) as char(4)),3,2)+cast(month(@next_data) as char(2))
select @date=(ltrim(rtrim(@date)))
if len(@date)=3
set @date=substring(@date,1,2)+'0'+substring(@date,3,1)
if not exists (select * from date )
insert date select @date
else
update date set date=@date要执行它,则在查询分析器中:
exec update_date
即可。
CREATE PROCEDURE [update_date]
AS
declare @data char(8),
@date char(4),
@next_data smalldatetime
select @data=(select top 1date from T_as)
select @next_data=cast(('20'+@data) as smalldatetime)
select @next_data=dateadd(month,1,@next_data)
select @date=substring(cast(year(@next_data) as char(4)),3,2)+cast(month(@next_data) as char(2))
select @date=(ltrim(rtrim(@date)))
if len(@date)=3
set @date=substring(@date,1,2)+'0'+substring(@date,3,1)
if not exists (select * from date )
insert date select @date
else
update date set date=@date要执行它,则在查询分析器中:
exec update_date
即可。
解决方案 »
- 求助:一行多列的问题(高手进)
- datetime类型如何计算相差的时间?
- [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server
- 如何循环执行存储过程? 进来看问题描述:)
- 关于MySQL出现Lost connection to MySQL server during query的问题
- 自己写的一个split的函数,用在sql语句中出现的问题
- 关于ROW_ID
- oracle有没有默认的系统管理员?用户名和密码是什么?
- 关于SQL Server,你们遇到过吗????????????????
- 怎么样合并这两个SQL,在线等。。。
- 求提高性能的解决方案-菜鸟请各位高手支招
- count(*)达到300多万条的时候会很慢......,请问用存储过程怎么写!
asp.net C#:
private void InsTxt()
{
Result=false;
//Conn=new SqlConnection(BaseStr);
string ProcName="DbaseCredUser";
Conn= new SqlConnection(BaseStr); Cmd=new SqlCommand(ProcName,Conn);
Cmd.CommandType=CommandType.StoredProcedure; Cmd.Parameters.Add(new SqlParameter("@Nickname",SqlDbType.VarChar,20));
Cmd.Parameters["@Nickname"].Value=TxtUserId.Value.ToString(); Cmd.Parameters.Add(new SqlParameter("@Username",SqlDbType.Char,8));
Cmd.Parameters["@Username"].Value=TxtUsername.Value.ToString();
Cmd.Parameters.Add(new SqlParameter("@Groupname",SqlDbType.VarChar,15));
Cmd.Parameters["@Groupname"].Value=SelUsergroup.Value.ToString(); Cmd.Parameters.Add(new SqlParameter("@Password",SqlDbType.VarChar,50));
Cmd.Parameters["@Password"].Value=FormsAuthentication.HashPasswordForStoringInConfigFile(PassPwd.Value.ToString(),"MD5").ToString(); Cmd.Parameters.Add(new SqlParameter("@Type",SqlDbType.Char,10));
Cmd.Parameters["@Type"].Value=SelType.Value.ToString(); Cmd.Parameters.Add(new SqlParameter("@SuccFlag",SqlDbType.Char,8));
Cmd.Parameters["@SuccFlag"].Direction=ParameterDirection.Output; try
{
Conn.Open();
Cmd.ExecuteNonQuery();
DispMsg=new ErrMsg();
Msg=Cmd.Parameters["@SuccFlag"].Value.ToString();
Message=DispMsg.SelErrMsg(Msg).ToString();
Response.Write ("<script language=javascript>alert("+"'"+Message+"'"+"); </script>");
}
catch(Exception ex)
{
ToErrPage=new GetErrNo();
ToErrPage.GoToErrPage(this,1,ex);
//GetErrNo(ex);
}
finally
{
Empty();
Conn.Close();
}
}其中DbaseCredUser为存储过程,
CREATE PROCEDURE [DbaseCredUser] @Nickname varchar(20),@Username char(8), @Password varchar(50), @Groupname varchar(15),
@Type char(10),@SuccFlag char(8) output
AS
declare @Admin int
select @Admin=0
select @SuccFlag='010103'
if exists(select * from T_username where Ltrim(Rtrim(Nickname))=@Nickname)
begin
select @SuccFlag='010101'
return
end
if exists(select * from T_username where Ltrim(Rtrim(Uname))=@Username)
begin
select @SuccFlag='010102'
return
end
if (@Type='管理员')
begin
select @Admin=1
end
insert T_username(Nickname,Uname,Upwd,Groupname,Admin) select @Nickname,@Username,convert(varbinary (50), @Password),@Groupname,@Admin
if (@@error=0)
begin
select @SuccFlag='010104'
end
GO
@aa varchar(20)--輸入參數
as
select @aa
go
--調用函數
--exec sp_test 'ok'
初学的话看4楼的例子比较好
还有几个全局变量最好记住@@error,@@rowcount,rowcount,@@identity,
其他的系统函数看看联机帮助好了
if exists (select * from sysobjects where id = object_id('dbo.pr_pur10500') )
drop procedure dbo.pr_pur10500
go
create procedure pr_pur10500
@v_docno char(9),
@v_docseq smallint,
@v_type char(1),
@v_docdate datetime,
@v_messcode char(4) output
WITH ENCRYPTIONas declare @l_del char(9),
@l_mrp char(1),
@l_unit char(3),
@l_punit char(3),
@l_uom char(3),
@l_pofact numeric(8,4),
@l_qty numeric(14,4),
@l_unprc numeric(14,4),
@l_amt numeric(14,2),
@l_taxamt numeric(14,2),
@l_taxcode char(3),
@l_taxrate numeric(4,2),
@l_trancnt int,
@l_r int select @l_trancnt = @@trancount
if @l_trancnt = 0
begin tran tran_pr_pur10500 --事务开始
else
save tran tran_pr_pur10500 select @v_messcode = '0002' select @l_del = 'Y' ,
@l_mrp = 'N'
select @l_unit = uom from dbo.mom10100 ( holdlock)
where no = @v_docno and
seq = @v_docseq
select @l_uom = p.uom ,
@l_pofact = p.pofact
from dbo.inv10100 p ( holdlock),
dbo.mom10100 m ( holdlock)
where p.part = m.part and
m.no = @v_docno and
m.seq = @v_docseq if @l_unit = @l_uom
select @l_pofact = 1 if exists
(select * from dbo.mrp20150 p ( holdlock),
dbo.mom10100 m ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq and
p.planno = m.relno and
p.planseq = m.relseq )
begin
select @l_mrp = 'Y'
/*********upd mps20100******/
delete from dbo.mps20100
from dbo.mrp20150 p ( holdlock),
dbo.mps20100 s ( holdlock),
dbo.mom10100 m ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq and
p.planno = m.relno and
p.planseq = m.relseq and
p.part = s.part and
p.date = s.date and
p.prio = s.prio and
s.type = '4' and
p.orderno = s.no and
p.orderseq = s.seq and
p.relno = s.relno and
p.relseq = s.relseq and
(round((m.qty * @l_pofact + s.rlsqty),2) >= s.qty)
if @@rowcount = 0
select @l_del = 'N' if @l_del = 'N'
update s
set s.rlsqty = s.rlsqty + round(m.qty * @l_pofact,2)
from dbo.mrp20150 p ( holdlock),
dbo.mps20100 s ( holdlock),
dbo.mom10100 m ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq and
p.planno = m.relno and
p.planseq = m.relseq and
p.part = s.part and
p.date = s.date and
p.prio = s.prio and
s.type = '4' and
p.orderno = s.no and
p.orderseq = s.seq and
p.relno = s.relno and
p.relseq = s.relseq if @@error <> 0
begin
rollback tran tran_pr_pur10500 --更新操作错误,事务回滚
return 99999
end
end
update i
set i.onorder = i.onorder + round(m.qty * @l_pofact,4)
from dbo.inv10101 i ( holdlock),
dbo.mom10100 m ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq and
i.part = m.part and
i.prloc = m.prloc
if @@rowcount = 0
insert into dbo.inv10101
(part,prloc,onorder)
select m.part,m.prloc,round(m.qty * @l_pofact,4)
from dbo.mom10100 m ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq
if @@error <> 0
begin
rollback tran tran_pr_pur10500
return 99999
end
/***************update pur10100*********/
select @l_qty = qty,
@l_unprc = price
from dbo.mom10100 ( holdlock)
where no = @v_docno and
seq = @v_docseq
select @l_amt = round(@l_qty * @l_unprc,2) if not exists (select * from dbo.pur10100 where pono = @v_docno)
begin
insert into dbo.pur10100
(pono,doctype,reldate,vendor,area,purchaser,taxcode,taxrate,status,
plpaydate,pacpdate,createdate,createuser,modifydate,modifyuser)
select m.no,@v_type,@v_docdate,m.vendor,v.area,v.purchaser,v.taxcode,s.rate,'OP',
m.reldate,m.duedate,
convert(datetime, convert(char(20),getdate(),101)),m.createuser,
convert(datetime, convert(char(20),getdate(),101)),m.createuser
from dbo.mom10100 m ( holdlock),
dbo.sys10120 v ( holdlock),
dbo.sys10290 s ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq and
m.vendor = v.addrno and
v.taxcode = s.taxcode
if @@error <> 0
begin
rollback tran tran_pr_pur10500
return 99999
end
end
select @l_taxcode = taxcode,@l_taxrate = taxrate
from pur10100 where pono = @v_docno
if @l_taxcode = '3' or @l_taxcode = '5' or @l_taxcode = '7' or @l_taxcode = '9'
select @l_taxamt = round(@l_amt * @l_taxrate / 100,2)
else
select @l_taxamt = 0
select @l_pofact = p.pofact
from dbo.inv10100 p ( holdlock),
dbo.mom10100 m ( holdlock)
where p.part = m.part and
m.no = @v_docno and
m.seq = @v_docseq if @@rowcount = 0 select @l_pofact = 1 if not exists (select * from dbo.pur10110
where pono = @v_docno and poseq = @v_docseq)
begin
insert into dbo.pur10110
(pono,poseq,part,prloc,qty,unit,reldate,pacpdate,orderno,orderseq,reason,
unprc,status,amt,rs,pofact,taxamt,relno,relseq,
createdate,createuser,modifydate,modifyuser)
select m.no,m.seq,m.part,m.prloc,m.qty,m.uom,m.reldate,m.duedate,
m.orderno,m.orderseq,m.reason,m.price,'OP',@l_amt,'R',@l_pofact,
@l_taxamt,m.relno,m.relseq,
convert(datetime, convert(char(20),getdate(),101)),m.createuser,
convert(datetime, convert(char(20),getdate(),101)),m.createuser
from dbo.mom10100 m ( holdlock)
where m.no = @v_docno and
m.seq = @v_docseq
if @@error <> 0
begin
rollback tran tran_pr_pur10500
return 99999
end
end
select @v_messcode = '0003'if @l_trancnt = 0
commit tranreturn 0
go
下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
用ASP.NET与SQL SERVER可是缘份最好了,稍大的程序一般第一先考虑的是SQL SERVER,只是一些很考虑经济的才使用ACCESS等了。用SQL SERVER,为了使数据库的效率更好,一般都会才取存储过程,因存储过程执行速度快,并且可以实现一些高级的查询等功能。比如传入一些数据参数,但执行的SQL过程可能不同等。 下面就来个例子,建立一新的角色,要求角色的名字不能重复,以下是一存储过程。
CREATE PROCEDURE sp_AccountRole_Create
@CategoryID int,@RoleName nvarchar(10),@Description nvarchar(50),@RoleID int outputAS DECLARE @Count int
-- 查找是否有相同名称的记录 SELECT @Count = Count(RoleID) FROM Account_Role WHERE RoleName = @RoleName
IF @Count = 0
INSERT INTO Account_Role (CategoryID, RoleName, Description) valueS (@CategoryID, @RoleName, @Description)
SET @RoleID = @@IDENTITY
RETURN 1