Post reply

Identity function

  • April 20, 2005 at 3:45 am

    #164048

    Hi

    Using the identity function as follows :-

                    IDENTITY(bigint, 1, 1) AS <field name>

     

    However, rather than using 1 as the starting point, my starting point is derived and stored in a variable. However when I use the variable in the above statement, I get a syntax error.

                      IDENTITY(bigint, @StartPoint, 1) AS <field name>

    Incorrect syntax near '@StartPoint'.

     

    Can anyone help please? Can you not use a variable in the statement?

    Thanks.

     

     

     

     

     

  • April 20, 2005 at 3:50 am

    #553711

    It seems when looking in BOL that variables isn't supported.

    /Kenneth

  • April 20, 2005 at 4:19 am

    #553716

    Thanks Kenneth. That's what I had found but was hoping someone had discovered an undocumented  workaround.

     

  • April 20, 2005 at 4:45 am

    #553720

    Why do you need it to be dynamic? What's the reason for using the function this way? There may be some other way to solve the underlying actual problem?

    /Kenneth

  • April 20, 2005 at 5:06 am

    #553727

    Not only this, but AFAIK you can use the IDENTITY function only in conjunction with SELECT INTO. And SELECT INTO used in a proc will cause a recompile which might cause locking and blocking etc...

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 20, 2005 at 7:01 am

    #553760

    A possibility (if you have permission to run DBCC

    DECLARE @StartPoint bigint

    SET @StartPoint = 1001

    CREATE TABLE #Table (rowid bigint IDENTITY(1,1),field1 varchar(10))

    DBCC CHECKIDENT('#Table',RESEED,@StartPoint)

    INSERT INTO #Table (field1) VALUES ('Line 1')

    INSERT INTO #Table (field1) VALUES ('Line 2')

    INSERT INTO #Table (field1) VALUES ('Line 3')

    SELECT * FROM #Table

    DROP TABLE #Table

    result

    rowid                field1    

    -------------------- ----------

    1001                 Line 1

    1002                 Line 2

    1003                 Line 3

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 6 posts - 1 through 5 (of 5 total)

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