Dynamic Query in sql server

Sql server:Dynamic Query

Hi ,
         In this example we will see how to add conditions dynamically in where clause and
joins in sql statement. Lets look at an example that shows how this works.

Assume you have a table called #info,#Post,#city created with the following statement.

      Create table #info (id int,Sname varchar(50))
      Create table #Post (Post_id int, id int,post varchar(50))
      Create table #city (city_id int,id int,city varchar(100))

Add Some data in all tables by using following statements

   insert into #info(id,Sname) values(1,'sandip.')
   insert into #info(id,Sname) values(2,'sandip_SP')
   insert into #info(id,Sname) values(3,'sandip_Ak')
   insert into #info(id,Sname) values(4,'sandip_Sk')
   insert into #info(id,Sname) values(5,'sandipG')

   insert into #Post(Post_id ,id,post) values(1,1,'Software Dev')
   insert into #Post(Post_id ,id,post) values(2,2,'Database admin')
   insert into #Post(Post_id ,id,post) values(3,3,'tester')
   insert into #Post(Post_id ,id,post) values(4,4,'HR')
   insert into #Post(Post_id ,id,post) values(5,5,'Team Lead')

   insert into #city (city_id,id,city) values (1,1,'kolhapur')
   insert into #city (city_id,id,city) values (1,1,'sangli')
   insert into #city (city_id,id,city) values (1,1,'satara')
   insert into #city (city_id,id,city) values (1,1,'karad')
   insert into #city (city_id,id,city) values (1,1,'maharastra')

Here is final query to achive a dynamic conditions and joins in sql statements

    declare @main_str varchar(max)
     declare @query_str varchar(max)
     declare @InnerJoin_str varchar(max)
     declare @Where_str varchar(max)
     set @query_str =' select * from #temp a '
     set @InnerJoin_str =' inner join #post b on a.id=b.id '

     set @Where_str= 'Where a.id=4 '
     set @main_str=''
     set @main_str=@query_str+@InnerJoin_str+@Where_str
     exec(@main_str)

    --Add Dynamacally where clause in sql query

    set @Where_str=@Where_str+ ' or b.post=''Software Dev'''
    set @main_str=''
    set @main_str=@query_str+@InnerJoin_str+@Where_str
    exec(@main_str)

 --Add Dynamacally joins in sql Query

   set @InnerJoin_str=@InnerJoin_str+ ' inner join #city c on a.id=c.id '
   set @main_str=''
   set @main_str=@query_str+@InnerJoin_str+@Where_str
   exec(@main_str)