insert within a function

CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END
CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END

I m getting error at the Insert statement, it says error 443, invalid use of insert within a function,

Cann we use insert in a function, if we cann, what is the alternative to insert the values?
do help me asap.Try moving the "Insert" into a stored procedure and then "exec procedure" from your function. Other solution would be to transform your function in a stored procedure by itself|||Did you look at BOL?

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

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

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

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

EXECUTE statements calling an extended stored procedures.

And why are you define the same udf twice...and why isn't this a sproc?

INSERT UPDATE Trigger Question

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.Hi

Try something like:

CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )

CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END

INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')

SELECT * from Test

John

<imani_technology@.yahoo.com> wrote in message
news:f9208446.0309011615.5f269625@.posting.google.c om...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.|||imani_technology@.yahoo.com wrote in message news:<f9208446.0309011615.5f269625@.posting.google.com>...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.

You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):

create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn

Simon