Stored Procedure to return/result a string

I just started working/learning store procedures. I'm just wondering if it's possible for a Store Procedure to return a string.

Right now the store procedure that i'm working on takes one parameter (customer #) and returns two fields- vehicle # and ETA. I'm just wondering if this could be put on a string like "Your vehicle # is 1234 with ETA in 10 minutes." or some kind of a message if no val开发者_运维知识库ue is returned.

Thanks in advance.

CREATE PROCEDURE GetVehicleInfo 
@CustId INT 
OUTPUT AS

  RETURN (SELECT 'Your vehicle # is ' + id + ' with ETA in ' + ETA + ' minutes' 
          FROM YourTable WHERE CustId = @CustId )

GO

Well, once I was returning a full link :) In MSSQL for instance, you might first declare a result table in the procedure and at the end of the procedure you can do the following statement:

SELECT 'whatever string you want to put' + columnName + ' whatever...' FROM @result

Where @result is the declared table

Edit: case where the columnName type is INT you can use the CONVERT function. Hope this helps.

For Sql server, it is as simple as the below code:

select 'Your vehicle # is ' + vehiclenum + ' with ETA in ' eta ' minutes' from table_1
where your_select_criteria

In that sql vehiclenum and eta are both column names from table_1 but you can equally do a join in this query.

If vehiclenum is an int then you need to convert it using the convert function like so:

select 'Your vehicle # is ' + convert(varchar(10), vehiclenum) + ' with ETA in ' eta ' minutes' 
from table_1
where your_select_criteria

The last piece of the puzzle is getting the value out into code. As it is, this will return a record set.

Using the SqlClient you would use the ExecuteScalar method as describe here on MSDN.

var outputText = cmd.ExecuteScalar();