Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •