setlocal

call "C:\Program Files\Microsoft Visual Studio .NET Whidbey\Common7\Tools\vsvars32.bat"
rem compile the assemnbly
csc /target:library /out:bin\SqlProcedures.dll SqlProcedures.cs
rem drop the funcions, if they are there
sqlcmd -d Chapter3 -Q "drop procedure PassChars"
sqlcmd -d Chapter3 -Q "drop procedure ReturnNull"
sqlcmd -d Chapter3 -Q "drop procedure PassBack"
sqlcmd -d Chapter3 -Q "drop procedure PassBack2"
sqlcmd -d Chapter3 -Q "drop procedure PassBack3"
sqlcmd -d Chapter3 -Q "drop procedure NPassChars"
rem drop the assembly
sqlcmd -d Chapter3 -Q "drop assembly SqlProcedures"
rem add the assembly as SAFE, first cmd line param is directory of dll
sqlcmd -d Chapter3 -Q "CREATE ASSEMBLY SqlProcedures FROM '%~1\SqlProcedures.dll' with PERMISSION_SET = SAFE"
rem add each of the functions

rem this will fail because char is not allowed as a parameter of a
rem CLR stored procedure
sqlcmd -d Chapter3 -Q "CREATE Procedure PassChars(@c char(100)) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::PassChars"

rem this will work because nchar is supported as a parameter for a
rem CLR procedure
sqlcmd -d Chapter3 -Q "CREATE Procedure PassChars(@c nchar(100)) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::PassChars"


sqlcmd -d Chapter3 -Q "CREATE Procedure ReturnNull as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::ReturnNull"

sqlcmd -d Chapter3 -Q "CREATE Procedure PassBack(@in int, @out int OUTPUT) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::PassBack"

sqlcmd -d Chapter3 -Q "CREATE Procedure PassBack2(@in int, @out int OUTPUT) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::PassBack2"

sqlcmd -d Chapter3 -Q "CREATE Procedure PassBack2(@in int, @out int OUTPUT) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::PassBack2"

sqlcmd -d Chapter3 -Q "CREATE Procedure PassBack3(@in int = 5, @out int OUTPUT) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters]::PassBack2"

rem try to use nest class
sqlcmd -d Chapter3 -Q "CREATE Procedure NPassChars(@c char(100)) as EXTERNAL NAME SqlProcedures:[Procedures.Parameters.Nested]::NPassChars"

rem test the functions just added
sqlcmd -d Chapter3 -Q "declare @i int; exec @i = PassChars 'qwqwqw'; print @i"

sqlcmd -d Chapter3 -Q "declare @i int; exec @i = ReturnNull; print @i"

sqlcmd -d Chapter3 -Q "declare @i int; set @i = 3; declare @r int; exec @r = PassBack 4, @i OUTPUT; print @i"

sqlcmd -d Chapter3 -Q "declare @i int; set @i = 3; declare @r int; exec @r = PassBack 4, @i OUTPUT; print @i"


rem try passing in a null
sqlcmd -d Chapter3 -Q "declare @i int; set @i = 3; declare @r int; exec @r = PassBack2 null, @i OUTPUT; print @i"

rem try passing by name
sqlcmd -d Chapter3 -Q "declare @i int; set @i = 3; declare @r int; exec @r = PassBack @in = 4, @out = @i OUTPUT; print @i"


rem try default
sqlcmd -d Chapter3 -Q "declare @i int; set @i = 3; declare @r int; exec @r = PassBack3 DEFAULT, @i OUTPUT; print @i"


endlocal


    



