SQL Server stored procedures and calls detailed

Using SQL Server stored procedures can greatly improve the running speed of programs, simplifying the difficulty of programming maintenance, has been widely used.

The
stored procedure is actually a collection of Transact-SQL statements that are saved to accept and return user-supplied parameters.


can create a procedure for permanent use, or for temporary use in a session (local temporary procedures), or for temporary use in all sessions (global temporary procedures).


can also create stored procedures that run automatically when Microsoft SQL Server starts.





to use stored procedures, first we must familiarize ourselves with some basic T-SQL statements, because the stored procedures are made up of a set of T-SQL statements, and we need to understand some concepts about functions, procedures, because we need to call the stored procedures in the application. Just as we call the application's function, the method is somewhat different.


below let's take a look at how the stored procedures are built and used.



First, create stored procedures


and datasheet, we need to create a stored procedure before using it, and its concise syntax is:





Reference:


Create PROC Stored procedure name


[argument list (multiple to, delimited)]


as


SQL Statement





Example:





Reference:


Create PROC upgetusername


@intUserId INT,


@ostrUserName NVARCHAR Output-the parameters to be exported


as


BEGIN


-assigns the uname value to the @ostrUserName variable, which is the parameter to output


Select @ostrUserName =uname from Uuser Where uid= @intUserId


End





Where the Create PROC statement (the full statement is created PROCEDURE) means telling SQL SERVER that you now need to establish a stored procedure, upgetusername is the stored procedure name, @intUserId, and @ Ostrusername is the two parameters of the stored procedure, note that in SQL Server, all user-defined variables begin with "@", and the OUTPUT keyword indicates that the parameter is used for outputting, and the following is the stored procedure content. As soon as the above code is executed in Query Analyzer, SQL Server creates a stored procedure named "Upgetusername" in the current database. You can open "Enterprise Manager", select the database of the current operation, and then select "Stored Procedures" in the tree list on the left, where you can see the stored procedure you just created in the list on the right (if not, refresh it).





Second, call to stored procedure




Before
we had created a stored procedure called "upgetusername", which literally understood the function of the stored procedure to obtain the name of a particular user. The stored procedure is set up, and the next step is to call it in the application, and look at the call in the ASP program.





Reference:


Dim Adocomm


'//Create an object that we use to invoke the stored procedure


Set Adocomm = CreateObject ("Adodb.command")


with Adocomm


'//Set connection, set Adoconn as already connected ADODB. Connection Object


. ActiveConnection = Adoconn


'//type is stored procedure, adCmdStoredProc = 4


        . CommandType = 4


'//stored procedure name


        . CommandText = "Upgetusername"


'//set user number


        . Parameters.item ("@intUserId"). Value = 1


'//execute stored procedure


        . Execute


        


'//Obtain the user name returned from the stored procedure


Response.Write "User name:" &. Parameters.item ("@ostrUserName"). Value


End With


'//Release object


Set Adocomm = Nothing





through the two steps above, we can already create and use simple stored procedures. Let's look at a slightly more complex stored procedure to learn more about the application of the stored procedure.