create views in stored procedures?

Can you create views within a stored procedure?

The short answer is 'Yes'; what exactly do you have in mind because this might not be such a good idea. You might need to use dynamic SQL to get the job done; otherwise, you might get a procedure compile error such as:

Server: Msg 156, Level 15, State 1, Procedure wacko, Line 6
Incorrect syntax near the keyword 'view'.

|||

I've got a report (Access front end) that takes over 2 minutes to run. Access sometimes times out before the report finishes. (I've already adjusted Access's timeout range to the maximum amount.) The report data is based on a view that pulls data from several other views and tables (using unions, outer joins, etc.). I decided the best way to keep the report from timing out in Access was to have the result set queried each night and inserted into a table. The report will now use the table as it's data source. The data doesn't have to be dymnamic as long as it's updated nightly. So, now I'm trying to create a stored procedure that will run nightly, inserting the final results into a table. I'm getting this error message for every line I'm trying to create a view on:

Server: Msg 156, Level 15, State 1, Procedure sp_insert_edirecalltbl, Line 16
Incorrect syntax near the keyword 'VIEW'.

Here's a small portion of the code in my procedure:

BEGIN
CREATE VIEW edirecallview
As SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace
END

BEGIN
select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace
END

Maybe I should use temporary tables instead of views? If I have to, I can use permanant tables instead of views. Ideas?

|||

You can use a permanent table to hold the summary information that you populate each night. The procedure can look like this

CREATE PROCEDURE spxyz

AS

BEGIN

Truncate mySummaryTable;

--<Drop indexes if the data is in millions, otherwise not>

INSERT INTO mySummaryTable(col1, col2, col3)

select a.col1, a.col2, b.col3

from TableA a inner join TableB b on a.pk = b.pk

--<Recreate indexes if you dropped before the insert>

--Thats it

END

|||

I would actually use a CTE (common table expression) instead of a view. This is not available in 2000 just 2005.

WITH cte_edirecallview (xmltrace, xmllname, xmlvetcode, xmltransdate, xmltotalamt, xrawtrace, xrawtransdate, xrawamount, deptrace, deptransdate, depamount, depvouchernum, drawtrace, drawtransdate, drawamount)

AS

(

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

)

select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM cte_edirecallview x FULL OUTER JOIN cte_edirecallview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

|||That would be so perfect...if only I was using 2005! Thanks, anyway. It's something to look forward to.|||

You can dynamically create the view via Exec('view_definition').

e.g.

Code Snippet

Exec ('create view edirectcalview AS

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace')

However, in your case, you can just derive the query.

e.g.

Code Snippet

select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM (

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace)

x FULL OUTER JOIN (

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace)

d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

|||

In addition, in 2000 it was very typical to just give in an use a temporary table for this sort of thing, and to be honest, sometimes it can be better anyhow. It really depends on how well SQL Server happens to optimize your derived table. Sometimes it can manifest itself as multiple queries rather than spooling the rows into some temporary storage and calculating it once:

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount

into #edirecallview
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM #edirecallview x FULL OUTER JOIN #edirecallview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

This would be one of the steps I might take to optimize your query if it were running slowly (and we had exhausted all plan based optimizations) anyhow.