Post reply

very simple function won''t work

  • June 8, 2007 at 4:09 pm

    #93100

    i've got this function that i want to reference a table i have just created...it refuses to, saying the object doesn't exist (while outside the function i can insert, query, delete, etc.) any ideas?

    if object_id('testFunction') is not null drop function dbo.testFunction

    if object_id('testTable') is not null drop table testTable

    go

    create function dbo.testFunction() returns int as begin

    declare @i int

    select @i=count(*) from testTable

    return @i

    end

    go

    create table testTable (id_col int IDENTITY (1,1),a int)

    INSERT INTO testTable(a) VALUES(42)

    --this works:

    select count(*) from testTable

    --this doesn't...function errors saying testTable does not exist

    select dbo.testFunction()

    thanks.

  • June 8, 2007 at 4:54 pm

    #711813

    Hmmmmm.  I did a copy and paste of the code you gave and it worked fine for me.  Are you sure that the testTable table was created with the correct owner? 

    And then again, I might be wrong ...
    David Webb

  • June 8, 2007 at 9:44 pm

    #711826

    I would check the owner of TestTable, and make sure it is dbo.

  • June 11, 2007 at 11:11 am

    #712046

    I typed the function back up this morning, and it now works perfectly. No idea what i did differently, probably a stupid mistake somewhere. The user i'm using has full ownership over the database, and the function had no problems referencing other tables, so i must've screwed something up in table creation.

    i did try writing the thing originally at about 6:00 friday evening. could have something to do with it...

Viewing 4 posts - 1 through 3 (of 3 total)

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