How a stored procedure differs from an open session of query analyser

[PreacherUK]

I have a series of statements/code that runs with no problems in QA, using the word 'GO' inbetween each statement. The same series of statements/code requires me to remove all but the last 'GO' from the code. This is causing me problems.

Please, anyone, correct me if I'm wrong, but a QA window does not pre-compile or in anyway check ahead in your code/statements to check things?. In an sp SQL does attempt to pre-compile it?

I have made use of a couple of views in my process that rely upon tables created by statements previously in the process. If the table does not exist until a point after the sp is running then I get 'binding errors'. If I create the table before I run the sp it then allows me to save the sp as there is now an object for the view to be based on.

How do I allow for this short of leaving the data tables as permanent objects rather than truncating, dropping, and then re-creating them fresh each run of the sp?

FLOW:

sp start ---> truncate and drop tables ---> tables created throughout sp ---> views on those tables used to update other tables ---> end

Thanks

 

[jonwolds]

I think there may be a better solution out there but what about creating all the tables at the start of the procedure and then calling a second proc which inserts into these tables?

Jon

 

[PreacherUK]

Thanks for the input John.

That had occoured to me, as did just making the tables perm and simply truncate and insert into them within the procedure.

Really I was curious as to ways to deal with things within the one procedure. Would using aliases work? Creating the views from within the sp rather than as seperate objects that exist outside of the sp.

Again really just seeing if theres ways to do it, otherwise I will just set the tables as perm objects and use them that way.

Dave

 

[jonwolds]

Why are you using views to do the updates? Are these views referenced anywhere else other than this proc?

What happens if you change the views to procedures?

Jon

 

[PreacherUK]

the views are exclusivly for this one sp.

They offer summarised views on a couple of the tables that are created. These figures are then worked back into those tables.

I've taken the liberty of simplifing the table and view names.

(the layout of the code looks a little better when pasted into notepad)

My view:
SELECT TOP 100 PERCENT F_MAJOR, FUND_ID, CPTY, SUM(MKT_VAL_LN_C) AS TOTALCOLLVAL
FROM dbo.GlobalColl
GROUP BY FUND_ID, CPTY, F_MAJOR
ORDER BY F_MAJOR, FUND_ID, CPTY


Example of one of my from clauses using the above view:

FROM dbo.table LEFT OUTER JOIN
dbo.view ON
dbo.table.F_MAJOR = dbo.view.F_MAJOR AND
dbo.table.FUND_ID = dbo.view.FUND_ID AND
dbo.table.CPTY = dbo.view.CPTY


I don't have much practice with aliases, so found it easier to just create the views externally to the sp.

Would dropping and recreating the views during the sp get around the problem of the objects not existing at the point of saving/parsing the sp?

 

[jonwolds]

I wouldn't even bother with the views as you can achive identical summaries using derived tables.

Just tested this and I think you can do it all in 1 proc:

--Start Proc1--------------
drop table GlobalColl
drop table table
create table GlobalColl etc....
create table table etc....
--Now do inserts using derived tables

FROM
dbo.table
LEFT OUTER JOIN
(SELECT F_MAJOR, FUND_ID, CPTY,
SUM(MKT_VAL_LN_C) AS TOTALCOLLVAL
FROM dbo.GlobalColl
GROUP BY FUND_ID, CPTY, F_MAJOR) devtab
ON dbo.table.F_MAJOR = devtab.F_MAJOR AND
dbo.table.FUND_ID = devtab.FUND_ID AND
dbo.table.CPTY = devtab.CPTY
--End of Proc1----

 

[PreacherUK]

I'll give that a try Jon. Thanks for swapping ideas

I'll post back with the results.