Categories
Advance SQL
(20)
Interview QA
(4)
Knowledge Queries
(34)
SQL BASIC
(12)
SQL Functions
(2)
SQL Queries
(4)
Tuesday 21 May 2013
Dynamic Query in sql server
Dynamic Query
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)
3 comments:
Anonymous
22 May 2013 at 21:58
Very Good Example
Reply
Delete
Replies
Reply
Anonymous
22 May 2013 at 21:59
very helpfull example
Reply
Delete
Replies
Reply
MSSQL,SSIS Solution
22 May 2013 at 22:00
thanks
Reply
Delete
Replies
Reply
Add comment
Load more...
if you have any doubt any suggestions do comment
Newer Post
Older Post
Home
Subscribe to:
Post Comments (Atom)
Very Good Example
ReplyDeletevery helpfull example
ReplyDeletethanks
ReplyDelete