I'm weighing the potential performance impact of using one of three different methods of returning a single, scalar value from a stored procedure to my C# routine. Can anyone tell me which of these is "faster" and, most importantly, why?
Method 1:
CREATE PROCEDURE GetClientId
@DealerCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
SELECT ClientId
FROM Client
WHERE ClientCode = @DealerCode
END
-- this returns null if nothing is found,
-- otherwise it returns ClientId in a ResultSet
Method 2:
CREATE PROCEDURE GetClientId
@DealerCode varchar(10),
@ClientValue int out
AS
BEGIN
SET NOCOUNT ON
set @ClientValue = -1
set @ClientValue = (SELECT ClientId
FROM Client
WHERE ClientCode = @DealerCode)
END
-- this returns -1 for ClientValue if nothing is found,
-- otherwise it returns ClientId
-- the value for ClientValue is a scalar value and not a ResultSet
Method 3:
CREATE PROCEDURE GetClientId
@DealerCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
declare @ClientValue int
set @ClientValue =
(SELECT ClientId FROM Client WHERE ClientCode = @DealerCode)
if @ClientValue is null or @ClientValue = 0
return -1
else
return @ClientValue
END
-- this uses the return value of the stored procedure;
-- -1 indicates nothing found
-- any positive, non-zero value is the actual ClientId that was located
The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.
Return Value in SQL Server Stored Procedure In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement.
The simplest kind of SQL Server stored procedure that you can call is one that contains no parameters and returns a single result set. The Microsoft JDBC Driver for SQL Server provides the SQLServerStatement class, which you can use to call this kind of stored procedure and process the data that it returns.
Returning a scalar value is more efficient than a result set, the reason is result set carries lot more helper methods along with it, which makes it heavy thus increasing latency in transmission of the object from sql to C# code/routine.
In your method 3: You have used a variable to return the value this is more better than sending an out parameter since here you are cutting down on traverse of an object atleast in one route ( i.e., when invoking the stored procedure).
A result set is more flexible than an output parameter because it can return multiple rows (obviously), so if you need a result set then it's the only choice anyway.
To order the queries based on performance that goes as Method 3, Method 2 Method 1.
Hope this is helpful in understanding the concept.
In terms of performance penalty, method 3 (RETURN) is penalty-free. The reason being that SQL Server will always return an integer result code from a Stored Procedure. If you do not explicitly specify one, then it it will implicitly return 0 (SUCCESS).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With