How can i set an integer varable using stored procedure

Question

Hi there.

exec SP_HastaIcmal_AktifKaliciHastalar 25

it returns 86.

DECLARE @iAktifKaliciHastalar int

SET @iAktifKaliciHastalar = exec SP_HastaIcmal_AktifKaliciHastalar 25

that code return error.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'exec'.

Answer 1

DECLARE @iAktifKaliciHastalar int

EXEC @iAktifKaliciHastalar = SP_HastaIcmal_AktifKaliciHastalar 25

This should work fine :-)

Answer 2

Assuming the value is returned as a return code from the sproc (i.e. RETURN 86), then:

DECLARE @iAktifKaliciHastalar int
exec @iAktifKaliciHastalar = SP_HastaIcmal_AktifKaliciHastalar 25

If it's being returned as a resultset, I'd recommend changing it to being returned as a return code like above, or as an OUTPUT param (return codes can only be integers), as long as it's only ever going to return a single value.

Answer 3

DECLARE @iAktifKaliciHastalar int

EXEC @iAktifKaliciHastalar = SP_HastaIcmal_AktifKaliciHastalar 25

This should work fine :-)

Answer 4

DECLARE @iAktifKaliciHastalar int 
exec @iAktifKaliciHastalar = SP_HastaIcmal_AktifKaliciHastalar 25 
print @iAktifKaliciHastalar

return 86 from sp but print 0

Answer 5

In addtion to the EXEC @var = ProcName solutions, you could use output parameters which means changing your stored proc of course

DECLARE @iAktifKaliciHastalar int

EXEC SP_HastaIcmal_AktifKaliciHastalar 25, iAktifKaliciHastalar OUTPUT