Initial knowledge of SQL Server stored procedures

The previous company did not use the stored procedure to do the project, so the niche of the call to the stored procedure, use is also smattering, just the company has a large number of stored procedures

This time, in order to maintain a consistent style, it is also necessary to use stored procedures to implement dynamic SQL and data paging

Next up to see how to achieve it ( small white One, do not like to spray, please tap ) ~

Call a stored procedure (where condition is the foreground incoming stitching query condition, parameters[4] is a sort field)

Stored Procedure Implementation

is Use [EPMS]GO/** * * * object:storedprocedure [dbo].    [Sp_getcollectionmanage] Script DATE:2016/9/14 10:14:00 zhangxiaoyong******/SETAnsi_nulls onGOSETQuoted_identifier onGOALTER PROCEDURE [dbo].[Sp_getcollectionmanage]     @PageSize  INT,--Page Size     @pageCurrentIndex INT,--Current Page     @condition nvarchar(MAX),--Query Criteria     @pageCount INTOut,--number of current page contents     @orderStr  nvarchar(MAX)--Sort Fields as begin/*Get Record Count*/Declare @RecordCount int/*Calculate page Data*/Declare @SqlMain nvarchar(4000)Declare @SqlStr nvarchar(4000)/*The total number of data that SQL SERVER gets to the query*/Set @SqlMain='Select @CountTemp =count (1) from (select Ef.id, E.dictdata_name, Ef.city_id,ef.receive_time,ef.receive_amount, Ef.batch_id,ef.city__manager,ef.contract_id,row_number () Over (order by'+@orderStr+'desc) As SerialNumber from V_city e left joins collection_confirmation ef on e.id = ef.city_id where'+@condition+'As T left joins contract FA on t.contract_id = Fa.id left joins V_employeedutymerger show on T.dictdata_name=show.cit Yname and Show.dutyname="'City Manager" ";execsp_executesql@SqlMainN'@CountTemp int Output',@pageCountOutput--Important ImplementationDeclare @beginIndex  as varchar( -)Declare @endIndex  as varchar( -)--Define a start number, and a number of ends, for pagingSet @beginIndex=(@pageCurrentIndex-1)*@PageSize+1    Set @endIndex=@pageCurrentIndex*@PageSize/*SQL SERVER executes the query and pagination output*/Set @SqlStr='Select T.id, T.dictdata_name, t.receive_time,t.receive_amount,t.batch_id, Fa.contract_name,show.name from ( Select Ef.id, E.dictdata_name, Ef.city_id,ef.receive_time,ef.receive_amount,ef.batch_id,ef.city__manager, Ef.contract_id,row_number () Over (order by'+@orderStr+'desc) As SerialNumber from V_city e left joins collection_confirmation ef on e.id = ef.city_id where'+@condition+'As T left joins contract FA on t.contract_id = Fa.id left joins V_employeedutymerger show on T.dictdata_name=show.cit Yname and Show.dutyname="'City Manager"'where t.serialnumber>='+cast(@beginIndex  as varchar( -) )+ 'and T.serialnumber <='+ cast(@endIndex  as varchar( -)) --Print (@pageCurrentIndex * @PageSize)--Print @SqlCount1--Print @SqlStrexec(@SqlStr)End--execute the stored procedureDeclare @pageCount int;exec [dbo].[Sp_getcollectionmanage]  the,1,'ef.city_id in ()',@pageCountOutput'ef.id'

stored Procedure Implementation

Foreground effect

First page

Second page

Note: This stored procedure also has a lot of places to optimize, for reference only, welcome to provide valuable comments ~

End

Initial knowledge of SQL Server stored procedures