>Kinda a random question but it has crossed my mind as I have worked through the many posts - what are stored procedures written in when they are contained within SQL Server or PostgreSQL server? I would assume it is some sort of function call that runs some sort of script?
>
>Albert

In my opinion, there are different scenarious where stored procedures may make sense or not:

1) You write an application for a database backend, which is managed by a DB Amin. In this case, you get the specs for the database schema and stored procedures you should use. You may not be able to modify the schema or to create stored procedures, as they are managed on their side. ThIs is a perfect scenario where stored procedures (and views) make the most sense, because the DB Admin can optimize those queries based on their needs and in fact they have more knowledge about their database design as you do. The upside of this Scenario is that you don't really need to care that much which backend server is being used, except for those query syntax differences that are other than ANSI standard. There are DB ADmins that even force you to use exclusively stored procedures and you are not allowed to access the tables directly.

2) You manage the database from within the application, you even have sa privileges to create tables and stored procedures, and you support only one database backend. In this case best is to define the database schema and stored procedures in the application and a migration strategy will keep those updated on the server. This also is a perfect scenario of stored procedures, because you can create complex opimized processes that are run entirely on the server side. What I do in this case, with each database migration I drop the stored procedures and create them with their newest versions automatically. The procedures themselves are defined in VFP classes. An implementation could look like the following:

LOCAL loStoredProcedure AS spHandler OF database.vcx
loStoredProcedure = NEWOBJECT("spHandler_complexSP", "database.vcx")
loStoredProcedure.Create()

3) You manage the database yourself, but want or need to support different database backends: This will become much more complex, since you Need to maintain the stored procedures simultaneously, and have to duplicate those. That means you must violate the dry principle. Best strategy would be to use stored procedures only where they help you to write standardized queries, like function calls, and you don't expect those procedures to change ever. Like an ISNULL() wrapper function. For the rest you would try to use only ANSI Standard queries and move different flavors into post processing. The latter is a good way to avoid duplication, unless performance becomes an issue, then you need to deal with exceptions. But the first and preferred query will always be the standard that you can use on any backend.
For exceptions you could implement a factory pattern:

LOCAL loStoredProcedure AS spHandler OF database.vcx
loStoredProcedure = NEWOBJECT("spHandler", "database.vcx")
loStoredProcedure = loStoredProcedure.GetStoredProcedure("complexSP","MSSSQL")
loStoredProcedure.Create()

Here an example of a stored procedure definition in my code which runs with every database version migration:

LOCAL lcQuery
*
TEXT TO lcQuery TEXTMERGE NOSHOW
CREATE PROCEDURE [dbo].[UserHasAccess]
	@tnCoID int = 0, 
	@tcUsKey varchar(36) = ''
AS
BEGIN
	IF @tcUsKey = 'DISTRIBUTOR'
	BEGIN
		SELECT 1
	END
	ELSE
    DECLARE @lcSQL nvarchar(max)
    SET @lcSQL  = 'select CAST(COUNT(*) AS int) AS FXCount from ' + 'Payroll' + ltrim(str(@tnCoId)) + '..Roles' + ' where DelFlag = 0 AND roUsKey = @tcUsKey'
    EXECUTE sp_ExecuteSQL @lcSQL, N'@tcUsKey varchar(36)', @tcUsKey
END
ENDTEXT
*
IF THIS.CheckExist("UserHasAccess")
	THIS.DoSQL("DROP PROCEDURE UserHasAccess")
ENDIF
*
LOCAL llRetVal
llRetVal = THIS.DoSQL(m.lcQuery)
*
RETURN m.llRetVal