dynamic query store procedure in sql





Create PROCEDURE [dbo].[Prc_SearchMembers]
(@MemberName varchar(100)=null,@MemberId varchar(20)=null,@IntroId varchar(20)=null,@Mobile Varchar(10)=null,@Email varchar(100)=null,@LevelNo int=null,@Deactive bit=null,@Export bit=null)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Query nvarchar(4000),@Condition nvarchar(4000),@Msrno int
DECLARE @ParmDefinition NVARCHAR(500);
--select @Msrno=Msrno from MemberMaster where MemberId=@MemberId
set @Condition='where 1=1 '
if @MemberName is not null
set @Condition=@Condition + ' and MemberName Like @MName'
if @MemberId is not null
set @Condition=@Condition + ' and m.MemberId=@Mid'
if @IntroId is not null
set @Condition=@Condition + ' and IntroId=@Intro'
if @LevelNo is not null
set @Condition=@Condition + ' and LevelNo=@Act'
if @Mobile is not null
set @Condition=@Condition + ' and Mobile=@Mob'
if @Email is not null
set @Condition=@Condition + ' and Email=@Mail'
if @Deactive is not null
set @Condition=@Condition + ' and deactivated is not null '
if @Export=1
  set @Query='Select m.Memberid,MemberName,DOJ,IntroId,(select MemberName from MemberMaster where MemberId=m.IntroId) IntroName,Mobile,Email from MemberMaster m inner join Membertree t on t.msrno=m.msrno '+ @Condition
else
set @Query='Select m.Msrno,m.Memberid,MemberName,DOJ,IntroId,(select MemberName from MemberMaster where MemberId=m.IntroId) IntroName,Mobile,Email from MemberMaster m inner join Membertree t on t.msrno=m.msrno '+ @Condition
SET @ParmDefinition = N'@MName varchar(100),@Mid varchar(20),@Intro varchar(20),@Act int,@Mob varchar(10),@mail varchar(100)';
exec sp_executeSql @Query,@ParmDefinition,@MName=@MemberName,@Mid=@MemberId,@Intro=@IntroId,@Act=@LevelNo,@Mob=@Mobile,@Mail=@Email

END

Featured Post

Sql dynamic query With Join And search using like query with multiple column

Sql dynamic query With Join And search using like query with multiple column declare @keyword varchar(100) DECLARE @sqlCommand va...

Popular Posts