create the Store Procedure create Dynamic Query and Paging

-

Here we Create Store Procedure For the Create the Dynamic  Query and also paging and also insert data when page no is first



Create Procedure Gia_Search(
@Shape varchar(64)='',
@color Varchar(64)='',
@purity varchar(64)='',
@polish varchar(64)='',
@cut Varchar(64)='',
@pricemax varchar(50)='',
@pricemin varchar(50)='',
@caratMax varchar(50)='',
@caratmin varchar(50)='',
@symm varchar(64)='',
@fls varchar(64)='',
@userid varchar(64)='',
@PageNumber varchar(50)=0,
@PageSize Varchar(50)=0
)As

Begin

  SET NOCOUNT ON;
 Declare @Search varchar(max)='', @wherclause varchar(max)=''

 if @PageNumber='1'
     begin
          insert into gia_search_history (user_id,shape_seq,color_seq,purity_Seq,Polish_seq,Cut_Seq,Symm_seq,Fls_Seq,Carats_Max,Carats_Min,Price_max,Price_Min)
     values (@userid,@Shape,@color,@purity,@polish,@cut,@symm,@fls,@caratMax,@caratmin,@pricemax,@pricemin)

     end 

    set @Search ='select  S.*,SM.Name as shape,CM.Name as Color,PM.Name as Purity,POM.Name As Polish,CUM.Name as Cut,SYM.Name As Symm,Fm.Name as FLS from stock as S Inner join SHAPE_MAS As SM  on SM.SEQ_NO=S.shape_seq inner join COLOR_MAS As CM on CM.SEQ_NO=S.color_seq inner join PURITY_MAS As PM on PM.SEQ_NO=S.PURITY_SEQ inner join POLISH_MAS AS POM on POM.SEQ_NO=S.polish_seq inner join CUT_MAS As CUM on CUM.SEQ_NO=S.cut_seq inner join FLS_MAS As FM on FM.SEQ_NO=S.FLS_Seq inner join SYMM_MAS as SYM on SYM.SEQ_NO=S.SYMM_SEQ'
      
    set @wherclause=' where S.STONE_TYPE =1 and(cts BETWEEN '+@caratmin+' and '+@caratMax+') and (Rate BETWEEN '+@pricemin+' and '+@pricemax+')'
    if (@Shape!='')
    Begin
      set @wherclause +=' and  S.shape_seq in ('+@Shape+') '
    End
      
       if (@color!='')
    Begin
      set @wherclause += ' and S.color_seq in ('+@color+')'
    End

    if (@purity!='')
    Begin
            set @wherclause +=' and S.purity_seq in ('+@purity+')'
    end

    if (@cut!='')
    Begin
      set @wherclause +=' and S.cut_seq in ('+@cut+')'
    end

    if (@symm!='')
    Begin
               set @wherclause +=' and S.symm_seq in ('+@symm+')'
    end

    if (@fls!='')
    Begin
              set @wherclause +=' and S.Fls_Seq in ('+@fls+')'
    end

              set @wherclause=+' order by S.SEQ_NO Asc OFFSET  '+@PageSize+' * ('+@PageNumber+' - 1) ROWS  FETCH NEXT  '+PageSize+' ROWS ONLY'
    set @Search+=@wherclause -- Concatenate all Query

     exec(@Search)   -- also Call  exec sp_executesql @Search



End

Comments

Post a Comment

Popular posts from this blog

How To Migrate MVC 3 Application To MVC 5

-
Image
Introduction In this article we discuss about how to migrate an MVC 3 application to MVC 5. Step 1:  Open your MVC 3 application in Visual Studio and change the .net framework to 4.7.2 as MVC 5 runs on this framework. MVC 3 Project Property Project → Properties → Application → Target Framework → 4.7.2 For Set MVC 5  Note:  targetFramework in Web.Config will be changing to 4.7.2 Step 2:  Install Asp.Net MVC 5 version through Package Manager Console. Run this Command: Install-Package Microsoft.AspNet.Mvc -Version 5.7.2 Nuget Package Manage → Package Manager Console and run above command. Step 3:  Go to Web.Config(root level) and change, <add key="webpages:Version" value="1.0.0.0" /> to <add key="webpages:Version" value="3.0.0.0" /> webpages:Version Step 4:  Check System.Web.Razor, System.Web.Helpers, System.WebPages.Razor versions. These all should be having versions 3.0.0.0. If th...
Read more

Populate a drop-down in Vue.js and Asp.net Core from an ajax call

-
Image
  If you want to call a function on page load and Bind Drop down  List in Vue js then in this example i will show you how to trigger function on page load in vue js. we will run function on page load vue application. we mostly require to call method on page load in our application. so you want to call function in Vue.js app then you can do it using created option in vue js. So i will give full example so you can check it out. Index . cshtml < div id ="apps">     < div class ="form-group">         < label class ="control-label"> State </ label >         < select v-model ="state" class ="form-control" v-on:change ="getcity($event)">             < option v-for ="state in States" : value ="state.stateId"> {{ state.stateName }} </ option >       ...
Read more

Building a CRUD Application with Ag-Grid

-
Image
To Create Crud operation In Ag-Grid using C# MVC here Is a Download Link  Click here.. Bundle Config bundles.Add( new ScriptBundle( "~/bundles/jquery" ).Include(                          "~/Scripts/jquery-1.12.4.js" ,                         "~/Scripts/jquery-ui-1.12.1.js" ,                         "~/Scripts/bootstrap.js" ,                           "~/Scripts/jquery.validate*" ,                     ...
Read more