Search code examples
sqlsql-servert-sqlexeclocal-variables

How do I set a local variable to the returned value from an execute in SQL

In the following code I'm trying to set a local variable to what is returned from an execute statement in TSQL.

A variable declared: @id The the execute takes a type, and an increment amount, and returns a value The desired outcome is to set the value of what is returned to the variable @id

The select statement below shows @id as 0 and the execute appears to be returning the value but not setting it to @id. Researching/googling states this is how I should set a local variable to what the execute statement returns but I'm clearly not doing it right.

declare @id int = -1 
exec @id = AP_GET_NEXTID @type = 'CT', @increment = 1
print @id
select @id

Next ID seems to be returned but not set to @id field.

enter image description here

Solution

  • Try something like ...

    CREATE TABLE #Result ( NextID int )

DECLARE @id int

INSERT #Result exec AP_GET_NEXTID @type = 'CT', @increment = 1

SELECT TOP 1 @id = NextID FROM #Result

PRINT @id

SELECT @id