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

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

how to find n number of nodes have child or cildren

 how to find n number of nodes have child or cildren for that we use recursive function  const   data = {     'id' : '0' ,...

Popular Posts