join stored procedure with a table??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join stored procedure with a table???

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-11 : 06:30:19

hi all....
may i know how to join a stored procedure with a table in same database.
Suppose there are two tables named as A,B
A is having columns...[eid],[name1],[salary],[dept_id].
B is having columns.... [dept_id],[dep_name].
A.eid is primary key and dept_id is a foreign key.
Now suppose there is a procedure which has input parameter as @name1,@salary and displays A.eid,A.name1,A.dept_id.

Now how we can join stored procedure with some other table???
I did went thorugh some articles and found one example which i am giving below..

--select t1.id, sp1.field1
--from t1
--join sp1(parameters) on t1.id = sp1.id;

I tried with use of this query but cant get correct results...Do help...

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 06:47:55

select a.eid,a.name1,a.dept_id
from tablea a
inner join tableb b on (a.dept_id = b.dept_id)
where a.name1 = @name1 and a.salary = @salary

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 06:52:34

i think u can't join the sp with a table

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-11 : 06:53:04

thanks jai for the reply....
The solution you are giving is perfect ,but my need is to join stored procedure with a table.
The data i have given above is just a small sample data to explain my problem..
Now i hope i am able to elaborate my problem more clearly ...

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 06:55:43

Instead of joining the sp with a table

join the table with the select statement in the sp

Jai Krishna

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-11 : 06:55:51

thanks bklr for your input...
Just check the link below once....
http://www.firebirdfaq.org/faq143/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-11 : 07:14:58

In MS SQL you would need a table valued user defined function for this.

CREATE FUNCTION dbo.myTable(@Var1 int, @Var2 int)
RETURNS table 
AS

BEGIN 
  DECLARE @myTable table (ID int IDENTITY(1, 1), myNumber int)

  INSERT INTO @myTable (myNumber)
  SELECT @Var1 UNION ALL 
  SELECT @Var2 

  RETURN @myTable
END

and then

SELECT * 
FROM someTable a
  INNER JOIN dbo.myTable(Var1, Var2) b
  ...

- Lumbago

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 07:19:49

quote:
Originally posted by abcd

thanks bklr for your input...
Just check the link below once....
http://www.firebirdfaq.org/faq143/

hi abcd,
i think it is not ms sql k
up to my knowledge u can't join the sp with table in sql
use fuction as lumbago suggested

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-11 : 07:27:22

thnks bklr...
may be i was wrong..but after reading that link i thought to discuss out here..
yup i think lumbago is right and i am taking help from his post...
thnks to you too...:):)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources