You must Sign In to post a response.
  • Category: SQL Server

    How to write stored procedure for joinning two tables

    hi,

    i want to know how to write stored procedure for joinning two tables.
  • #732154
    Hi dineshkumar,

    Nothing, how you wrote query for joining tables same like you just write in SP also.

    EX:

    CREATE PROCEDURE Get_Data
    (
    @Param1 varchar(100)
    )
    AS
    BEGIN
    SELECT *
    FROM TABLE1 A
    JOIN TABLE2 B ON A.ID=B.ID AND A.ID=@Param1
    END

    Try something like above to achieve your goal..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #732156
    Hi Dinesh...


    Try this sample sp for joining two tables


    CREATE PROCEDURE SampleSP (@ID VARCHAR(50))
    AS
    BEGIN

    SELECT A.EMPID,A.EMPNAME,A.SALARY,B.ADDRESS1,B.CITY,B.STATE,B.COUNTRY FROM EMPLOYEE A
    INNER JOIN ADDRESSTABLE B ON A.EMPKEY=B.EMPKEY
    WHERE EMPID=@ID

    END


    Hope this helps you if it is means mark this answer!!

    Regards,
    Sri

  • #732159
    Try this SP without any input parameters..

    CREATE PROCEDURE SP_Join
    AS
    BEGIN

    SELECT A.EMPID,A.EMPNAME,A.SALARY,B.ADDRESS1,B.CITY,B.STATE
    FROM EMPLOYEE A
    INNER JOIN ADDRESSTABLE B
    ON A.EMPID = B.EMPID

    END

    Regards,
    Manick

  • #732163
    Hi,

    I hope you know about, what do you mean by a "join". A join is used to get records or data from two or more tables based on a common column from the joined tables.

    There are many types of joins available, you need to choose which one is suitable for your requirement

    Types of Joins
    -----------------
    1) Inner Joins
    2) Outer Joins (Left Outer, Right Outer)
    3) Self Join
    4) Cross Join

    Try like this

    CREATE PROC GetData
    AS
    BEGIN

    SELECT T1.Column1, T1.Column2, T2.Column1 T2.Column2, ....
    FROM Table1 T1
    INNER JOIN Table2 T2
    ON T1.CommonColumnName = T2.CommonColumnName

    END

    Check these links to know about joins

    http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html

    http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html

  • #732173
    HI,

    Use joins to join to two table in sql and it will return all rows from both the table..


    Create procedure yourprocedurename
    AS
    select * from table1 innerjoin table2 on table1.primarykey = table2.primarykey


    Hope it will be useful.

    Regards,
    T.N. THEAAVARAAJ
    Senior Software Engineer,
    Microsoft Certified Technology Specialist.
    Email:devabe2005@gmail.com

  • #732175
    [Response removed by Admin. Read forum policies.]

  • #732187
    Hi,

    If you want to join two tables in two different database then please check below query. You will be able to join two tables in two different database like below,

    SELECT *
    FROM [DB1].[dbo].[Table1] t1
    INNER JOIN [DB2].[dbo].[Table1] t2
    ON tab1.EmpID = tab2.EmpID


    Please make sure you have access to both the database before executing the query.


    Regards,
    Asheej T K

  • #732192
    Hi.,

    INNER JOIN :

    Returned Match rows between the two tables.

    LEFT OUTER JOIN:

    Based on the two tables specified in the join clause, all data is returned from the left table.

    RIGHT OUTER JOIN:

    Based on the two tables specified in the join clause, all data is returned from the right table.




    CREATE TABLE Employee
    (
    EmpID int,
    FirstName varchar(255),
    LastName varchar(255),
    Address varchar(255),
    City varchar(255)
    );

    CREATE TABLE Salary
    (
    EmpID int,
    Amount Decimal(18,2),
    PANNO VARCHAR(50)
    );


    INSERT INTO Employee SELECT 4,'DDD','DDD','Choolai','Trichy'
    INSERT INTO Employee SELECT 3,'CCC','CCC','Srirangam','Trichy'
    INSERT INTO Employee SELECT 2,'BBB','BBB','Tambaram','Chennai'
    INSERT INTO Employee SELECT 1,'AAA','AAA','Guindy','Chennai'

    INSERT INTO Salary SELECT 1,'10000','ABCB'
    INSERT INTO Salary SELECT 2,'12000','EFGH'
    INSERT INTO Salary SELECT 3,'15000','IJKL'
    INSERT INTO Salary SELECT 5,'25000','MNOP'

    CREATE PROC [dbo].[SP_Employee]
    AS
    BEGIN

    SELECT EMP.EmpID,Emp.FirstName,Emp.LastName,Emp.City,S.Amount AS Salary, S.PANNo FROM Employee AS EMP
    INNER JOIN Salary S
    ON EMP.EmpID=S.EmpID

    SELECT EMP.EmpID,Emp.FirstName,Emp.LastName,Emp.City,S.Amount AS Salary, S.PANNo FROM Employee AS EMP
    LEFT OUTER JOIN Salary S
    ON EMP.EmpID=S.EmpID

    SELECT EMP.EmpID,Emp.FirstName,Emp.LastName,Emp.City,S.Amount AS Salary, S.PANNo FROM Employee AS EMP
    RIGHT OUTER JOIN Salary S
    ON EMP.EmpID=S.EmpID

    END


    Regards,
    Gandhi

  • #732266
    [Response removed by Admin. Read forum policies.]

  • #734153
    To create table

    CREate table Table1(tid int,amount float)

    CREate table Table2(tid int,amount float)

    Insert some values to table1

    insert into Table1 values (1,10)
    insert into Table1 values (2,30)
    insert into Table1 values (3,40)

    insert into Table2 values (1,10)
    insert into Table2 values (2,30)
    insert into Table2 values (3,40)
    insert into Table2 values (4,70)
    insert into Table2 values (5,40)
    insert into Table2 values (6,60)

    If you want to calculate sum of amount from table1 then you can use below stored proceudre

    create procedure Sumoftwotables
    as
    begin

    select SUM(A.amount)as Total,a.tid from Table1 A inner join Table2 B on a.tid =b.tid group by a.tid
    end

    To run above stored procedure use below statements

    EXEC Sumoftwotables

    Output
    Total tid
    20 1
    60 2
    80 3



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #735443
    create procedure test_proc
    @userid int
    as
    begin

    select * from test1 inner join test2
    on test1.empid=test2.empid where userid=@userid
    end


  • Sign In to post your comments