PROCEDURES desirable to copy the behavior of a

PROCEDURES desirable to copy the behavior of a SQL Server type. A SqlType has a Value property, which has its value in terms of its corresponding CLR type. A SqlType can be cast to another SqlType and to a CLR type, and some CLR types can be cast to SqlTypes. So far we have covered the types typically used for parameters and return values in methods that will be used for procedures and functions. Now we will look at how to use a CLR method as a SQL Server procedure, function, or trigger. Procedures A SQL Server procedure has parameters and a return code. The parameters themselves may be passed by value or by reference. The return code from a procedure is, by convention, meant to indicate the error status that results when the procedure is executed. Typically, a return code of 0 indicates that the procedure executed with out any error. A nonzero result code is used to indicate what part of the procedure failed. A SQL Server procedure is created using the CREATE PROCEDURE command. Before we look at the all the details of implementing a SQL Server procedure with the CLR, let s first look at a procedure implemented using T-SQL and then implement an equivalent procedure using a method. Note that this example violates the convention of using a return code only for an error status just to make the example compact and show the construction of a stored procedure. Stored procedures typically manipulate a database by, for example, inserting something into a table. Chapter 4 will discuss how to write functions that manipulate the database; this example is just to illustrate the construction of a function that implements a stored procedure. CREATE PROCEDURE PassCharsTSql (@c nvarchar(100)) AS BEGIN RETURN LEN(@c) END When this procedure is run, the return code will indicate the number of characters in @c. A short SQL batch that shows this follows. DECLARE @i INT EXEC @i = PassCharsTSql bcd PRINT @i GO

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

Bookmark the permalink.

Comments are closed.