Declaring table variable

  • Guys,

    I am trying compile below function where I pass tablename as variable.

    CREATE FUNCTION [dbo].[GetNextValue] (@tablename varchar(30) )

    RETURNS int AS

    BEGIN

    declare @value int

    truncate table @tablename

    INSERT INTO @tablename (FNAME) VALUES ('SMITH')

    select @value = id from @tablename

    return @value

    END

    I get the following message

    Msg 1087, Level 15, State 2, Procedure GetNextValue, Line 7

    Must declare the table variable "@tablename".

    Any suggestions/inputs to get around this would be helpful

    Thanks

  • Freaky function, really.

    Don't see any point of doing this.

    But if there is some hidden magic behind you can do it this way:

    CREATE FUNCTION [dbo].[GetNextValue] (@name varchar(30) )

    RETURNS int AS

    BEGIN

    declare @value int

    DECLARE @tablename TABLE (

    ID int IDENTITY(1,1),

    FNAME varchar(30)

    )

    INSERT INTO @tablename (FNAME)

    VALUES ('SMITH')

    select @value = id from @tablename

    return @value

    END

    _____________
    Code for TallyGenerator

  • What you are trying to do is invalid SQL.  You do what you want in dynamic SQL, but you cannot do dynamic SQL in a function.

     

     

  • I was able to do this through dynamic sql in a stored procedure.

    Thanks all for your help

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

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