SQL Server stored procedures (additional deletions)

* Ident_current returns the identity value that was last generated for any session and for a particular table in any scope.

CREATE PROCEDURE [dbo]. [Pr_newsaffiche_addnewsentity] (    @NewsTitle varchar,    @NewsContent varchar (4000),    @Creator varchar,    @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

Stored procedure method body definition and assignment:

DECLARE @recordCount intset @recordCount =0

SQL Server stored procedures use out to pass parameters

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

Increase:

Stored Procedures

Use [testdb]go/****** object:  StoredProcedure [dbo].[ Pr_queuenewsaffiche_addqueuenews]    Script Date: 11/01/2013 15:40:56 ******/set ansi_nulls ongoset QUOTED_IDENTIFIER ongocreate proc [dbo]. [Pr_queuenewsaffiche_addqueuenews] (@Content nvarchar, @NewsAfficheId int, @BeginTime datetime, @EndTime datetime, @Creator varchar, @CreateTime datetime, @Updator varchar (@UpdateTime datetime) as    INSERT into Tbqueuenewsaffiche ([Content],newsafficheid, Begintime,endtime,creator,createtime,updator,updatetime) VALUES (@Content, @NewsAfficheId, @BeginTime, @EndTime, @ Creator,getdate (), @Updator, GETDATE ());

corresponding to the DAL layer backend code:

<summary>//Add///</summary>//<param name= "Newsinfo" ></param> <returns></returns> public int Add (Queuenewsaffiche_newsinfo newsinfo) {Idatap                arameter[] ParamArray = new idataparameter[]{db.getparameter ("@Content", dbtype.string,newsinfo.content), Db.getparameter ("@NewsAfficheId", Dbtype.int32,newsinfo.newsafficheid), Db.getparameter ("@Begi                Ntime ", Dbtype.datetime,newsinfo.begintime), Db.getparameter (" @EndTime ", Dbtype.datetime,newsinfo.endtime), Db.getparameter ("@Creator", Dbtype.string,newsinfo.creator), Db.getparameter ("@CreateTime", DbT Ype. Datetime,newsinfo.createtime), Db.getparameter ("@Updator", Dbtype.string,newsinfo.updator), D            B.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 ("error adding" + e.tostring ());        } return returnvalue; }

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

Delete:

Stored Procedures

Use [testdb]go/****** object:  StoredProcedure [dbo].[ Pr_queuenewsaffiche_delete]    Script Date: 11/01/2013 15:46:12 ******/set ansi_nulls ongoset quoted_identifier ONGOCREATE PROCEDURE [dbo]. [Pr_queuenewsaffiche_delete]  (@Id int, @Result int output)//@Result output Asbegin    if EXISTS (select 1 from Tbqueuenewsaffiche//Determine if there is data select top 1 from Tbqueuenewsaffiche WHERE id = @Id   not present with NOT exists            WHERE id = @Id)    BEGIN        DELETE from Tbqueuenewsaffiche
   where Id = @Id        set @Result =1;    END    Else     begin    set @Result =0;    EndEnd

Corresponds to the DAL layer code:

        <summary>//delete///</summary>//        <param name= "id" ></param>//        < returns></returns> public        int Deletebyid (int id)        {            idataparameter[] ParamArray = new idataparameter[]{                db.getparameter ("@Result", dbtype.int32,parameterdirection.output),  //output parameter                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 ("delete failed" + ex.) ToString (), 0, 0, 0);                return 0;            }        }

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

Modify:

Stored procedures:

Use [testdb]go/****** object:  StoredProcedure [dbo].[ Pr_queuenewsaffiche_update]    Script Date: 11/04/2013 10:54:53 ******/set ansi_nulls ongoset quoted_identifier ONGOCREATE PROCEDURE [dbo]. [Pr_queuenewsaffiche_update] (@Id int, @Content nvarchar, @BeginTime datetime, @EndTime datetime, @Updator varchar (50)) Asbegin    SET NOCOUNT on;  This sentence to note: The role is not to return the number of rows affected, generally do not set the update, easy to get to the number of rows returned to determine whether the update is successful.     update Tbqueuenewsaffiche    set         [Content][email protected],        [email protected],        [email Protected],        [email protected],        updatetime=getdate ()    where [email protected]end

Corresponds to the DAL layer code:

        <summary>///update//</summary>/<param name= "id" > Queued news ID&LT;/PARAM&G        T <param name= "Content" ></param>//<param name= "BeginTime" ></param>//<para M name= "EndTime" ></param>//<param name= "Updator" ></param>//<returns></re            turns> 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 ("error on update {pr_newsaffichequeue_update}" + Ex.            ToString ());        } return returnvalue; }

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

Inquire:

Stored Procedures

Use [testdb]go/****** object:  StoredProcedure [dbo].[ Pr_queuenewsaffiche_getallnews]    Script Date: 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 [e-mail protected] ORDER by Begintimeend

Corresponding DAL code:

       <summary>///Check all queued news///</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_geta                    Llnews ", 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 ("Error in Query" + ex.            ToString ());        } return list; }

Query by ID

Use [testdb]go/****** object:  StoredProcedure [dbo].[ Pr_queuenewsaffiche_getnewsbyid]    Script Date: 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

Corresponds to the DAL layer code:

        <summary>/////</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_getn Ewsbyid ", 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 ("error querying queued News {pr_newsaffic Hequeue_getnewsbyid} "+ ex.            ToString ());        } return newsinfo; }

Execute SQL statement in stored procedure:

Use [booksale]go/****** object:  StoredProcedure [dbo].[ SP_SALEBOOKCUSTOMADDRESS_GETCUSTOMADDRESSBYUSERIDLIST_1_19882]    Script Date: 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

The DAL obtains the contents of the stored procedure above:

<summary>///Bulk get list of address entities based on multiple UserID//</summary>///<param name= "Useridlist" ></param>/// <returns></returns>public list<entity.activity.salebookcustomaddress> Getactivityrosterbyuseridlist (String useridlist) {idataparameter[] par = new idataparameter[] {adohelper.ge    Tparameter ("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 (read                Er, "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;}

Piecemeal additions:

 @AboutTheAuthor varchar (max), @RMBOriginPrice decimal (18,2),/* Represents a total of 18 digits, including a 2-bit decimal point (The integer part is 16 bits) */decimal >& gt; @AuthorName varchar (100) = ",/* parameter Assignment Initial value */@RecordCount int=0 output//outputs variable assigned initial value */select @RecordCount =count (1) from Sal EBook where companyid=17as/* represents the following for the body part of the stored procedure *//*@@ 表示 a global variable (built-in system variable), gets the latest primary key ID SELECT @ @rowcount the number of rows affected */select @ @identity EXEC (@[email protected]+ ' ORDER by Createtime DESC ')/* Execute SQL statement */with Mem_salebook_book as/*with usage */(SELECT Booki D from Bookview V WHERE companyid in (select Item from Dbo.fn_split (@CompanyId, ', ')) and bookid= case @SearchT ype when ' BookID ' then @SearchValue ELSE bookid END/*case ... When syntax */and bookname like case @SearchType if ' bookname ' then @SearchValue ELSE bookname END) SELECT m.*, SC. CompanyName as CompanyName from Mem_salebook_book mm with (nolock) inner joins Bookview M on MM. Bookid=m.bookidleft join Salecompany SC on M.COMPANYID=SC. CompanyID 

Navigation inside the station:

SQL Server stored Procedure instance explained

Off-site expansion:

SQL Server Functions Daquan

SQL Server stored procedures (additional deletions)