Post reply

Insert into table using function.

  • October 13, 2010 at 3:08 pm

    #227843

    Hi all,

    I've been busting my head trying to create a function that inserts into a table and returns the Scope_Identity().

    I found out that it isn't possible to have an insert in a function.

    So I decided to create a Stored Procedure that would be called by the Function.

    And that's not working either... I get the following error message...

    "Only functions and extended stored procedures can be executed from within a function."

    I'm trying to avoid to use a cursor.

    What I'm trying to do is to fill my Locations table from my OldLocations table. The catch is that there's a MapImageID field that is in the Locations table which isn't in the OldLocations Table. The MapImageId Column is a foreign key to the MapImage Table.

    Which also need to be filled by the OldMapImage Table.

    So I'm trying to fill the Locations Table and when it come to the column MapImageId column, I want it to call a function that inserts into MapImage and returns the scope_Identity() into the Locations Table...

    There's a reason for this madness... the OLDMapImage table can contain multiple times the same address and I need to do this between 6 different tables that link to this MapImage table. looking for the right image and inserting a new line everytime.

    Am I trying to do the impossible?

    Here's my code:

    create Procedure TransferMapImage

    @SourceDB varchar(100), @Address varchar(200), @City Varchar(100), @State Varchar(100), @Zip varchar(20),@MapImageID int Output

    AS

    Set nocount on

    Declare @SQLStr varchar(8000)

    Set @SQLStr = '

    Select 0.0,0.0,[Address],'''',[City],[State],Country,Zip,mpMapImage

    From [' + @SourceDB + '].dbo.Map_MapPointXRef

    where[Address] = ''' + @Address + '''

    and[City] = ''' + @City + '''

    and[State] = ''' + @State + '''

    and[Zip] = ''' + @Zip + ''''

    Insert into dbo.Map_Image

    Exec(@SQLStr)

    Set @MapImageID = SCOPE_IDENTITY()

    Alter Function dbo.InsertReference_MapImage (@SourceDB varchar(100), @Address varchar(200), @City Varchar(100), @State Varchar(100), @Zip varchar(20))

    Returns Bigint

    As

    Begin

    Declare @InsertedID int

    Exec TransferMapImage @SourceDB, @Address , @City , @State , @Zip, @InsertedID -- @MAPImageID = @InsertedID OUTPUT

    Return @InsertedID

    End

    Select dbo.InsertReference_MapImage('BFR_CBS_IPP_Test','adressxxxxx', 'streetxxxxxxxx', 'ST', '11111')

    I get the following error:

    "Only functions and extended stored procedures can be executed from within a function."

    Any help will be greatly appreciated...

    Thanks

    JG

  • October 13, 2010 at 3:24 pm

    #1236531

    just change your plan to use a stored procedure.

    a function cannot change data. No DML operations allowed, no dynamic SQL and a lot of other restrictions.

    a stored procedure does not have that extra layer of restrictions, so if you plan on using a procedure instead, you'l get what you are after.

    here's some of the many restrictions on a function:

    ··UDF has No Access to Structural and Permanent Tables.

    ··UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)

    ··UDF Accepts Lesser Numbers of Input Parameters.

    ··UDF can have upto 1023 input parameters, Stored Procedure can have upto 2100 input parameters.

    ··UDF Prohibit Usage of Non-Deterministic Built-in Functions Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure

    ··UDF Returns Only One Result Set or Output Parameter Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure

    ··UDF can not Call Stored Procedure Only access to Extended Stored Procedure.

    ··UDF can not Execute Dynamic SQL or Temporary Tables

    ··UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.

    ··UDF can not Return XML FOR XML is not allowed in UDF

    ··UDF does not support SET options SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)

    ··UDF does not Support Error Handling RAISEERROR or @@ERROR are not allowed in UDFs.

    ··UDF does not Support print statements for debugging

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2010 at 3:05 am

    #1236686

    sounds good to me :-):-)

    cissp dumps
    comptia a+
    ccie
    pmp dumps

  • October 14, 2010 at 4:27 am

    #1236720

    You could use the OUTPUT clause, returning the new PK and an old PK for the backlink. This would allow you to do all of the inserts in one go, then all of the backlink updates in one go.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 14, 2010 at 6:29 am

    #1236774

    The problem with using a stored Procedure is that I can't use the stored procedure in a insert into select statement for that field.

    And... I can't insert all at once as suggested. Let me try to clarify furthermore...

    The oldMapImage is a table with many addresses and a map per address.

    I have 6 tables that have foreign keys that referent the new MapImage table that I'm creating.

    I want to transfer the data from OldMapImage to MapImage but I want every ID of a same address (can have 6 same addresses) to be linked on each of the 6 individual tables.

    Example:

    In my Customer table, a customer has an address and an image... this entry will have an ID of X

    In My Appointment Table, the address can be the same as in the Customer Table but I need it to have a different ID in the MapImage table.

    I can solve the problem by creating a cursor that would go through the Old Customer table, find the matching address in OldMapImage

    Insert into the New MapImage table, get the Scope Identity and with that, insert into the new customer table referencing the new record inserted in MapImage.

    But I'm trying to avoid using the cursor... I try to use cursors as last last last last resort... 😉

    I hope this puts a light on the situation I have.

    Any help is greatly appreciated.

    Thanks

    JG

  • October 14, 2010 at 6:31 am

    #1236776

    Can you write a SELECT which retrieves only the rows which you want to insert into the new table?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 14, 2010 at 6:43 am

    #1236790

    /* ************************************** */

  • October 14, 2010 at 6:45 am

    #1236794

    As Criss Morris suggested, using the OUTPUT clause is the best, especially if you want to capture multiple rows.

    here's a prototype/best guess based on the SQLs you posted; this might get you started:

    this works wehtehr the select is one row or all the rows in a table:

    --a table to capture the new Id and also the associated data for reference.

    DECLARE @ResultsFromInsert TABLE(MapImageID int,

    geoX decimal(10,4),

    GeoY decimal(10,4),

    Addr varchar(100),

    Addr2 varchar(100),

    city varchar(100),

    [state] varchar(2),

    country varchar(20),

    zip varchar(9),

    mpMapImage varbinary(max))

    Insert into dbo.Map_Image(geoX,GeoY,Addr,Addr2,city,[state],country,zip,mpMapImage)

    --OUTPUT clause has access to the INSERTED and DELETED tables from the trigger!

    OUTPUT

    INSERTED.MapImageID, --the new identity

    INSERTED.geoX,

    INSERTED.GeoY,

    INSERTED.Addr,

    INSERTED.Addr2,

    INSERTED.city,

    INSERTED.[state],

    INSERTED.country,

    INSERTED.zip,

    INSERTED.mpMapImage

    INTO @ResultsFromInsert

    SELECT 0.0,0.0,[Address],'',[City],[State],Country,Zip,mpMapImage

    From dbo.Map_MapPointXRef

    --now i have a table variable with the new ID so i can insert into some child tables.

    insert into someothertable(MapImageID,othercolumns)

    select MapImageID AS FK,othercolumns

    FROM

    @ResultsFromInsert

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2010 at 6:57 am

    #1236808

    Thank you Lowell!!!!

    WOW! I'm looking into your post and got me very curious as there's new stuff in there that I never knew about.

    Thanks

    JG

  • November 6, 2010 at 5:41 am

    #1247399

    Cool information here

    Clark Anderson

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply