problem with functions and datetime parameters!

Hi all.
i've written a portion of sql code with a dtetime parameter that run very
very fast on a sql window, but when i create a function with the same code
the execution time is extremely long!
to recreate the same speed i found that i must declare a local variable
inside the scope of the function and then assign the variable passed to the
function.
does anyone had the same problem? there is a solution to this bug?
this is my code...
regards,
stefano
create function kp.getQuotaHWM (@.dd1 datetime)
returns float
as
begin
declare @.dd datetime
set @.dd = @.dd1
return (
... code of the function
)
endOn Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:

>Hi all.
>i've written a portion of sql code with a dtetime parameter that run very
>very fast on a sql window, but when i create a function with the same code
>the execution time is extremely long!
>to recreate the same speed i found that i must declare a local variable
>inside the scope of the function and then assign the variable passed to the
>function.
>does anyone had the same problem? there is a solution to this bug?
Hi stefano,
This is a known issue. Not exactly a bug - more an unwanted side effect
of a wanted feature.
Search this group (or the internet) for "parameter sniffing" to find
alll the details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo.
many thanks for your informations.
regards, stefano
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q5iff1pndv10nhhr32c650b9vb5evi0fkb@.
4ax.com...
> On Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:
>
> Hi stefano,
> This is a known issue. Not exactly a bug - more an unwanted side effect
> of a wanted feature.
> Search this group (or the internet) for "parameter sniffing" to find
> alll the details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with Functions

I have a main table that stores the names of all the tables in that database & the next pkey that the record in the respective table should have when a new record is inserted in it(This is the requirement). I am writing a function which returns the primary key of the tablename entered.

The function that i have written is as follows:

CREATE Function next_pkey(@.in_tablename varchar(250),@.in_increment int)
RETURNS int
AS
BEGIN
DECLARE @.nextid int
DECLARE @.out_nextid int
SET @.out_nextid = -1
UPDATE main_ids SET nextid = nextid + @.in_increment WHERE tablename = @.in_tablename
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO main_ids (tablename, nextid) VALUES (@.in_tablename, 1 + @.in_increment)
SET @.nextid = 1
END
ELSE
BEGIN
SELECT @.nextid = nextid - @.in_increment FROM main_ids WHERE tablename = @.in_tablename
END
IF (@.@.ERROR = 0)
SET @.out_nextid = @.nextid
Return @.out_nextid
END
GO

This function gives 2 erros as mentioned below:
Invalid use of 'UPDATE' within a function.
Invalid use of 'INSERT' within a function.

I have the following query:
Is an update statement allowed in a function?

Can you tell me what should i do so that this function executes properly?
Plz Help???
Thanks.from BOL...

The types of statements that are valid in a function include:

DECLARE statements can be used to define data variables and cursors that are local to the function.

Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

Control-of-flow statements.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

EXECUTE statements calling an extended stored procedure.|||Can you tell me what should i do so that this function executes properly?
Turn it into a Procedure, making your return value an outbound parameter.

Functions are not allowed to change data.

Problem with function

Hello,

I am unable to resolve an error message for following user defined function.

Scope of this function:

This functions generates a unique identifier no from a table and returns it to caller.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION SystemNoShiva1

( -- Add the parameters for the function here

)

RETURNS int

AS

begin

DECLARE @.st2 numeric(18,0)

DECLARE @.sys01 numeric(18,0)

declare sys cursor for Select Max(SystemNo) From TISSystemMst1

open sys

Fetch sys into @.sys01

begin

--DECLARE @.st2 numeric(18,0)

if (@.@.fetch_status = 0)

-- IF @.@.ROWCOUNT <> 0

close sys

Deallocate sys

select @.sys01 = @.sys01 + 1

--update TISSystemMst1 set systemno = (@.sys01)

insert into TISSystemMst1 values(@.sys01)

select @.st2 = @.sys01

end

RETURN @.st2

end

Messeg I receive is

Msg 128, Level 15, State 1, Line 17

The name "sys" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

How can I clear above error message?

Nilkanth Desai

Hi,

A typical cursor fetch as follows, however looking at the script, why are you using a cursor, if we are simply selecting the topmost (Max) value and incrementing it?

If you are simply incrementing the topmost value why not use something like

CREATE FUNCTION SystemNoShiva1

( -- Add the parameters for the function here

)

RETURNS int

declare @.st2 int

set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)

insert into TISSystemMst1 values (@.st2)

return @.st2

Go

--Cursor Option As Follows--

declare @.sys01 int

Declare sys Cursor For
Select Max(SystemNo) From TISSystemMst1

Open sys
fetch next from sys
into @.sys01

WHILE @.@.fetch_status=0
begin

set @.sys01 = (@.sys01+1)

insert into TISSystemMst1 values(@.sys01)

fetch next from sys
into @.sys01
end

close sys
deallocate sys

return @.sys01

Hope this helps

|||

Hello GDR,

Thx for u r prompt reply. I tried both samples but receive error as under.

Sample 1

While creating this function I receive message "Incorrect syntext near the keyword declare"

In Sample 2

In this code sample I receive same error message as I was receiving in my previous (main) message.

Nilkanth Desai

|||

My humble appologies, the 'AS' is missing, I wrote the script on the forum page, not on a SQL interface thus parsing was not an option,

CREATE FUNCTION SystemNoShiva1

RETURNS int

AS

begin

declare @.st2 int

set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)

insert into TISSystemMst1 values (@.st2)

return @.st2

End

Problem with full text searching

I'm wrtiting a local site search egine but I need to make use of functions like FREETEXTABLE
for instance. In order to use this I have to have the' Full Text Searchtable' enabled for a table. Now the Microsoft Search Service is runningin MSDE; of that I'm sure. However when I try to enable the full textsearching on a table or database the option that I'm supposed to chooseis grayed out which means I can't select it. What's wrong? What do Ihave to do to be able to use that option? I' short of time and wouldappreciate n answer a.s.a.p. Thanks
MSDE does not support the full text service, sorry :-(
|||I am using sql server 2000 trial edition and I'm sure I checked the full text search option during the installion.
|||Figured out the problem thanks!