Using the result of a stored procedure in a query

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 2000 Forums

SQL Server Development (2000)

Using the result of a stored procedure in a query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-14 : 10:39:10

Peter writes "NT 4.0 with SP6, SQL 7.0

I am trying to get the following to work:

SELECT fields
FROM table
WHERE ValidField(fieldname)

where ValidField is a kind of function to do some checks on a specific field. During my 2-day search in BOL and on the internet, I found the way to do this in 7.0 is to use a UDF in a seperate dll. I don't want that. If I used SQL 2000, I could use a CREATE FUNCTION. But I don't use 2000. So I tried to put the validation in a stored procedure and returning all the valid rows. ANd now, I want to use the result of the sp in a query, like:

SELECT t.fields
FROM table t INNER JOIN stored_procedure s
ON t.key = s.key

but it gives an error. So my (quite simple) question is: How can I use the result of a sp in a query? I am also working with InterBase, and I can just use "SELECT * FROM sp" there...

So can this be done or is upgrading to 2000 a good option so I can use CREATE FUNCTION?"

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2001-12-14 : 10:55:18

create table #mytemp(
product_id varchar(30))
insert into #mytemp
exec stored_procedure

select description
from product p inner join #myTemp s
on p.key=s.key

drop table #myTemp

helena

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources