SQLTeam.com | Weblogs | Forums

How to Use Stored Procedure?and when?

sql2012
sql2008
sql2014

#1

Hi
I am confused with Stored Procedure And Create View Query, I need Simple explanation.with Example

Thanks,


#2

Stored proc example:

CREATE PROC SomeProc1 (@var1 int, @var2 varchar(50))
AS

SET NOCOUNT ON;

IF @var1 = 1
SELECT Column1, Column2, Column3
FROM Table1
WHERE Column4 = @var2
ELSE
SELECT Column1, Column2, Column3
FROM Table1
WHERE Column4 <> @var2

View example:

CREATE VIEW SomeView1
AS
SELECT TableZ.ColumnA, TableZ.ColumnB, TableZ.ColumnC, TableZ.ColumnD
FROM TableZ
JOIN TableX ON TableZ.ColumnA = TableX.Column1
WHERE TableZ.ColumnW = 1

A view is sort of like a saved query, one that you'll probably use in a bunch of places. You don't want to just copy/paste that SELECT in a bunch of places as what happens if the business logic changes? You'd have to update the code in a bunch of places, whereas if you used a view you'd only have to update the code in the view.

As for a stored procedure, this is what the application will use, generally speaking. The app will exec stored procs to get data back.