SqlServer預存程序（增刪改查）

標籤：

* IDENT_CURRENT 返回為任何會話和任何範圍中的特定表最後產生的標識值。

CREATE PROCEDURE [dbo].[PR_NewsAffiche_AddNewsEntity] (    @NewsTitle varchar(200),    @NewsContent varchar(4000),    @Creator varchar(50),    @LastNewsId int output,    @DepartId int)ASBEGIN    SET NOCOUNT ON;    insert into tbNewsAffiche(Title,Content,Creator,CreateTime,Updator,UpdateTime,DepartId)    values(@NewsTitle,@NewsContent,@Creator,getdate(),@Creator,getdate(),@DepartId)        set @LastNewsId = IDENT_CURRENT(‘tbNewsAffiche‘)END

預存程序方法體內定義及賦值：

declare @recordCount intset @recordCount=0

 

SqlServer預存程序使用out傳遞出參數 

====================================

增加：

預存程序

USE [testdb]GO/****** 對象:  StoredProcedure [dbo].[PR_QueueNewsAffiche_AddQueueNews]    指令碼日期: 11/01/2013 15:40:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [dbo].[PR_QueueNewsAffiche_AddQueueNews](@Content nvarchar(500),@NewsAfficheId int,@BeginTime datetime,@EndTime datetime,@Creator varchar(50),@CreateTime datetime,@Updator varchar(50),@UpdateTime datetime)as    insert into tbQueueNewsAffiche([Content],NewsAfficheId,BeginTime,EndTime,Creator,CreateTime,Updator,UpdateTime) values(@Content,@NewsAfficheId,@BeginTime,@EndTime,@Creator,getdate(),@Updator,getdate());

 對應DAL層後台代碼：

/// <summary>        /// 添加        /// </summary>        /// <param name="newsInfo"></param>        /// <returns></returns>        public int Add(QueueNewsAffiche_NewsInfo newsInfo)        {            IDataParameter[] paramArray = new IDataParameter[]{                Db.GetParameter("@Content",DbType.String,newsInfo.Content),                Db.GetParameter("@NewsAfficheId",DbType.Int32,newsInfo.NewsAfficheId),                Db.GetParameter("@BeginTime",DbType.DateTime,newsInfo.BeginTime),                Db.GetParameter("@EndTime",DbType.DateTime,newsInfo.EndTime),                Db.GetParameter("@Creator",DbType.String,newsInfo.Creator),                Db.GetParameter("@CreateTime",DbType.DateTime,newsInfo.CreateTime),                Db.GetParameter("@Updator",DbType.String,newsInfo.Updator),                Db.GetParameter("@UpdateTime",DbType.DateTime,newsInfo.UpdateTime)        };            int returnValue = 0;            try            {                returnValue = Db.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PR_QueueNewsAffiche_AddQueueNews", paramArray);            }            catch (System.Exception e)            {                LogHelper.Error("添加時出錯" + e.ToString());            }            return returnValue;        }

=======================================

刪除：

預存程序

USE [testdb]GO/****** 對象:  StoredProcedure [dbo].[PR_QueueNewsAffiche_Delete]    指令碼日期: 11/01/2013 15:46:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[PR_QueueNewsAffiche_Delete](@Id int,@Result int output) //@Result為輸出ASBEGIN    IF EXISTS(SELECT 1 FROM tbQueueNewsAffiche //判斷是否有資料SELECT top 1 FROM tbQueueNewsAffiche WHERE Id = @Id   不存在用not exists            WHERE Id = @Id)    BEGIN        DELETE FROM tbQueueNewsAffiche        WHERE Id = @Id        set @Result=1;    END    else     begin    set @Result =0;    endEND

對應DAL層代碼：

        /// <summary>        /// 刪除        /// </summary>        /// <param name="id"></param>        /// <returns></returns>        public int DeleteById(int id)        {            IDataParameter[] paramArray = new IDataParameter[]{                Db.GetParameter("@Result",DbType.Int32,ParameterDirection.Output),  //輸出參數                Db.GetParameter("@Id",DbType.Int32,id)            };            try            {                int effectedRows = Db.ExecuteSPNonQuery(ConnectionString, "PR_QueueNewsAffiche_Delete", paramArray);                int result = Field.GetOutPutParam(paramArray[0], 0);                return result;            }            catch (System.Exception ex)            {                Log.WriteUserLog("刪除失敗" + ex.ToString(), 0, 0, 0);                return 0;            }        }

 ================================

修改：

預存程序：

USE [testdb]GO/****** 對象:  StoredProcedure [dbo].[PR_QueueNewsAffiche_Update]    指令碼日期: 11/04/2013 10:54:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[PR_QueueNewsAffiche_Update] (@Id int,@Content nvarchar(500),@BeginTime datetime,@EndTime datetime,@Updator varchar(50))ASBEGIN    SET NOCOUNT ON;  這一句要注意：作用是不返回受影響的行數，一般做更新的不要設定，方便擷取到返回的行數來判斷是否更新成功。    update tbQueueNewsAffiche    set         [Content][email protected],        [email protected],        [email protected],        [email protected],        UpdateTime=getdate()    where [email protected]END

對應DAL層代碼：

        /// <summary>        /// 更新        /// </summary>        /// <param name="id">排隊新聞id</param>        /// <param name="content"></param>        /// <param name="beginTime"></param>        /// <param name="endTime"></param>        /// <param name="updator"></param>        /// <returns></returns>        public int Update(int id,string content,DateTime beginTime,DateTime endTime,string updator)        {            IDataParameter[] paramArray = new IDataParameter[] {             Db.GetParameter("@Id",DbType.Int32,id),            Db.GetParameter("@Content",DbType.String,content),            Db.GetParameter("@BeginTime",DbType.DateTime,beginTime),            Db.GetParameter("@EndTime",DbType.DateTime,endTime),            Db.GetParameter("@Updator",DbType.String,updator)            };            int returnValue = 0;            try            {                returnValue = Db.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "PR_NewsAfficheQueue_Update", paramArray);            }            catch (System.Exception ex)            {                LogHelper.Error("更新時出錯{PR_NewsAfficheQueue_Update}" + ex.ToString());            }            return returnValue;        }

 

=================================

查詢：

預存程序

USE [testdb]GO/****** 對象:  StoredProcedure [dbo].[PR_QueueNewsAffiche_GetAllNews]    指令碼日期: 11/01/2013 15:53:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [dbo].[PR_QueueNewsAffiche_GetAllNews](@NewsAfficheId int)asbegin    SET NOCOUNT ON;    select         Id,        [Content],        NewsAfficheId,        BeginTime,        EndTime,        Creator,        CreateTime,        Updator,        UpdateTime    from dbo.tbQueueNewsAffiche where [email protected] order by BeginTimeend

對應DAL代碼：

       /// <summary>        /// 查詢所有排隊新聞        /// </summary>        /// <returns></returns>        public List<QueueNewsAffiche_NewsInfo> GetList(int newsAfficheId)        {            IDataParameter[] paramArray = new IDataParameter[]{                Db.GetParameter("@NewsAfficheId",DbType.Int32,newsAfficheId)            };            List<QueueNewsAffiche_NewsInfo> list = null;            try            {                using (IDataReader reader = Db.ExecuteSPReader(ConnectionString, "PR_QueueNewsAffiche_GetAllNews", paramArray))                {                    list = new List<QueueNewsAffiche_NewsInfo>();                    while (reader.Read())                    {                        QueueNewsAffiche_NewsInfo newsInfo = new QueueNewsAffiche_NewsInfo();                        IDataRecord rec = reader as IDataRecord;                        newsInfo.Id = Field.GetInt32(rec, "Id");                        newsInfo.Content = Field.GetString(rec, "Content");                        newsInfo.NewsAfficheId = Field.GetInt32(rec, "NewsAfficheId");                        newsInfo.BeginTime = Field.GetDateTime(rec, "BeginTime");                        newsInfo.EndTime = Field.GetDateTime(rec, "EndTime");                        newsInfo.Creator = Field.GetString(rec, "Creator");                        newsInfo.CreateTime = Field.GetDateTime(rec, "CreateTime");                        newsInfo.Updator = Field.GetString(rec, "Updator");                        newsInfo.UpdateTime = Field.GetDateTime(rec, "UpdateTime");                        list.Add(newsInfo);                    }                }            }            catch (System.Exception ex)            {                LogHelper.Error("查詢時出錯" + ex.ToString());            }            return list;        }

 

根據id查詢

USE [testdb]GO/****** 對象:  StoredProcedure [dbo].[PR_QueueNewsAffiche_GetNewsById]    指令碼日期: 11/04/2013 13:44:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[PR_QueueNewsAffiche_GetNewsById](    @Id int)ASBEGIN    SET NOCOUNT ON;    select *     from tbQueueNewsAffiche     where [email protected]END

對應DAL層代碼：

        /// <summary>        /// 根據id查詢        /// </summary>        /// <param name="Id"></param>        /// <returns></returns>        public QueueNewsAffiche_NewsInfo GetQueueNewsById(int id)        {            IDataParameter[] paramArray = new IDataParameter[] {             Db.GetParameter("@Id",DbType.Int32,id)            };            QueueNewsAffiche_NewsInfo newsInfo = null;            try            {                using (IDataReader reader = Db.ExecuteSPReader(ConnectionString, "PR_NewsAfficheQueue_GetNewsById", paramArray))                {                    while (reader.Read())                    {                        newsInfo = new QueueNewsAffiche_NewsInfo();                        IDataRecord rec = reader as IDataRecord;                        newsInfo.Content = Field.GetString(rec, "Content");                        newsInfo.BeginTime = Field.GetDateTime(rec, "BeginTime");                    }                                    }            }            catch (System.Exception ex)            {                LogHelper.Error("查詢排隊新聞時出錯{PR_NewsAfficheQueue_GetNewsById}" + ex.ToString());            }            return newsInfo;        }

 

預存程序中執行SQL語句：

USE [BookSale]GO/****** 對象:  StoredProcedure [dbo].[SP_SaleBookCustomAddress_GetCustomAddressByUserIdList_1_19882]    指令碼日期: 01/16/2014 15:30:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        -- Create date: -- Description:    -- =============================================CREATE procedure [dbo].[SP_SaleBookCustomAddress_GetCustomAddressByUserIdList_1_19882](    @UserIdList        nvarchar(max))asbeginexec(‘select * from SaleBookCustomAddress where UserId in (‘[email protected]+‘) and Status=1‘) end

DAL擷取上面預存程序獲得的內容：

/// <summary>/// 根據多個UserId批量擷取地址實體列表/// </summary>/// <param name="userIdList"></param>/// <returns></returns>public List<Entity.Activity.SaleBookCustomAddress> GetActivityRosterByUserIdList(string userIdList){    IDataParameter[] par = new IDataParameter[] {            AdoHelper.GetParameter("UserIdList",DbType.String ,userIdList )    };    List<SaleBookCustomAddress> rtn = new List<SaleBookCustomAddress>();    try    {        using (IDataReader reader = AdoHelper.ExecuteReader(this.DefaultConnectionString, CommandType.StoredProcedure, "SP_SaleBookCustomAddress_GetCustomAddressByUserIdList_1_19882", par))        {            while (reader.Read())            {                SaleBookCustomAddress address = new SaleBookCustomAddress();                address.UserId = Convert.ToInt64(reader["UserId"]);                UserDataAccess userDB = new UserDataAccess();                Users user = userDB.GetUserNickname(new int[] { Convert.ToInt32(address.UserId) })[0];                if (user != null)                {                    address.NickName = user.UserName;                }                else                {                    address.NickName = "";                }                address.CustomName = Field.GetString(reader, "CustomName");                address.Region = Field.GetString(reader, "Region");                address.Province = Field.GetString(reader, "Province");                address.City = Field.GetString(reader, "City");                address.Street = Field.GetString(reader, "Street");                address.Postcode = Field.GetString(reader, "Postcode");                address.MobileNo = Field.GetString(reader, "MobileNo");                address.FullTelNum = Field.GetString(reader, "TeleArea") + "-" + Field.GetString(reader, "Telephone") + "-" + Field.GetString(reader, "TeleExt");                if (address.FullTelNum == "--")                {                    address.FullTelNum = "";                }                else                {                    if (string.IsNullOrEmpty(Field.GetString(reader, "TeleExt")))                    {                        address.FullTelNum = Field.GetString(reader, "TeleArea") + "-" + Field.GetString(reader, "Telephone");                    }                }                address.LastUpdateTime = Field.GetDateTime(reader, "LastUpdateTime");                rtn.Add(address);            }        }    }    catch (Exception ex)    {        Log.LogException(ex);    }    return rtn;}

 

 零碎補充：

@AboutTheAuthor varchar(max),@RMBOriginPrice    decimal(18,2), /*表示一共18位元字，其中包括2位小數點(整數部分則為16位)*/   decimal詳解>>@AuthorName   varchar(100)=‘‘, /*參數賦初值 */@RecordCount   int=0 output /*賦初值的輸出變數 */select @RecordCount=count(1) from SaleBook where companyid=17AS /*表示下面的為預存程序主體部分*//*@@表示全域變數（內建系統變數）、擷取最新的主鍵id select @@rowcount受影響的行數 */SELECT @@identity exec(@[email protected]+‘ ORDER BY CreateTime DESC‘) /*執行sql語句*/WITH Mem_SALEBOOK_Book AS /*WITH的用法*/(  SELECT bookId    FROM bookView V    WHERE  CompanyId in ( select Item from dbo.fn_Split(@CompanyId,‘,‘) )    and BookId= CASE @SearchType WHEN ‘bookid‘ THEN @SearchValue ELSE BookId END /*CASE...WHEN文法 */    and BookName like CASE @SearchType WHEN ‘bookname‘ THEN @SearchValue ELSE BookName  END)SELECT M.*, SC.CompanyName as CompanyName FROM     Mem_SALEBOOK_Book MM with (nolock)inner join bookView M on MM.BookId=M.BookIdleft join SaleCompany SC on M.CompanyId=SC.CompanyId

 

站內導航：

SqlServer預存程序執行個體講解

 

站外擴充：

sqlserver函數大全

 

SqlServer預存程序（增刪改查）