UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Stored Proc V Views, Any Versions    
 
   
duggie
post Oct 23 2017, 03:16 PM
Post#1



Posts: 405
Joined: 14-October 12



Can someone tell me the difference between a Stored Procedure and a View?

My understanding is an SP is a packaged query, ie instead of writing the same query over many times, you create an SP and execute it.

As for a View, it's a virtual table.

If you can provide VBA equivalents, that'll be great.

Thanks
Go to the top of the page
 
MadPiet
post Oct 23 2017, 03:52 PM
Post#2



Posts: 2,257
Joined: 27-February 09



Huge differences...

Check this article out: https://stackoverflow.com/questions/5194995...dure-and-a-view

See the section by cusimar9
Go to the top of the page
 
theDBguy
post Oct 23 2017, 07:04 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,217
Joined: 19-June 07
From: SunnySandyEggo


Hi,

My over simplified translation is Views in SQL Server are equivalent to Queries in Access, and Stored Procedures are equivalent to User Defined Functions.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Oct 23 2017, 07:20 PM
Post#4


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


One more perspective, saying the same things as have been pointed out a little differently.

Stored Procedures CAN, but do not have to, include SQL statements to perform tasks, much like Subs and Functions do in VBA and action queries do.

View are SQL statements that return records, like SELECT queries do.

--------------------
Go to the top of the page
 
PhilS
post Oct 24 2017, 03:38 AM
Post#5



Posts: 404
Joined: 26-May 15
From: The middle of Germany


A View is basically a stored single Select statement, very much like a normal Access Query.

A Stored Procedure can contain procedural logic and any number of arbitrary SQL statements, which might (or might not) return a recordset each.

--------------------
Go to the top of the page
 
BruceM
post Oct 24 2017, 07:10 AM
Post#6


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


A difference between a view and a stored Access query is that a view cannot be used to modify a table. In other words, it is read only. However, a query based on the view can be used to modify a table, if the structure allows it. An Access query that includes outer joins is read only. The same structure as a view is read only because views are read only. A query based on the view is also read only because of the joins.

A SQL Server query is not saved as a query directly, as nearly as I can make out. Rather, it is stored as something else, either a view or a stored procedure. Or maybe a user-defined function? I haven't figured those out yet.

Another distinction between views and queries is that views do not support sorting. If you add ORDER BY you will be prompted on that point.

One difficulty I have encountered while trying to learn about this is that most of the instruction I have read is from the point of view of writing SQL and other coding in SQL Server Management Studio. I am still trying to find practical information about how to implement the various SQL Server objects to best effect when using SQL Server as the back end for an Access database. Should a data entry form, using a single table as the record source, be based on an Access query that selects from that table? Or should I do the sorting and so forth in a SQL Server view, then do a simple SELECT from that view.

To summarize, tables are pretty much the same in both Access and SQL Server. Other objects in Access and SQL Server, such as queries and views, have some similarities to each other, but are not equivalent.
Go to the top of the page
 
PhilS
post Oct 24 2017, 07:49 AM
Post#7



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
A difference between a view and a stored Access query is that a view cannot be used to modify a table. In other words, it is read only.

This is incorrect. Views are not at all read-only in general. It depends on the nature of the SQL behind the view. To a large extend the same rules as for Access select queries apply in regard to writeability of a view.

QUOTE
A SQL Server query is not saved as a query directly, as nearly as I can make out.

A SQL Server query is just a SQL string that is executed. You can incorporate a query into a view, stored procedure or a function.

QUOTE
Another distinction between views and queries is that views do not support sorting.

Correct. You are supposed to apply the sorting in the actual query that fetches the data from the view. However, it is allow to sort a view in combination with the TOP-clause. You can use this as a workaround to create a view as SELECT TOP 100 PERCENT * FROM yourTable ORDER BY yourColumn. - Still, I would rather recommend to sort the data in the SQL query on top of the view.


QUOTE
I am still trying to find practical information about how to implement the various SQL Server objects to best effect when using SQL Server as the back end for an Access database.

Unfortunately my time does not permit to cover this in depth right now. Just two important hints:
  • Never ever bind any Access Form or Report to an unfiltered SQL Server Table! Always limit the data to the (very few) records the user wants to actually see/edit.
  • For simple queries on a single table or very few tables there is little difference if you query the table directly or create a view on the server and query that instead. The more complex the query gets (particularly in Outer Joins), the better a view on the server will perform compared to an Access Query.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Oct 24 2017, 08:21 AM
Post#8


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Another distinction that I think hasn't been mentioned (pardon me if I missed it), is that stored procedures can be parameterized, as is the case with a UDF in Access.

Here's an example of a stored proc based on a view:

Passing in a month , e.g. 8, returns all of the people who have birthdays in August, along with their current age.

CODE
CREATE procedure  [dbo].[sp_BirthdaysByMonth]
    @BthMnth [int]=1

As
    SET NOCOUNT ON

    SELECT DateName(m,vw_Individual_ByNames.Birthdate) AS BirthMonth, IndividualID, LastName, FirstName,
     Birthdate,CurrentAge, Month(vw_Individual_ByNames.Birthdate) AS SortMonth
    FROM dbo.vw_Individual_ByNames
    WHERE Month(vw_Individual_ByNames.Birthdate) = @BthMnth  
    AND vw_Individual_ByNames.IndividualInactive is  null  
    AND vw_Individual_ByNames.HouseholdInactiveDate IS  NULL
    AND vw_Individual_ByNames.Birthdate IS NOT NULL
    ORDER BY Year(vw_Individual_ByNames.Birthdate)


Here's the view on which it is based:
CODE
CREATE VIEW [dbo].[vw_Individual_ByNames]
AS
SELECT  DISTINCT
      FLG.FamilyLine
      , FLG.HouseholdID
      ,I.IndividualID
      , I.FirstName
      , I.MiddleName
      , I.LastName
      , I.NickName
      , I.Suffix
     , CASE
        WHEN IP1.FMILName Is Null
        THEN ''
        ELSE IP1.FMILName
        END  +
       CASE
        WHEN IP1.FMILName Is Null or IP2.FMILName Is Null
        THEN '' ELSE' & '
        END +
       CASE
        WHEN IP2.FMILName Is Null
        THEN ''
        ELSE IP2.FMILName
       END
    AS [Parent(s)]
    , dbo.CurrentAge(I.Birthdate) AS CurrentAge
    , CAST(I.Birthdate AS VARCHAR(10)) AS Birthday
    , i.BIRTHORDER
    , I.Birthdate
    , I.SpouseSOID
    , ISNULL(I.Parent1ID,0) AS Parent1ID
    , I.Parent2ID
    , I.InactiveDate AS IndividualInactive
    , IH.InactiveDate AS HouseHoldInactiveDate
    , I.RelationshipTypeID
    --, IH.HouseholdID
    , IH.HouseholdRoleID
    , H.HouseHoldName + Cast(IH.HouseholdID as nvarchar(4) ) As UNIQUEHH
    , I.InactiveDate
FROM dbo.tblIndividual AS I
LEFT OUTER JOIN
     dbo.tblIndividualHousehold AS IH
ON I.IndividualID = IH.IndividualID
LEFT OUTER JOIN
DBO.tblHousehold H
ON IH.HouseholdID = H.HouseHoldID
LEFT OUTER JOIN tblIndividual AS IP1
ON IP1.IndividualID = I.Parent1ID
LEFT OUTER JOIN tblIndividual AS IP2
ON IP2.IndividualID = I.Parent2ID
LEFT OUTER JOIN [dbo].[FamilyLineGroups] AS FLG
ON IH.IndividualID = FLG.IndividualID


And, just to throw a new wrinkle in, the view includes a SQL Server Function, also including a parameter, which is Date of Birth, or DOB, from which the current age is calculated and returned to the view for each person:

CODE
CREATE FUNCTION [dbo].[CurrentAge]
(
    @DOB datetime
)  
RETURNS int
AS
BEGIN
      DECLARE @Age int
SELECT    
@Age  = YEAR(GETDATE()) - YEAR(@DOB ) -
(CASE
   WHEN (MONTH(@DOB ) > MONTH(GETDATE()))
   OR (MONTH(@DOB ) = MONTH(GETDATE())
   AND DAY(@DOB ) > DAY(GETDATE()))
   THEN 1  
   ELSE 0
END)  
RETURN @Age
END

GO

--------------------
Go to the top of the page
 
BruceM
post Oct 24 2017, 10:58 AM
Post#9


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


OK, I must have misunderstood something about views. For the past few weeks at least half my time has spent trying to learn how to work with SQL Server as the back end for Access databases, so I can't remember where I came across some of the information. But in several places I read that a view cannot be used to modify a table. Perhaps that meant it can't be used as an action query.

QUOTE
Always limit the data to the (very few) records the user wants to actually see/edit.

In my limited experience so far I have found that the biggest bottleneck is with related data rather than the number of records. I have a form that typically displays about 300 records. However, it has four subforms, one of which has two subforms of its own. Users scroll back and forth through the records, typically accessing 50 or more each day. With an Access back end it takes about eight seconds to scroll forward 100 records. With SQL Server it is close to two minutes. It is also possible to navigate directly to a record via one of more search boxes, but the users find the scrolling convenient in many cases. I am not looking forward to telling them they will have to alter their approach, and use the direct search rather than scrolling for most purposes.

George, that clears up some of how the objects can be interconnected. I know a stored procedure can be executed from within Access via a passthrough query or by opening an ADODB connection. It seems the resulting recordset is read-only in every case, although of course I could be wrong about that too. I haven't quite sorted out when I would use a stored procedure instead of some other method, but I may recognize the situation when I come across it. I expect it would be a good way to do update or append actions, for one thing.

Anyhow, I'm not trying to hijack the thread, although perhaps I have already. If so, sorry about that.
Go to the top of the page
 
duggie
post Oct 24 2017, 03:55 PM
Post#10



Posts: 405
Joined: 14-October 12



Thanks to all for the replies.

"A View is basically a stored single Select statement, very much like a normal Access Query.

A Stored Procedure can contain procedural logic and any number of arbitrary SQL statements, which might (or might not) return a recordset each."


Does this mean what you can do in a View, you can do in an SP but not the other way round, ie Views are a subset of SPs?
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2017, 05:59 AM
Post#11


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Yes, and NO!

You can generally write stored procedures that return the same recordset as a view, yes.

To call one a subset of the other is a case of heading down the wrong path.

--------------------
Go to the top of the page
 
Minty
post Oct 25 2017, 06:15 AM
Post#12



Posts: 70
Joined: 5-July 16



QUOTE
I haven't quite sorted out when I would use a stored procedure instead of some other method, but I may recognize the situation when I come across it. I expect it would be a good way to do update or append actions, for one thing.


As mentioned another reasons is to pass parameters into a procedure which you can't do with a view. I have a complicated process that works out time spent across different charging bands for engineering work.
To query the entire systems data would takes many many minutes as it involves creating temporary tables within the stored procedure, and then calculating result sets for different customers with different charging values per period. (In hours / out of hours / weekends etc)
By passing in a specific customer, and their contract details from Access I get a result set that is returned almost instantly.

When I attempted this in Access it took forever or timed out.
Go to the top of the page
 
PhilS
post Oct 25 2017, 08:00 AM
Post#13



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
Does this mean what you can do in a View, you can do in an SP but not the other way round, ie Views are a subset of SPs?


The first part is correct, you can do in a SP what you can do in a view. - The second part not so much.

You should not think of Views as a subset of SPs. They are different. SPs are routines that get executed and do what they were programmed to do.
Views behave like a table. You can use them in select statements (in insert, update, and delete statemens as well, if they are updatable). You cannot do that with SPs.

--------------------
Go to the top of the page
 
duggie
post Oct 25 2017, 08:29 AM
Post#14



Posts: 405
Joined: 14-October 12



Thanks.
Go to the top of the page
 
BruceM
post Oct 26 2017, 07:48 AM
Post#15


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


QUOTE
As mentioned another reasons is to pass parameters into a procedure which you can't do with a view
I believe I have grasped the purpose and limitations of views, if not all the nuances. For instance, I am not certain when it makes more sense to create a View of, say, active (current) customers and another for all customers, and when it would be best to have the one View for all customers and select subsets from that. Just mentioning it for now, not trying to start a new thread within a thread.

What I should have said is I haven't quite sorted out when to use stored procedures and when to use user defined functions. Again, I'm not trying to get into that discussion here, but I do understand that a scalar UDF is appropriate for returning a value such as a calculation result, conceptually similar to the way a VBA procedure returns a calculation result. I won't worry about table UDFs for now.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:32 PM