>Hi
>
>I want to use Scope_Identity to return the newly created record ID in my table. My code, thus far, looks something like:
>
>ALTER procedure [dbo].[Candidate_Insert_Blank]
> @CreatedByID int, @CandidateID int output
>
>as
>begin
> set nocount on;
> insert into Candidate (CreatedByID)
> values (@CreatedByID)
> set @CandidateID = cast(scope_identity() as int)
>end
>
>CandidateID is a field within Candidate and it's automatically incremented using Indentify Increment. In reality, I want the stored procedure to return the value of field Candidate.CandidateID.
>
>Regards
You don't need to cast to int. Other than that, your code is already doing it (returning the ID using SCOPE_IDENTITY).
Alternatively you can use OUTPUT clause in SQL Server 2005 and up.
UPDATE. Actually, you may try casting to int in case your table has a different type of int for identity field (but it would make more sense to return the appropriate type instead).
If it's not broken, fix it until it is.
My Blog