SCALAR-VALUED FUNCTIONS Scalar-Valued Functions A scalar-valued function in

SCALAR-VALUED FUNCTIONS Scalar-Valued Functions A scalar-valued function in SQL Server has input parameters, just as a procedure does, but those parameters can only be passed in by value. In addition, it returns a scalar value, not a return code. The return value can be of any of the types that input parameters can use. The following code is an example of a simple function written in T-SQL. CREATE FUNCTION RepeatSql (@repeat INT, @str NCHAR(1)) RETURNS NCHAR(30) AS BEGIN RETURN Replicate(@str, @repeat) END Repeattakes as input parameters an intand a character, and returns a string with the character repeated. The following SQL batch uses it. PRINT dbo.RepeatSql(5, A ) GO AAAAA Using Table 3-1 as a guide, as we did for building the PassCharsprocedure, we can write a method that duplicates the RepeatSql function, which is shown in the following code fragment. static public SqlString Repeat(SqlInt32 i, SqlString c) { return new String(c.Value[0], i.Value); } The Repeat method is not doing the error checking it should just for the sake of keeping the example small. The syntax for adding a function is similar to that for adding a procedure except that it requires an additional piece of information, the return type. The following code is a SQL batch for creating a function from the Repeatmethod. CREATE FUNCTION Repeat(@i INT, @c NCHAR(1)) RETURNS NCHAR(100) EXTERNAL NAME functions.[Chapter3.Functions].Repeat In spirit, the difference between procedures and functions is that a function returns a value that can be used as part of a query, a computed column, or an index. There is no way to use a procedure directly in a query or a computed column. The following SQL batch shows and uses a table that includes a computed column that uses the Repeatfunction.

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

Bookmark the permalink.

Comments are closed.