0

I want to pass the id of the last inserted tuple in the stored procedure as an argument to another stored procedure.

Here are four snippets of code. The first two snippets works correctly:

declare @s bigint
select @s = scope_identity()
exec some_stored_proc @s

and

exec some_stored_proc scope_identity

But both these snippets cause query complete with errors:

declare @s bigint
select @s = scope_identity

and

exec some_stored_proc scope_identity()

I can't figure out what's the fuss with the brackets! How can scope_identity() have different syntax?

superM
  • 8,605
  • 8
  • 42
  • 51

1 Answers1

1

it doesnt have different syntax

its a function and afunction should be executed by ()

SCOPE_IDENTITY()

declare @s bigint
set @s = scope_identity()
exec some_stored_proc @s

should work.

edit

exec XXX should get "ready values" and not "calculated values". this is the reason why you cant send datetime as getDate().

edit2

acording to your sample - i can send getDate without () to a sp.

enter image description here

it doesnt work. it has to go through @param

Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • it DOES work. I just want to understand how does 'exec some_stored_proc scope_identity' work and why doesn't 'exec some_stored_proc scope_identity()' work. – superM Apr 23 '12 at 11:58
  • why does 'exec some_stored_proc scope_identity' work? I use scope_identity like a variable here. – superM Apr 23 '12 at 12:02
  • @superM we should use according to the syntax. maybe its a bug - didnt test it... there is no such thing scope_identity. its a function name which should be executed by (). – Royi Namir Apr 23 '12 at 12:04
  • Yes, maybe it's just a bug. I just was really confused when it did work! – superM Apr 23 '12 at 12:05
  • got confused because sql management studio painted it pink. ))) thanks a lot – superM Apr 23 '12 at 12:08