Execute Dynamic SQL commands in SQL Server

In some applications having hard-coded SQL statements is not appealing, because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET , ColdFusion or any other programming language. But how do you do this from within a SQL Server stored procedure? SQL Server offers a few ways of running a dynamically built SQL statement. These ways are: Writing a query with parameters Using EXEC Using sp_executesql Writing a query with parameters This first approach is pretty straightforward if you only need to pass parameters into the WHERE clause of your SQL statement. Let’s say we need to find all records from the Customers table where City = ‘London’. This can be done easily as the following example shows.

How To Use Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id

emp_name

emp_manager_id

1

John

Null

2

Tom

1

3

Smith

1

4

Albert

2

5

David

2

6

Murphy

5

7

Petra

5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:

manager

employee

John

Tom

John

Smith

Tom

Albert

Tom

David

David

Murphy

David

Petra


Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Comments

Dineshrajan said…
VERY USEFUL ARTICLE. THANKS MAN
Abhi "jai ho" said…
what a way of teaching.......
i m too happy, thank u very much.
Unknown said…
how would you go about including the null value in the query? i.e. if you wanted to include the fact that employee john has no manager
Unknown said…
Thank you so much.
Good explaination.

Deepak patil
Unknown said…
well... thank u ....
so use full for learner....
hi..thanks im feeling this as spoonfeeding
srsofttech said…
Hey dear,nice post..but if i want to populate data based on one condition like i want to display employees who's manager is Me and employees whose manager are those who are my employees..n similar to it..Thanks ..
kindly reply soon...
I need it urgent
Unknown said…
thanx for posting such a useful info.
Reshma said…
nice article :)
Hi Rohit,

Your scenario may be invalid since a manager cannot be a self reporting manager but even then it filters out if you use the same query
This comment has been removed by the author.
Unknown said…
Hi, I have a 'Emp' table like
Empid Fname Lname Mgrid
1 x Last x
2 First x 1
Want to join and the result should be like "First Last" in one row

Written a query like,
"Select A.Fname, B.Lname
From Emp A join Emp B
on A.Mgrid = B.Empid"

but getting the result with duplicate records 3-4 times like
First Last
First Last
First
First Last
First Last
Unknown said…
How can i remove the duplicates on my above problem? 'Distinct' still keep result with two rows like
First Last
First
Unknown said…
Hi, I have a 'Emp' table like
Empid Fname Lname Mgrid
1 x Last x
2 First x 1
Want to join and the result should be like "First Last" in one row

Tried with the query like,
"Select Distinct A.Fname, B.Lname
From Emp A join Emp B
on A.Mgrid = B.Empid"
or
"Select Distinct A.Fname, B.Lname
From Emp A, Emp B
where A.Mgrid = B.Empid"
or
"Select Distinct A.Fname, B.Lname
From Emp A
Left join Emp B on A.Mgrid = B.Empid"

but getting the results like,
First Last
First

Can any one help in removing the second row from the results?
Unknown said…
very nice artical thaks a lot
Unknown said…
Useful article to learn self join clearly, Thanks dear .............

by
Gunnas
R.B. Prajapati said…
Thanks you so much dear.this is good explanation.and hope that give other query like it
Unknown said…
Hi lot of thanks for bring the valuable information



BY
Siddu
Unknown said…
nice explanation, thanks man
swapna c said…
Nice explanation
kaku said…
can any one please tell me what are the usages of this Self join as i am new to this field..when and where what are its advantages and limitations
adi grandhi said…
hi vinay singh

i solved you are query like as follows

select d.fname+' '+d1.lname from dummy as d join dummy as d1 on d.mgrid=d1.empid

Results like this as,
"FirstLast"
Anonymous said…
Good job continue
Ramu alagappan said…
Thanks for giving such as useful article. Thanks a lot.
papia said…
thanks....i have to take class of 3rd yr students tomorrow on this ...
thank you vary much....
Ramesh said…
This comment has been removed by the author.
Ramesh said…
Tank u vry vry much 4 ur clr xplanation.....:-):)
Pradeep said…
thank u very much ........
Good artical for understand meaning of self join ...

thanx once again...
lalita singh said…
thnxxxxxxxxxxxx a lot of this
lalita singh said…
u no finished ma self join problem
kalaivendan said…
Very useful article.. Thanks...
yusuf said…
THANK YOU SOOOOOO Much
Unknown said…
i didnt understand e1 and e2 you have taken, iam a beginner of sql server
can u explain me plz...with an example...
J4H said…
Really its use full for learners, done good job......

e1 and e2 are the objects for the EMP table, we can use same table for the self join na, so we should declare like and use them.

Thanks,
JJ
Siddharth said…
really nice article, useful very very useful and understandable
* Sql Introduction
* Using Stored Procedures
* sql queries
* groupby functions etc
* Joins

Complete explanation

Link here for Sql Tutorial
http://geeksprogrammings.blogspot.in/search/label/StructuredQueryLanguage%28SQL%29

Link here for joins explanation
geeksprogrammings.blogspot.in/2013/06/joins-in-sql.html

Popular posts from this blog

Check If Temporary Table Exists

Multiple NULL values in a Unique index in SQL

Row To Column