Views vs. Sprocs

I'm trying to get some of our more commonly used sql in one place. Everyone says how bad views are and to use stored procedures instead, but how do you return a data set from a stored procedure? I need to be able to look at a large data set. A view allows me to do that and I don't know how a sproc can. How can I write code within one sproc that pulls a dataset from another sproc? A view allows me to do that easily. I just reference it like I would a table. Everyone says how bad using views is, but I can't find anything on another way of doing what I need to. PLEASE let me know if you know the answer!

Thanks,
Chris

create proc dbo.spc_myproc1<br /> @mycriteria varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />as <br />begin<br />set nocount on<br />select .... <br />from mytable/myview (join,...)<br />where colx = @mycriteria <br /><br />end<br /><br />If you need resultsets from subsequent sp-s you'll need :<br />create table @mytemptb (...)<br /><br />insert into @mytemptb <br />exec dbo.spc_myproc2<br /><br />select ... from @mytemptb <br /><br />

Even using views r great at times as it solves problem and indexed views r optimised views.
Views r even implicitly parsed query.
So idea of using view is good too

Chris,

In QA, create a select statement that returns data. Now add CREATE PROCEDURE dbo.MyProc AS before the select statement, and press F5. This will create a stored procedure. Clear the window, type EXEC dbo.MyProc, and press F5 again: you should see the same data appear.

So basically, if you have a stored procedure that returns a rowset, then you can invoke it through the same channels as any SELECT statement.

This can also save you a lot of programming if you're retrieving a lot of individual values from a stored procedure: just let the last line of the procedure say SELECT @val1 AS [val1], @val2 AS [val2] etc., and you can read the [val1] and [val2] fields straight from your recordset object.

It depends on your client application, but it should all be transparent - the key property being ReturnsRecords.

The beauty of stored procedures is that you can pass criteria as dynamic parameters, which you can never ever do when using views.