Stored Procedures are like a subroutine in a traditional programming language. They are stored within the database, and normally executed outside the control of the database application. In VistaDB you may use CLR Procs and T-SQL Stored Procs.
Typical uses for Stored Procedures are business logic, and complex data validation beyond the abilities of simple constraint checking. Some companies use stored procedures as a way to centralize logic that was originally implemented in the client layer. If five client applications all need to insert invoices in a database, centralizing the logic ensures that all applications perform the logic in the same manner, and that any changes in that process are encapsulated in the stored procedure.
Another traditional argument for stored procedures is that they are executed on the server. This is not always the best design for all application models. If your application has 50 clients and 1 server, where is the CPU bottleneck likely to occur? The server resources are probably at the highest premium. The more logic you can parallelize out to the clients the more scalable your application will become.
CLR Procs are stored procedures written in a CLR language (C#, VB.NET, C++.NET, etc). These types of stored procedures use all the power of your managed language, but can be more complex to program. VistaDB supports the same programming style for CLR Procs as SQL Server 2005/2008.
Another advantage of CLR Procs is the rich programming environment of the Dot Net runtime. Need to login to a remote server and check email? You can't do that within a TSQL Proc.
See the CLR Stored Procedures and Functions topic for more information.
T-SQL stored procedures (SP's) are written in SQL. They normally contain conditional logic to trap errors, and execute entirely in the process of the SQL interpreter. VistaDB is the only non Microsoft database to support the same dialect as SQL Server for T-SQL Stored Procs. The current implementation is designed to allow programmers ease of transition from VistaDB to SQL Server. In general you can typically expect all VistaDB T-SQL procs to execute in SQL Server, but not the other way around. If you use sp_ functions, and other Microsoft specific functionality your SPs will probably not translate to VistaDB. If you instead look at the VistaDB functionality as your minimum feature set, your applications will scale to SQL Server with little difficulty.
Please view the topic on TSQL Proc Creation.
Please view the topic on TSQL Proc Removal.
NOTE: See the related topics section for information on how to get the list of current TSQL Procs and UDFs in the current database.
Not all SQL Server features are supported. See the feature topic for more information.
SQL Functions are like SQL Procedures except that they may also return any data type, including Tables. You can create a custom table inside a SQL Function and return it as the output. This is quite handy to avoid the old method of building up XML when you need multiple entries to a function or procedure.