Selecting results from a stored procedure as part of larger recordset 1

[gbaughma]

I am building a pretty large query that builds an XML table to be imported into our accounting system.

One of the things that I need to do, however, is to select the tax table for the accounting system and include it in my results.... so for example, I need to do something like this:

SELECT CustomerName, ShipToAddress, ShipToCity, ShipToState, (SELECT TAXTableID from sp_LookupTax ShipToCity, ShipToState) AS TaxTableID, bla bla joins etc.

So, I use a stored procedure instead of a JOIN because not every city and/or state is going to be in there, and the stored procedure returns a default value. So, to recap, when I select the city and state from the main record, I need to sub-query the tax table from another table, based on the information that I just got from the main query.

Any thoughts?

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[RiverGuy]

My first thought is that you should have used a user-defined function instead of a stored procedure. It would work perfectly for this. Your syntax would look something like:

SELECT CustomerName, ShipToAddress, ShipToCity, ShipToState, dbo.fn_LookupTax (ShipToCity, ShipToState) AS TaxTableID, bla bla joins etc.

Secondly, if you have a lot of rows, this will slow down your query considerably. You might also want to think about just doing the logic from your procedure/function within the main query in a more set-based way. Or if you are using the correct version of SQL Server, you could try creating a function which returns a table variable and using CROSS APPLY. See this link for details:

http://msdn.microsoft.com/en-us/library/ms175156.aspx

 

[gbaughma]

I wrote it as a function. That will work. Thank you!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com