Stored Proc that has output values

Question

Hello, I am new to TSQL. I need to create a stored proc that will return records (more than one record). I would like to return back AccountID, FirstName, LastName & email.

For example, I am comparing two tables using the AccountID fields. So currently my query is:

Select AccountID, FirstName, LastName, email from tblCustomers Where AccountID not in (select AccountID from tblVendors)

Answer 1

you just do as may select statements as you need within the Stored Procedure then depending on what language you are using you can loop through the result sets to get the data.

hope that helps,

Josh

Answer 2

Not sure what your question here is...but you could create that procedure with

CREATE PROCEDURE dbo.spGetNonVendors
AS
BEGIN
  Select AccountID, FirstName, LastName, email 
  from tblCustomers Where AccountID not in (select AccountID from tblVendors)
END

See here for syntax for created stored procedures:

http://msdn.microsoft.com/en-us/library/ms187926.aspx

As for how to get the output, you should know there are actually three different sources of output from a stored procedure:

1. Output parameters
2. Result sets
3. Messages

If you look at the docs on the page I referenced, you'll see how to declare output variables. Those are good for single values, but not what you're looking for here.

Result sets are just any select statement you happen to run while in the procedure. As you've seen from the answers here, there's nothing special to do, just execute the select.

Finally, the message output contains error messages, print statements, etc.

Now how you get to your select output depends on your client. How are you connecting to sql server and executing the procedure? If you're using Sql Management studio, you'll see the two tabs for result sets and messages. If you're using C#, a SqlDataAdapter or SqlDataReader will automatically be reading from your result sets.

Answer 3

CREATE PROCEDURE Procedurename
AS
BEGIN
    Select AccountID, FirstName, LastName, email
    from tblCustomers
    where AccountID not in (select AccountID from tblVendors)
END

Answer 4

So, just put it in a procedure:

create procedure CompareCustomers
as

select AccountID, FirstName, LastName, email
from tblCustomers
where AccountID not in (select AccountID from tblVendors)

I would however prefer a join over "not in":

create procedure CompareCustomers
as

select c.AccountID, c.FirstName, c.LastName, c.email
from tblCustomers c
left join tblVendors v on v.AccountID = c.AccountID
where v.AccountID is null