Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB and Stored Procedure call

Status
Not open for further replies.

jfield817

Programmer
Joined
Jun 2, 2000
Messages
153
Location
US
Can a SQL Server 7 Stored Procedure be executed
Inside of a "Where" clause from VB 6 ....

The proc would be called as such "Myproc"

The pseudocode would be something like ...

Select * from Table where Table.x = 5 and
Table.y IN a string returned by Myproc ...

How could this be done ....

Thanks much
John
 
You cannot use a stored procedure in a query like that. You can insert the results of a stored procedure into a temp table and use the temp table in the query.

Create #tmp (col1 date_type)

Set nocount on

Insert #tmp
Exec MyProc

Select *
From Table
Where Table.x = 5 and
Table.y IN (Select col1 from #tmp)

It would be even more efficient to use a join if the SP returns many rows.

Select *
From Table
Join #tmp
On table.y=#tmp.col1
Where Table.x = 5

I recommend that you create another stored procedure to perform this process. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top