0

I want to call SCOPE_IDENTITY() within a parameterized query

I know using the statement works in SQL, and I know I can do it if I don't use a parameterized query.

    insert_stmt = 'INSERT INTO [some_table] ([value_1], [value_2], [value_3], [value_4]) VALUES ( ?, ?, ?, ? )'
    insert_values = [some_varchar_1, some_int_2, SCOPE_IDENTITY(), some_varchar_4]

    with cnxn:
        cursor.execute(insert_stmt, insert_values)

I expect the function to execute on the server to correctly populate that value

MosaicOrange
  • 90
  • 12
  • https://stackoverflow.com/questions/37798229/passing-a-date-to-a-function-in-python-that-is-calling-sql-server – CR241 Jan 24 '19 at 19:20
  • 1
    @CR241 that is not a duplicate of this question. Very different situation. – Sean Lange Jan 24 '19 at 19:21
  • 1
    I don't think you want to use SCOPE_IDENTITY here because the value is for the current scope. Since this is a different statement you will get NULL. You should probably return the identity from the previous insert statement and then pass it as a parameter in this insert statement – Sean Lange Jan 24 '19 at 19:22
  • Try placing that value in body of SQL and not as Python value to be parameterized. – Parfait Jan 24 '19 at 19:28
  • @SeanLange I have an identity serial that I'm getting back with `output inserted.[serial_id]`, I just want to also insert it into another field a second time – MosaicOrange Jan 24 '19 at 19:59
  • @Parfait I know that works, I'm just seeing if there is a way to do it with parameterization since I already have a comprehensive function built out for inserts – MosaicOrange Jan 24 '19 at 20:01
  • But that entity is unknown in Python. And there is no reason to parameterize since it resides in the database not application layer (i.e., nothing to pass). – Parfait Jan 24 '19 at 20:04
  • 1
    Your last comment is not making this any more clear. I assumed you want to insert that value into another table. This is very common with foreign keys. But unless this is the same statement you will get NULL with scope_identity. Pretty sure that cursor.execute will close the connection. – Sean Lange Jan 24 '19 at 20:07
  • @SeanLange You're right, I was mistaking what was put in as the correct value, but it wasn't, it was the scope identity from the proceeding query, I'll have to do a specialized query with `OUTPUT INSERTED.[serial_id]` to get the correct value – MosaicOrange Jan 24 '19 at 20:29

0 Answers0