0

I would like to get executed datatype in SQL Server. Have you got idea what I have to do? 

USE [MO]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[SBC]
        @UserId = 1,

SELECT  'Return Value' = @return_value
GO

Returned Value:

Name  ID
----------
Adam  1
Jhon  2

For example ID is int or bigint? I can only execute existing procedure and here is a problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rafał Developer
  • 2,135
  • 9
  • 40
  • 72

1 Answers1

0

That 'return value' is meant to be an execution status indicator, and is an integer. It is not meant to pass back data. Standard values are 0 for success, 1 for failure, but you can pass back any integer in your SP to reflect a number of different statuses.

From the SQL Server Help file:

@ return_status Is an optional integer variable that stores the return status of a module. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.

When used to invoke a scalar-valued user-defined function, the @ return_status variable can be of any scalar data type.

If you want to pass back different data types (and be able to query the 'type' of these), use an 'output parameter'. Output parameters can be of any data type, and once the SP exists, you can query the sys.parameters system table, passing in the object_id of your SP (or just join to sys.procedures on object_id and filter procedure name), joining sys.parameters to sys.types (on user_type_id) to get the sql data type name of each parameter, including the output parameters

E.g.

DECLARE @Param3 bigint
exec dbo.MySPName @Param1, @Param2, @Param3 output
SELECT @Param3 --gives you output value

--This will give you the names and data types of all output parameters in this SP
SELECT p.name, t.name as [type], p.max_length, is_output 
FROM sys.parameters p 
JOIN sys.types t ON p.user_type_id = t.user_type_id 
WHERE p.object_id = OBJECT_ID(N'MySPName') 
AND p.is_output = 1

For a scalar valued function, this same query on parameters and types tables will return a nameless parameter (p.name is an empty string/blank) - this represents the return value for the UDF incl the data type. But Store Procedures can only have the 'return status' integer.

ari
  • 61
  • 3