Join with Stored Procedure ? 1

[1DMF]

I have an SP that takes an argument (ContactID) and returns a row of calculated information relating to the ContactID supplied.

I want to add this to another SP as a join, is this possible?

This is what I'm trying to do..

SELECT DISTINCT Contacts.ContactID, 
             Case When Qual.Mort_Qual = 1
			Then 'Y'
			Else 'N'
			End
			AS Quals
    FROM Contacts LEFT OUTER JOIN (
			SELECT Mort_Qual, ContactID FROM (EXEC spMEM_Is_Qualified(Contacts.ContactID))
			) AS Qual
			ON Contacts.ContactID = Qual.ContactID

How would I achieve this?

1DMF


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[bborissov]

No,
create a table valued function instead.



Borislav Borissov
VFP9 SP2, SQL Server

 

[1DMF]

But the TVF won't let me run an SP into a temp table?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

I've created a TVF ...

ALTER FUNCTION [dbo].[BrokerQual] 
(
	-- Add the parameters for the function here
	@ID as integer
)
RETURNS 
 @T TABLE 
(
	-- Add the column definitions for the TABLE variable here
	Mort_Qual BIT,
	Ins_Qual BIT,
	LifeTime_Qual BIT,
	ContactID Integer
)
AS
BEGIN
	EXEC [dbo].spMEM_Is_Qualified @ID
	RETURN 
END

But when I then try to run it

SELECT * FROM [dbo].[BrokerQual] (95)

I get the following error...

Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.

What am I doing wrong?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

Are you sure about a TVF? because I'm getting

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

and upon investigation it seems you can't EXEC in a TVF

http://jimbojw.com/wiki/index.php?title=SQL_2005_-_Why_You_Can't_EXEC()_Inside_a_UDF

So not sure what it is your are suggesting?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[softhemc]

I've not tried this - I just thought it might be a possible workaround - though not a brilliant solution.

If the sp spMem_Is_Qualified is altered so that it outputs to a table, you could run the sp and then join to the resultant table in your query??

 

[1DMF]

I swapped the SP for a TVF and then had the SP call the TVF so the SP still worked...

-- =============================================
ALTER PROCEDURE  [dbo].[spMEM_Is_Qualified]

	-- Add the parameters for the stored procedure here
	@ID as integer
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM BrokerQual(@ID)

END

But when I try to use it in my join..

		  LEFT OUTER JOIN (SELECT ContactID, Mort_Qual FROM [dbo].[BrokerQual] (Contacts.ContactID))
			 AS Qual
			ON Contacts.ContactID = Qual.ContactID

Still doesn't work?

The multi-part identifier "Contacts.ContactID" could not be bound.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

I think that might be the only way softhemc, but it's a bit nasty, as I would need to clear the holding table every execution and if two people hit it as the same time, could cause problems.

Not sure why i can't join to my TVF with a correlated sub-query?

This is very frustrating!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

I believe this is where you would want to use cross apply.

SELECT DISTINCT Contacts.ContactID, 
             Case When Qual.Mort_Qual = 1
			Then 'Y'
			Else 'N'
			End
			AS Quals
FROM   Contacts 
       Cross Apply dbo.BrokerQual(Contacts.ContactId) As Qual

This syntax may not be perfect, but I suspect this is what you are looking for.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George you are awesome!!!

'Cross Apply' is not a syntax I have come across before, but it works perfectly.

So I now have a TVF that can be 'cross applied' and my SP now calls the TVF so my apps using the SP continue as though nothing has changed

Muchas Gracias


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

So 'Cross Apply' is a way of performing correlated sub-queries and derived table queries :

http://sqlserverplanet.com/sql-2005/cross-apply-explained

Which also enables TVF's to be used in a dynamic correlated way


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Olaf Doschke]

The other solution would be to define the TVF to execute the query into the result table variable itself, not executing the SP.
That would mean a redundant definition of the query in the SP and the TVF, but do you really need the SP?

What is the SP definition anyway, does it really generate a temp table? A table variable as in memory data shoudl be preferrable, shouldn't it?

It's the main difference of procedure vs functions, functions have a result, procedures are commands executing in a shoot and go manner. Of course they influence data, but they have no direct result themselves.
So TVF or Views are the modules to use, when you want to use them in queries, not SPs.

Bye, Olaf.

 

[1DMF]

Hi Olaf,

The other solution would be to define the TVF to execute the query into the result table variable itself

That's what it does, so that is the solution I have isn't it?

I moved the Query / logic to the TVF so it is still only in one place, I then wrapped it in the SP for all the apps that call the SP so nothing broke.

this is my SP

PROCEDURE  [dbo].[spMEM_Is_Qualified]
	@ID as integer
	
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM BrokerQual(@ID)

END

What is the SP definition anyway, does it really generate a temp table?

No temp table it returns a resultset.

Quite a few of my SP's return resultsets, you are implying this is wrong?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

Most of my SP's return result sets. There is absolutely nothing wrong with that. I even have some stored procedures returning multiple result sets.

I have this one form in my application that was calling the approximately 30 different SP's during load. I made a "wrapper" SP that executed all 30 of them. My front end than calls the wrapper SP and receives all 30 result sets.

In my opinion, you did this the correct way.

Like I said earlier, there are multiple ways of accomplishing this.

1. Put your login in a function and then call it from the SP and your new query using cross apply.

2. You could have written a view with the logic. You would want to return all of that data and then apply the filter condition in your query.

3. You could have used Insert/Exec in the stored procedure, similarly to the way you tried it in your function (post dated 27 Aug 15 15:18).

4. You could have essentially recreated the logic for this stored procedure.

There may be more methods too. Not sure.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

>> In my opinion, you did this the correct way

That's always nice to hear, though I couldn't have done it without your help ;-)

I did look at a View possibility, but the SQL GUI didn't look like it wanted freely coded SQL and wanted me to select tables and columns, so decided to give that a miss.

I guess you can manually write the DDL rather than using the GUI, but I tend to use the GUI and find the interface for Views wants to simply join tables together.

I also wondered if I wanted a table (or view) that produced qualification info for all records in Contacts (20k+) when it's only relevant for members or (prospects about to become members) and so using a specific PK at time of request seems more efficient.

Not sure how 3 would have worked as you can't join to an SP and i don't like the holding table idea.

4 is a definite no-no as I only want the logic in one place, so if it ever needs to change it isn't a maintenance nightmare.

I'm in the middle of a major systems refactor and redesign and have been moving legacy spaghetti procedural code to reusable objects and where possible outsourcing SQL logic to SP's, so I have one place any app can ask the same questions and should the result need changing, it's in one place and all apps using the SP won't need altering

The apps don't care what constitutes being qualified, they just need to know for any given ContactID are they qualified, things are becoming much quicker and nicer to work with making it simpler to build new apps and functionality for both Web and in-house DB applications as well as far easier to maintain


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music