MS Sql server

1. Execute output param in procedure in sql server 

declare @RecordCount int=1

exec [Getcustomers_pager] @topup='0',@RecordCount=@RecordCount output

print @RecordCount

2. Define out parametered in procedure in sql server

CREATE PROCEDURE [dbo].[Getcustomers_pager] @SearchTerm  VARCHAR(100) = '', 

@PageIndex   nvarchar(max) = 1, 

@PageSize    nvarchar(max) = 100,

@mobile      NVARCHAR(max)='', 

@search      INT=0,

@fromdate   nvarchar(max)='',

@todate      nvarchar(max)='',

@topup varchar(10)= '',

@RecordCount INT output 

AS 

BEGIN 

SET nocount ON; 

declare @query nvarchar(max)='',@query1 nvarchar(max)=''

set @query = 'select row_number() over(order by m.id desc) as RowNumber,

case m.status when 1 then ''Active'' else ''Block'' end as blockstatus,

case m.status when 1 then ''success'' else ''danger'' end as blockclass,

case m.status when 1 then ''Block'' else ''Active'' end as blockbtn,

case m.status when 1 then ''danger'' else ''success'' end as blockbtnclass,

format(m.edate,''dd/MM/yyyy'') as date,format(m.top_update,''dd/MM/yyyy'') as top_update1,

(select  max(remark) from cto_achievers where status=1 and uid=m.uid) rank_name,

m.*,l.password,l.trans_password

from Joinnow m with(nolock) inner join login l on m.uid=l.uid 

where l.block=0'

set @query1='select @count= count(m.id) from Joinnow m with(nolock) inner join login l on m.uid=l.uid where l.block=0'

IF(@topup != '')

BEGIN

set @query=@query+' and m.top_up='''+@topup+''''

set @query1=@query1+' and m.top_up='''+@topup+''''

END

ELSE IF(@SearchTerm !='')

BEGIN

set @query=@query+' and m.user_id='+@SearchTerm+' OR l.password ='''+@SearchTerm+''''

set @query1=@query1+' and m.user_id ='+@SearchTerm+' OR l.password='''+@SearchTerm+''''

END

 

ELSE IF(@fromdate !='' or @todate != '')

BEGIN

if(@fromdate != '')

begin

set @query=@query+' and cast(m.edate as date) >= cast('''+@fromdate+''' as date)'

set @query1=@query1+' and cast(m.edate as date) >= cast('''+@fromdate+''' as date)'

end

if(@todate != '')

begin

set @query=@query+' and cast(m.edate as date)<= cast('''+@todate+''' as date)'

set @query1=@query1+' and cast(m.edate as date)<= cast('''+@todate+''' as date)'

end 

END

set @query=@query+' order by m.id desc offset ('+@PageIndex+'-1)*'+@PageSize+' rows fetch next '+@PageSize+' rows only'

exec(@query)

exec sp_executesql @query1,N'@count int output',@count=@RecordCount output

END