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