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 SP results into variable?

Is it possible to take the result from an SP that returns 1 value and
insert it into a variable?
DECLARE @.x varchar(9), @.sql1 nvarchar(200)
SELECT @.sql1=N'select @.x=count(*) from tt'
EXEC sp_executesql @.sql1, N'@.x varchar(9) OUTPUT', @.x output
Select @.x

Madhivanan|||<jw56578@.gmail.com> wrote in message
news:1111709552.442972.201630@.l41g2000cwc.googlegr oups.com...
> Is it possible to take the result from an SP that returns 1 value and
> insert it into a variable?

If a procedure returns one value, then you should use an output variable:

create proc dbo.p_out
@.i int output
as
set @.i = 1
go

declare @.num int
exec dbo.p_out @.i = @.num output
select @.num

This is usually the best approach for procedures which return only one row
of data:

http://www.sommarskog.se/share_data.html#OUTPUT

Simon

Insert query problem and also select

I have a Bit field in my database ,but when i try to insert the bit in database it inserted succesfully but while accessing it returns -1,what would be the problem?

i used integer while inserting same as get the value from database?

In SQL Server Bit data can only be one of the 3 values: 1, 0, or Null. So I'm wondering how did you get the returned value -1. What's the returned value if you directly query the database (in Query Analyzer or Enterprise Manager, or using OSQL utility)? And could you please post the code for retrieving the bit data from database?