ASP.NET中常用的存储过程

1.查询功能： 描述：通常在网页上根据点击查询按钮获得所要结果 例： set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author:冰霜 -- Create date: 2009-09-04 -- Description:查询 -- ============================================= Alter PROCEDURE [dbo].[KDF_PackageMeetingRoom_Examine] ( @HotelID varchar(20), @State int, @StartDate varchar(30), @EndDate varchar(30) ) as DECLARE @MainStr VARCHAR(1000) set @MainStr='select * from PackageMeetingRoom where ID=ID ' if(@HotelID<>'') set @MainStr=@MainStr+'and HotelID like'+''''+'%'+@HotelID+'%'+'''' if (@State <> '-1') set @MainStr=@MainStr+'and State='+''''+convert(varchar(20),@State)+'''' if(@StartDate<>'' or @EndDate<>'') set @MainStr=@MainStr+'and Dtedate between'''+@StartDate+''''+'and'''+@EndDate+'''' set @MainStr=@MainStr+'order by Dtedate desc' exec (@MainStr)

很不好意思,最近有点忙,现在把一些常用的存储过程贴出来,以后整理 2,插入增加 ALTER PROCEDURE [dbo].[KDF_AccountRecord_Add] ( @AcNumOrder varchar(50), @UserID varchar(50), @TransactionType varchar(20), @AmountPaid int, @AmountReceived int ) AS insert into AccountRecord ( AcNumOrder, UserID, TransactionType, AmountPaid, AmountReceived ) values ( @AcNumOrder, @UserID, @TransactionType, @AmountPaid, @AmountReceived ) return @@IDENTITY   3.删除 ALTER PROCEDURE [dbo].[KDF_AccountRecord_Del] ( @ID int ) AS delete FROM AccountRecord     where ID= @ID   4,查询 ALTER PROCEDURE [dbo].[KDF_AccountRecord_ExamineByID] ( @ID int ) as select * from AccountRecord where @ID=ID 这个以可以自己写一些要查询的内容,不推荐用* 5.登录 ALTER PROCEDURE [dbo].[KDF_Admin_Login] ( @AdminName varchar(50), @AdminPwd varchar(50) ) AS if exists ( SELECT AdminName FROM Admin WHERE AdminName = @AdminName AND AdminPwd = @AdminPwd ) -- 登录成功 RETURN 0 if exists ( SELECT AdminName FROM Admin WHERE AdminName = @AdminName ) -- 登录失败 密码错误 RETURN 1 -- 登录失败 用户名不正确 RETURN 2   6更新 ALTER PROCEDURE [dbo].[KDF_AdminPwd_Motify] ( @AdminName varchar(50), @AdminPwd varchar(50) ) AS Update Admin     set     AdminPwd=@AdminPwd     where AdminName=@AdminName 7多表插入 ALTER PROCEDURE [dbo].[KDF_BillInfo_Pay] ( @BillID varchar(50), @AcNumOrder0 varchar(50), @AcNumOrder1 varchar(50), @BillTape varchar(50), @PayDate datetime, @ExpirationDate datetime ) AS if @BillTape = N'年费' begin    declare @UserID varchar(50)    declare @Amount int    select @UserID = CostumerID, @Amount =BillAmount    from BillInfo    where BillID = @BillID    declare @RechargeK int    declare @RewardK int    select @RechargeK = RechargeK, @RewardK = RewardK    from KAccount where UserID = @UserID    set @RechargeK = @RechargeK - @Amount    set @RewardK = @RewardK + @Amount    update KAccount    set RechargeK = @RechargeK, RewardK = @RewardK    where UserID = @UserID       --支付账单    insert into AccountRecord    (     AcNumOrder,     UserID,     TransactionType,     AmountPaid,     AmountReceived,     DteDate    )    values    (     @AcNumOrder0,     @UserID,     N'付账',     @Amount,     0,     getdate()    )    --奖励账单    insert into AccountRecord    (     AcNumOrder,     UserID,     TransactionType,     AmountPaid,     AmountReceived,     DteDate                )    values    (     @AcNumOrder1,     @UserID,     N'预存',     0,     @Amount,     getdate()    )    --更新Bill帐单    update BillInfo    set    PayDate = @PayDate,    ExpirationDate = @ExpirationDate,         TransactionNum=@AcNumOrder0    where BillID = @BillID          end