Build StorProc into Another SP

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

Transact-SQL (2005)

Build StorProc into Another SP

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-28 : 07:59:01

While transferring my Access DB in SQL Server I have this Query which looks at a Table and a Query shown below

C1a_CareGroupTOTAL_SplitCGs_CorrectMatches is a Query

Access Query
SELECT PARAMED_temp.ContactServiceID, Count(PARAMED_temp.EventID) AS CountOfEventID
FROM PARAMED_temp
LEFT JOIN C1a_CareGroupTOTAL_SplitCGs_CorrectMatches
ON PARAMED_temp.EventID = C1a_CareGroupTOTAL_SplitCGs_CorrectMatches.EventID
WHERE (((PARAMED_temp.F2F)="y")
AND ((PARAMED_temp.TH_SOR_LOC_CAT)="DA"
Or (PARAMED_temp.TH_SOR_LOC_CAT)="NR"
Or (PARAMED_temp.TH_SOR_LOC_CAT)="COM")
AND ((C1a_CareGroupTOTAL_SplitCGs_CorrectMatches.EventID) Is Null))
GROUP BY PARAMED_temp.ContactServiceID
HAVING (((PARAMED_temp.ContactServiceID)="diet"
Or (PARAMED_temp.ContactServiceID)="ot"
Or (PARAMED_temp.ContactServiceID)="physio"
Or (PARAMED_temp.ContactServiceID)="podiatry"
Or (PARAMED_temp.ContactServiceID)="speech"))
ORDER BY PARAMED_temp.ContactServiceID

I have a StoredProc which will run the SPC1a_CareGroupTOTAL_SplitCGs_CorrectMatches but how can I build a Stored Procedure to use replicate the above Access Query as a SP?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-28 : 08:12:42

I am not understanding it well. You want to know how to convert the above access query to an MS Sql query or SP?

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-28 : 08:46:32

Ok This code is my Access Query.

SELECT PARAMED_temp.ContactServiceID, Count(PARAMED_temp.EventID) AS CountOfEventID
FROM PARAMED_temp
LEFT JOIN C1a_CareGroupTOTAL_SplitCGs_CorrectMatches
ON PARAMED_temp.EventID = C1a_CareGroupTOTAL_SplitCGs_CorrectMatches.EventID
WHERE (((PARAMED_temp.F2F)="y")
AND ((PARAMED_temp.TH_SOR_LOC_CAT)="DA"
Or (PARAMED_temp.TH_SOR_LOC_CAT)="NR"
Or (PARAMED_temp.TH_SOR_LOC_CAT)="COM")
AND ((C1a_CareGroupTOTAL_SplitCGs_CorrectMatches.EventID) Is Null))
GROUP BY PARAMED_temp.ContactServiceID
HAVING (((PARAMED_temp.ContactServiceID)="diet"
Or (PARAMED_temp.ContactServiceID)="ot"
Or (PARAMED_temp.ContactServiceID)="physio"
Or (PARAMED_temp.ContactServiceID)="podiatry"
Or (PARAMED_temp.ContactServiceID)="speech"))
ORDER BY PARAMED_temp.ContactServiceID

This is another query in Access
C1a_CareGroupTOTAL_SplitCGs_CorrectMatches

So my above Access Query looks at a Table and a Query to give me the results.

I have this Access query (C1a_CareGroupTOTAL_SplitCGs_CorrectMatches ) in SQL Server as a Stored Procedure allready (This is how I run that EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM')

I want to understand how I can replicate the Access Query as a Stored Procedure using the allready built to link the data sets as in the case of the Access Query

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-28 : 09:21:17

If I understand you correctly you want to join on a field from another query.
Two ways I can think of:
Save the C1a_CareGroupTOTAL_SplitCGs_CorrectMatches as a View and join to the View.
or
Use the query SQL itself with-in the second query as a derived table and join to the derived table

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-28 : 09:30:08

I thought about the view, but cant do that as the Stored Proc relies on parameters from the EXEC Statement as shown in thread 3

"Use the query SQL itself with-in the second query as a derived table and join to the derived table"
How would I do something like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 01:04:39

quote:
Originally posted by JezLisle

I thought about the view, but cant do that as the Stored Proc relies on parameters from the EXEC Statement as shown in thread 3

"Use the query SQL itself with-in the second query as a derived table and join to the derived table"
How would I do something like this?

first create a temporary table with same structure as your stored procedure resultset (sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches)
then use insert like below


INSERT INTO #C1a_CareGroupTOTAL_SplitCGs_CorrectMatches
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'

then use this temp table in your query


SELECT PARAMED_temp.ContactServiceID, Count(PARAMED_temp.EventID) AS CountOfEventID
FROM PARAMED_temp 
LEFT JOIN #C1a_CareGroupTOTAL_SplitCGs_CorrectMatches c
ON PARAMED_temp.EventID = c.EventID
WHERE (((PARAMED_temp.F2F)="y") 
AND ((PARAMED_temp.TH_SOR_LOC_CAT)="DA" 
Or (PARAMED_temp.TH_SOR_LOC_CAT)="NR" 
Or (PARAMED_temp.TH_SOR_LOC_CAT)="COM") 
AND ((c.EventID) Is Null))
GROUP BY PARAMED_temp.ContactServiceID
HAVING (((PARAMED_temp.ContactServiceID)="diet" 
Or (PARAMED_temp.ContactServiceID)="ot" 
Or (PARAMED_temp.ContactServiceID)="physio" 
Or (PARAMED_temp.ContactServiceID)="podiatry" 
Or (PARAMED_temp.ContactServiceID)="speech"))
ORDER BY PARAMED_temp.ContactServiceID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 01:22:14

Also just noticed that your sp name is sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches
please dont use sp_ for sp names.
this will cause cache miss sometimes and results in poor performance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 01:24:56

More details here

http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-01 : 04:00:16

Thanks for that...

I have built a TempTable, now with inserting the records into it. I have tried this below
INSERT INTO #C1a_CareGroupTOTAL_SplitCGs_CorrectMatches(I put the field names here)
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'
It says it has run the command, but when opening the TempTable it hasnt actually inserted the data.
What have I done wrong?

I have stopped usuing the sp_ on my StoredProc and changing all others.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 04:03:06

quote:
Originally posted by JezLisle

Thanks for that...

I have built a TempTable, now with inserting the records into it. I have tried this below
INSERT INTO #C1a_CareGroupTOTAL_SplitCGs_CorrectMatches(I put the field names here)
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'
It says it has run the command, but when opening the TempTable it hasnt actually inserted the data.
What have I done wrong?

I have stopped usuing the sp_ on my StoredProc and changing all others.

did you try executing below

EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'

and confirmed if you got reqd resultset before running insert?

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-01 : 05:29:45

Yes that works fine and returns the results I was after.

Then I run this below

ALTER PROCEDURE jez.sp_PARA_c1aTEMP
AS
INSERT INTO PARA_TEMPTABLE([Care_Group], ContactServiceID, EventID)
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'

Once I have run that I've looked in the TempTable and there are 0 rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 05:36:26

quote:
Originally posted by JezLisle

Yes that works fine and returns the results I was after.

Then I run this below

ALTER PROCEDURE jez.sp_PARA_c1aTEMP
AS
INSERT INTO PARA_TEMPTABLE([Care_Group], ContactServiceID, EventID)
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'

Once I have run that I've looked in the TempTable and there are 0 rows

Are you looking at right table?
Are you executing code as dbo? does the table exist in any other schema too?

what does this return you?

select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='PARA_TEMPTABLE'

single or mltiple records?

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-01 : 05:52:04

Running that show that there is only 1 PARA_TEMPTABLE and shows in the schema thats set up for me to use.
I only use schema jez

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-01 : 06:04:49

If you want to use a derived table rather than a temp table it would be something like this:

SELECT PARAMED_temp.ContactServiceID, Count(PARAMED_temp.EventID) AS CountOfEventID
FROM PARAMED_temp

left join

--derived table C1a_CareGroupTOTAL_SplitCGs_CorrectMatches

(
--Put the original SQl from Access Query here
select EventID

from TableName
where .........) as C1a_CareGroupTOTAL_SplitCGs_CorrectMatches

--join to derived table

ON PARAMED_temp.EventID = C1a_CareGroupTOTAL_SplitCGs_CorrectMatches.EventID
WHERE (((PARAMED_temp.F2F)='y')
AND ((PARAMED_temp.TH_SOR_LOC_CAT)='DA'
Or (PARAMED_temp.TH_SOR_LOC_CAT)='NR'
Or (PARAMED_temp.TH_SOR_LOC_CAT)='COM')
AND ((C1a_CareGroupTOTAL_SplitCGs_CorrectMatches.EventID) Is Null))
GROUP BY PARAMED_temp.ContactServiceID
HAVING (((PARAMED_temp.ContactServiceID)='diet'
Or (PARAMED_temp.ContactServiceID)='ot'
Or (PARAMED_temp.ContactServiceID)='physio'
Or (PARAMED_temp.ContactServiceID)='podiatry'
Or (PARAMED_temp.ContactServiceID)='speech'))
ORDER BY PARAMED_temp.ContactServiceID

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-01 : 06:29:55

quote:
Originally posted by darkdusky

If you want to use a derived table rather than a temp table it would be something like this:

SELECT PARAMED_temp.ContactServiceID, Count(PARAMED_temp.EventID) AS CountOfEventID
FROM PARAMED_temp

left join

--derived table C1a_CareGroupTOTAL_SplitCGs_CorrectMatches

(
--Put the original SQl from Access Query here
select EventID

from TableName
where .........) as C1a_CareGroupTOTAL_SplitCGs_CorrectMatches

--join to derived table

I dont quite understand this bit. My original SQL from Access no longer exists . Its now run by a Stored Proc using this line
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y', 'DA', 'NR', 'COM'

How would I incorporate this into the derived table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 06:39:34

After creating sp like this

ALTER PROCEDURE jez.sp_PARA_c1aTEMP
AS
INSERT INTO PARA_TEMPTABLE([Care_Group], ContactServiceID, EventID)
EXEC sp_PARA_C1a_CareGroupTOTAL_SplitCGs_CorrectMatches 'DIET', 'OT', 'PHYSIO', 'PODIATRY', 'SPEECH', 'Y'

did you execute it like

EXEC jez.sp_PARA_c1aTEMP

before looking into temp table? if not, you wont get any records as you've only created sp without executing it.

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-01 : 07:19:17

No, I was running the StoredProc looking at the TempTable but forgot to EXEC jez.sp_PARA_c1aTEMP first.

Thanks for the help :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 07:23:09

quote:
Originally posted by JezLisle

No, I was running the StoredProc looking at the TempTable but forgot to EXEC jez.sp_PARA_c1aTEMP first.

Thanks for the help :-)

Ok... so i guessed right...

does everything work fine now?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-01 : 07:46:57

Another option would be to convert the stored procedure into a table-valued function which could accept parameters similar to proc but could also be directly joined to and or queried.

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-12-01 : 08:09:16

Yes everything works fine now. Thanks for the help.

I googled the drived tables and found some information on them, they seem like a good little trick...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 08:52:56

quote:
Originally posted by JezLisle

Yes everything works fine now. Thanks for the help.

I googled the drived tables and found some information on them, they seem like a good little trick...

They really are. But in your case since your results comes from stored procedure, you need to use below method (which is not much performant) if you want to bring them inside derived table.

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources