Anyway to declare and use variables in a View

[mike]

I have some SQL code I wrote that returns about 15,000 rows (using a
group by statement, raw data is around 1mill rows). In this code I
have a few variables that I declare and set. These variables for the
most part are just calling functions that return a set value that I
use later in my script in Where statements. The script runs in about
11sec and all the data looks good.

Now I would like to create a view with this script, my ultimate goal
here is to have Excel call the view for an end user that will update
whenever they want. I want to make it as easy as possible on them.

So my question is, is there any way that I can do this?

Now I can certainly just replace all of my variables. However because
of the way that SQL works and the way that I have the script
constucted it fires the functions 1 time each then just uses the
result, if I replace all the variables with the functions it has to
fire the functions around 1mill times each. These functions were
written by someone else and they work but I do find them slow.
Consequently the time to run the script jumps from 11sec to over 15min.

[--CELKO--]

Re: Anyway to declare and use variables in a View

This sounds like you are confusing a spreadsheet and procedural code
with a VIEW. In SQL, we don't like function calls and local
variables. We prefer declarative code and queries. For example, I
can write a VIEW like this:

CREATE VIEW InvoiceHeaders (customer_nbr, order_tot, ..)
AS
SELECT customer_nbr,
SUM(order_qty * unit_price),
..
FROM Invoices AS I, InvoiceDetails AS D
WHERE I.invoice_nbr = D.invoice_nbr
GROUP BY customer_nbr;

If you post what you have, we can do better.

[Plamen Ratchev]

Re: Anyway to declare and use variables in a View

You cannot declare and use variables in views. Maybe it is possible to
change your SQL statement so it does not call those functions for each row.

An alternative is to use a table valued function, in which you can declare
and use variables:

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

Then a view can reference the table valued function as regular table.

HTH,

Plamen Ratchev

http://www.SQLStudio.com