Re: Bulding expressions in views

If iStdt() and iDate() are of type datetime:

declare @iStdt2 datetime
declare @iDate2 datetime

set @iStd2 = dbo.Stdt()
set @iDate2 = dbo.iDate()

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN @iStd2 AND @iDate2)

Caution: if you are in batch mode instead of inside a stored procedure, each
time you use the command GO, the declarations and values of previously
defined local variables are lost thereafter. In another way, you start with
a blank slate after each Go command.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Jose Perdigao" <perdijc@xxxxxxxxxxx> wrote in message
news:u77SJ6zKGHA.2628@xxxxxxxxxxxxxxxxxxxxxxx
Hi Sylvain
The fuunction return the same value for all rows. So, how can I store in a
local variable? Could you give me an example?

Thanks
José Perdigão

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23xhCOnzKGHA.3896@xxxxxxxxxxxxxxxxxxxxxxx
When using a function that's returning the same value for all rows of a
query, you should store the value in a local variable whenever possible.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Jose Perdigao" <perdijc@xxxxxxxxxxx> wrote in message
news:Ojhu9OvKGHA.1288@xxxxxxxxxxxxxxxxxxxxxxx
Good Morning Vadim,

I'm disappointed with UDF.

I created two scalar functions to use in views, sp or inlinefunctions
and the result was low performance (too slow)

I give you the examples:



query 1:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:30sec; 31502 rows; 49 columns

query2:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



The query 2 (without functions), the performance is much better than
query 1.

Off course the statement of query1 is easier than query 2

Do you think some this is wrong?

I would like to create views with functions in criteria.

From this example, can I conclude, we must not use functions in
criteria?





My owner functions:



ALTER FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN
RETURN (SELECT iDate FROM dbo.A4_Users WHERE (Login = dbo.iLogin()))
END



ALTER FUNCTION dbo.iStdt ()
RETURNS datetime
AS
BEGIN
RETURN (SELECT iStdt FROM dbo.A4_Users WHERE (Login = dbo.iLogin()))
END



ALTER FUNCTION dbo.iLogin ()

RETURNS varchar(30)

AS

BEGIN

/* Sql Server autentication mode */

IF CharIndex('\',system_user)=0

RETURN system_user

/* Windows Autentication mode */

RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END



I appreciate your suggestions,

José Perdigão



"Vadim Rapp" <vr@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23WNaRbmKGHA.3896@xxxxxxxxxxxxxxxxxxxxxxx
Hello Jose,
You wrote in conference microsoft.public.access.adp.sqlserver on Sat,
4 Feb 2006 11:27:02 +0100:

JP> There is a way to simplify expressions in views, sp or
JP> in-line-functions?

consider using user-defined functions in sql server.

Vadim Rapp








.

Relevant Pages

  • Speeding up call to a remote table
    ... I've got a SQL Query created to pull aggregate values from a linked ... Declare @StartDate as datetime ... Declare @EndDate as datetime ...
    (microsoft.public.sqlserver.programming)
  • RE: Select across databases
    ... > How would I add this to the query? ... > also in the other database. ... > DECLARE @startdate AS DATETIME ...
    (microsoft.public.sqlserver.programming)
  • Re: UDF and SQL2000 - Why doesnt this work?
    ... Basically This is what I want to do - I have created a query ... > datetime, @PeriodEnd datetime) ... > DECLARE @TempDate datetime ... > DECLARE @TempVal float ...
    (microsoft.public.sqlserver.programming)
  • Re: Declare in Pass-Through Queries
    ... > Declare @BegDate as datetime ... > select CompletionDate betwee @BegDate and @End date. ... AFAIK a PT query cannot execute everything that QA can. ...
    (microsoft.public.access.queries)
  • Re: Spalten alternativ ansprechen
    ... DECLARE @Monat INT ... '01' AS DATETIME) ... case when DATUM between '...' ... then UMSATZ as ...
    (microsoft.public.de.access.clientserver)
Loading