I have Table Format And record TCNo SR OANO OAID HeatNo 200/16 1 10982 2 PP012 200/16 2 10982 2 PP011 200/16 3 10982 2 PP010 200/16 4 10982 2 PP009 200/16 5 10982 2 PP013 200/16 6 10982 2 PP008 200/16 7 10982 2 PP007 OutPut Table Like TCNO SRNO OANO OAID HeatNo Pgno 200/16 1 10982 2 PP012 1 200/16 2 10982 2 PP011 1 200/16 3 10982 2 PP010 1 200/16 4 10982 2 PP009 1 200/16 5 10982 2 PP013 1 200/16 6 10982 2 PP008 2 200/16 7 10982 2 PP007 2 store procedure parameter pass TCNo And Record per page Integer value Like 5 How it possibe through store procedure

Hi , Please use this, CREATE PROC usp_GetRecordsByPageAndSize @Page INT , -- Page Numver @ Size INT , @TCNo Varchar (100) AS BEGIN SELECT TCNo,SR,OANO,OAID,HeatNo, @Page as Pgno FROM YourTableName where TCNo = @TCNo OFFSET (@Page -1) * @ Size ROWS FETCH NEXT @ Size ROWS ONLY END Exueture SP like exec usp_GetRecordsByPageAndSize 1,5, ' 200/16' -- Here page no is 1, rows is 5 and TCNo '200/16'

You can use the below link as a tutorial to create procedures with parameters https://www.w3schools.com/sql/sql_stored_procedures.asp You need to write the below query inside the procedure , where @Recordperpage is the parameter of Record per page SELECT TCNo TCNO, SR SRNO, OANO, OAID ,HeatNo, CEIL(SR/@Recordperpage) Pgno FROM Table order by SR;

Answers

store procedur in Sql Server

Manish Patat

596

I have Table Format And record

TCNo	SR	OANO	OAID	HeatNo
200/16	1	10982	2	PP012
200/16	2	10982	2	PP011
200/16	3	10982	2	PP010
200/16	4	10982	2	PP009
200/16	5	10982	2	PP013
200/16	6	10982	2	PP008
200/16	7	10982	2	PP007

OutPut Table Like

TCNO	SRNO	OANO	OAID	HeatNo	Pgno
200/16	1	10982	2	PP012	1
200/16	2	10982	2	PP011	1
200/16	3	10982	2	PP010	1
200/16	4	10982	2	PP009	1
200/16	5	10982	2	PP013	1
200/16	6	10982	2	PP008	2
200/16	7	10982	2	PP007	2

store procedure parameter pass TCNo And Record per page Integer value Like 5

How it possibe through store procedure

Answers (2)

Next Recommended Forum

Optimize sql query

varchar declaration in Work_Table