Retrieving data

When we query a sql database there are three main ways to return data:

• Result set
• Output parameter
• Return code

In the example below we're assuming a stored procedure to demonstrate these techniques.

Result set

The most common method to return data.

Returns multiple rows of data split by columns.

Used when you want to return multiple rows of data. These might be consumed by an application via ado.net or translated into an object enumeration via your chosen orm.

Stored procedure (proc)

Create a proc that returns a result set of customers

-- proc to demonstrate returning result set
CREATE PROCEDURE up_manage_customers-- create proc named up_manage_customers
AS
BEGIN-- begin proc content
	select tc.first_name-- select the listed columns
	, tc.last_name
	, tc.insert_dt
	from tbl_customers tc-- from this table
	where tc.deleted = 0-- where the customer is not deleted
END

Calling the proc

-- calling the proc
exec up_manage_customers-- execute the given proc

Results

The results of calling the proc showing result set.

Output parm

Return a single piece of data of your chosen type.

Use when you need a single field of a specified sql type and don't require the overhead of creating a result set.

Stored procedure (proc)

Create a proc that returns an output parm containing the date of the most recently inserted customer record

-- proc to demonstrate returning an output parm
CREATE PROCEDURE up_manage_customers-- create proc named up_manage_customers
@most_recent_customer_insert_dt_parm datetime = null out-- declare the output datetime parm, if not supplied default value to null and it's an output (out) parm
AS
BEGIN-- begin proc content
    -- here we select all customers by insert_dt with newest first and then select the first row into the out parm. so the most recent insert_date will be in the out parm
	select top 1 @most_recent_customer_insert_dt_parm = (tc.insert_dt)-- select into out parm the first insert_dt returned
	from tbl_customers tc-- select from this table
	where tc.deleted = 0-- where record isn't deleted
	order by tc.insert_dt desc-- order by insert_date desc (newest first)
END

Calling the proc

-- calling the proc
-- declare the argument that will be supplied to the proc with null value
declare @most_recent_customer_insert_dt_arg datetime = null

-- execute the proc passing the argument to the out parm and add the keyword out to indicate we are expecting a value back
exec up_manage_customers @most_recent_customer_insert_dt_parm = @most_recent_customer_insert_dt_arg out

-- the proc will fill @most_recent_customer_insert_dt_arg, here we just select that value (so we can view it) and give it a more friendly column alias
select @most_recent_customer_insert_dt_arg as MostRecentCustomerInsertDt

Results

The results of calling the proc showing the most recent insert date.

Return code

Return an integer value from the proc.

Use when you only need to return an int, typically a status code with 0 indicating success (no failures). However, you can use a return code to return any int quickly and cleanly.

Stored procedure (proc)

Create a proc that returns a customer count as a return code

-- proc to demonstrate returning an output parm
CREATE PROCEDURE up_manage_customers-- create proc named up_manage_customers
AS
BEGIN-- begin proc content
    return-- return following int
	(
		-- select count of undeleted customers
		select count(*)
		from tbl_customers tc
		where tc.deleted = 0
	)
END

Calling the proc

-- calling the proc
-- execute the proc and note the return code
exec @Return = up_manage_customers

-- display the return code value
select @Return as CustomerCount

Results

The results of calling the proc showing the customer count.

 

Finally, there is nothing preventing you from using all three techniques in a single proc. Just don't create a code tangle.

#SQL