/*--------------------------------------------------------------------
  This file loads the assemblies and the stored procedures for the CLR
  chapter in the article "How to Share Data Between Stored Procedures."
  These days, all assemblies must be trusted on server level if they 
  are SAFE.
  --------------------------------------------------------------------*/
USE master
go
-- The key needs a password, but we don't need to know the password, so
-- we generate one for the occasion and forget it.
DECLARE @sql      nvarchar(MAX),
        @password char(40)
SELECT @password = convert(char(36), newid()) + 'Ab4?'

-- Drop any existing login and key.
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'ShareDataDemo$asymkey') 
   DROP LOGIN ShareDataDemo$asymkey
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'ShareDataDemo')
   DROP ASYMMETRIC KEY ShareDataDemo

-- Generate the SQL.
SELECT @sql = 'CREATE ASYMMETRIC KEY ShareDataDemo FROM FILE = ' +
              '''$(CD)\keypair.snk'' ' +
              'ENCRYPTION BY PASSWORD = ''' + @password + ''''

-- Execute to create the key.
PRINT @sql
EXEC(@sql)

-- Create the login to carry the permission.
CREATE LOGIN ShareDataDemo$asymkey FROM ASYMMETRIC KEY ShareDataDemo
GRANT EXTERNAL ACCESS ASSEMBLY TO ShareDataDemo$asymkey
GRANT UNSAFE ASSEMBLY TO ShareDataDemo$asymkey
go

-- Now we can go back to our regular database and create the assemblies.
USE $(SQLCMDDBNAME)
go
-- Drop any existing procedures and assemblies.
IF object_id('ExecAndInsertCLR') IS NOT NULL
   DROP PROCEDURE ExecAndInsertCLR
IF object_id('ExecAndInsert') IS NOT NULL
   DROP PROCEDURE ExecAndInsert
IF object_id('helpdb') IS NOT NULL
   DROP PROCEDURE helpdb
go
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'helpdb')
   DROP ASSEMBLY helpdb
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'ExecAndInsert')
   DROP ASSEMBLY ExecAndInsert
go
-- Create assemblies.
CREATE ASSEMBLY helpdb FROM '$(CD)\helpdb.dll'
WITH PERMISSION_SET = SAFE
CREATE ASSEMBLY ExecAndInsert FROM '$(CD)\ExecAndInsert.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
go
-- And create the stored procedures.
CREATE PROCEDURE helpdb @db sysname AS
  EXTERNAL NAME helpdb.StoredProcedures.helpdb
go
CREATE PROCEDURE ExecAndInsertCLR @server     sysname,
                                  @dbname     sysname,
                                  @schema     sysname,
                                  @table  sysname,
                                  @sqlcmdtext nvarchar(MAX) AS
   EXTERNAL NAME ExecAndInsert.ExecAndInsert.CLREntry
go
CREATE PROCEDURE ExecAndInsert @schema     sysname,
                               @table  sysname,
                               @sqlcmdtext nvarchar(MAX) AS
  DECLARE @server sysname = @@servername,
          @dbname sysname = db_name()
  EXEC ExecAndInsertCLR @server, @dbname, @schema, @table, @sqlcmdtext
go