CREATE FUNCTION Width(@Size int)
RETURNS int
AS
IF (@Size IS NULL)
RETURN NULL
RETURN -@Size

-- p75
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
-- 4

--p76
DECLARE @i INT
EXEC @i = PassCharsTSql null
PRINT @i
GO
-- 0
-- Notice that PassCharsTSql had a return code of 0. If a null
-- to the PassChars implementation shown earlier, the implementation
-- would throw an exception and raise a T-SQL error, as follows.

DECLARE @i INT
EXEC @i = PassCharsTSql null
-- .Net SqlClient Data Provider: Msg 6522, Level 16, State 1,
-- PassChars, Line 0
-- A CLR error occurred during execution of 'PassChars'...

-- p78
CREATE PROC PassChars(@str char(100)) as EXTERNAL NAME
procedures.[Procedures.Parameters].PassChar

-- p79
CREATE PROCEDURE PassBack(@valueIn INT, @valueOut INT OUTPUT) as
EXTERNAL NAME procedures.[Procedures.Parameters].PassBack

-- Once this procedure has been added to the database, it can be used as is
-- shown in the following SQL batch.
DECLARE @i INT
SET @i = 4
EXEC PassBack 3, @i OUTPUT
PRINT @i
GO
7

DECLARE @i INT
SET @i = 4
EXEC PassBack @valueRef = @i OUTPUT, @valueIn = 3
PRINT @i
GO
-- 7

CREATE PROC PassBack3(@valueIn INT = 5, @valueOut INT OUTPUT) as
EXTERNAL NAME procedures.[Procedures.Parameters].PassBack
CREATE PROC PassBack4(@valueIn INT = 7, @valueOut INT OUTPUT) as
EXTERNAL NAME procedures.[Procedures.Parameters].PassBack

-- The following SQL script executes the PassBack3 procedure, using a
-- default parameter.
DECLARE @i int
SET @i = 4
EXEC PassBack3 DEFAULT, @i OUTPUT
PRINT @i
GO
-- 9


-- p81
CREATE FUNCTION RepeatSql (@repeat INT, @str NCHAR(1))
RETURNS NCHAR(30)
AS
BEGIN
return Replicate(@str, @repeat)
END
-- Repeat takes as input parameters an int and a character, and returns a
-- string with the character repeated. The following SQL batch uses it.
print dbo.RepeatSql(5, 'A')
go
-- AAAAA

-- p81
CREATE FUNCTION Repeat(@i INT, @c NCHAR(1)) RETURNS NCHAR(100)

-- p82
CREATE TABLE R1
(
Id CHAR(1),
Other AS dbo.Repeat(3, Id)
)
INSERT INTO R1 VALUES ('A')
SELECT * FROM R1
GO
-- A AAA
EXTERNAL NAME functions.[Chapter3.Functions].Repeat