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 dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_datevarchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime data, where it is
the client that make it into a readable format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegr oups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>
|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
David Portas
SQL Server MVP
|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?
|||It returns datetime, which doesn't have a format. Datetime is formatted by the client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegr oups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>
|||cheers very useful
|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...

>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_datevarchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.

>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.

>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.

>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_date varchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime data, where it is
the client that make it into a readable format.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegroups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
--
David Portas
SQL Server MVP
--|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?|||It returns datetime, which doesn't have a format. Datetime is formatted by the client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegroups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>|||cheers very useful|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...
>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_date varchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.
>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.
>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.
>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_date varchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more pr
obably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datet
ime data, where it is
the client that make it into a readable format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegroups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
David Portas
SQL Server MVP
--|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?|||It returns datetime, which doesn't have a format. Datetime is formatted by t
he client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegroups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>|||cheers very useful|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...

>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_date varchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.

>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.

>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.

>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)