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.
Author |
Topic |
MonikaAhujaRBS
Starting Member
9 Posts |
Posted - 2012-04-19 : 01:27:52
|
Hi All,I want to join the stored proc, but i'm unable to.I cann't store my store proc result in temp table and then use temp table result in join because my stored proc takes one parameter which i want to change dynamically and use the result in join.i want to do something like this :select * from mytable mtjoin book b on b.idbook = mt.idbookjoin spGetbookdetail('BookName') p on p.idBook = b.idBookMy above query is throwing an error 'Invalid Object spGetbookdetail'.Could someone please help? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 01:30:07
|
you cant join to stored procedure like thisEasiest way is to make it a UDF and use APPLY to join to itIf you really want to join to stored procedure then you need to use distributed query method like OPENROWSET which is not recommended for above scenario------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MonikaAhujaRBS
Starting Member
9 Posts |
Posted - 2012-04-19 : 01:32:34
|
How to make UDF, Could you please give me for my below example:select * from mytable mtjoin book b on b.idbook = mt.idbookjoin spGetbookdetail(b.BookName) p on p.idBook = b.idBookYour help would be much apprecialted. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
MonikaAhujaRBS
Starting Member
9 Posts |
Posted - 2012-04-19 : 01:39:52
|
Also my stored proc is very big..i have many temporay tables in my procedure and in UDF we cann't create temp tables.So could you please help me giving some other alternative to do this?. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 01:46:34
|
then only way is thisselect * from mytable mtjoin book b on b.idbook = mt.idbookcross apply(SELECT * FROM OPENROWSET('SQLOLEDB','Data Source=yourServername;Trusted_Connection=yes;Integrated Security=SSPI','Execute yourdb..spGetbookdetail(b.BookName)')) p on p.idBook = b.idBook ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 01:47:59
|
quote: Originally posted by visakh16 then only way is thisselect * from mytable mtjoin book b on b.idbook = mt.idbookcross apply(SELECT * FROM OPENROWSET('SQLOLEDB','Data Source=yourServername;Trusted_Connection=yes;Integrated Security=SSPI','Execute yourdb..spGetbookdetail(b.BookName)')WHERE idBook = b.idBook) p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|