SQL(Dynamic SQL with storeprocedure)

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[ProcedureName](@Status varchar(1), @QueryBy varchar(150))

AS

--DynamicSQL

declare @SQL nvarchar(max),

@params nvarchar(4000)

SET @SQL= 'SELECT Top (@PerPage) * FROM <Table_Name> WHERE a.Status = @Status AND @QueryBy'

SELECT @params =  N'@Status varchar(1),'+

                              N'@QueryBy varchar(150)'

execute sp_executesql @SQL, @params, @Status=@Status, @QueryBy=@QueryBy