Supongo que el sql server que usas es el de microsoft.
Y creo que preguntas por un procedimiento almacenado llamado desde delphi.
Para obtener el valor devuelto, algo así, es sólo un ejemplo:
Código Delphi
[-]query.parambyname('codigo').asinteger := 1;
query.execsql;
valordevuelto := query.fields[0].asinteger; valordevuelto := query.fieldbyname('valor').asinteger;