18. Stored Procedure--sql

good text to top :

• Advantages and disadvantages of SQL stored procedures
• SQL Server Stored Procedures

I. Creating and using Stored procedures

Oracle version:

CREATE PROCEDUREMailinglistcount (ListCount outINTEGER) isv_rowsINTEGER;BEGINSELECT COUNT(*) intov_rows fromCustomersWHERE  notCust_email is NULL; ListCount:=v_rows;END;

Analysis
This stored procedure has a parameter named ListCount. This parameter returns a value from the stored procedure instead of passing a value to the stored procedure. The keyword out is used to indicate this behavior.
Oracle supports in (pass value to stored procedure), out (return value from stored procedure, as here), INOUT (pass value to stored procedure also returns value from stored procedure) type parameter
Number. The code for the stored procedure is enclosed in the begin and end statements, where a simple SELECT statement is executed that retrieves the customer with the mail address. Then use the number of rows retrieved to set the
ListCount (the output parameter to be passed).

Use:

var  Number EXEC Mailinglistcount (: returnvalue); SELECT returnvalue;

Analysis
This code declares a variable to hold any value returned by the stored procedure, executes the stored procedure, and then uses the SELECT statement to display the returned value.

SQL Server version:

 create  procedure   Mailinglistcount  as  declare  Span style= "color: #008000;" > @cnt  integer  select   @cnt  =  count  (*  )  from   where  not  Cust_email Span style= "color: #0000ff;" >is  null  ;  return   @cnt ; 

Analysis
There are no parameters for this stored procedure. The calling program retrieves the values supported by the return code of SQL Server. Where a local variable named @cnt is declared with the DECLA RE statement (in SQL Server
All local variable names begin with @), then use this variable in the SELECT statement to include the value returned by the count () function, and finally, return @cnt statement to return the count to
Back to the calling program.

Call:

DECLARE @ReturnValue INT EXECUTE @ReturnValue = Mailinglistcount; SELECT @ReturnValue;

18. Stored Procedure--sql