--- title: "Hello World Sample | Microsoft Docs" ms.custom: "" ms.date: "03/06/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: "database-engine" ms.topic: "reference" ms.assetid: fed6c358-f5ee-4d4c-9ad6-089778383ba7 author: mashamsft ms.author: mathoma manager: craigg --- # Hello World Sample The Hello World sample demonstrates the basic operations that are involved in creating, deploying, and testing a simple common language runtime (CLR) integration-based stored procedure. This sample also demonstrates how to return data through a record, which is dynamically constructed by the stored procedure and returned to the caller. The `HelloWorld` stored procedure returns the string "Hello world!" in a result set consisting of one row. This example illustrates some uses for the classes [Microsoft.SqlServer.Server.SqlMetaData](https://go.microsoft.com/fwlink/?LinkID=193572), [Microsoft.SqlServer.Server.SqlDataRecord](https://go.microsoft.com/fwlink/?LinkID=193573) and [Microsoft.SqlServer.Server.Pipe](https://go.microsoft.com/fwlink/?LinkID=193571). ## Prerequisites To create and run this project the following the following software must be installed: - [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express. You can obtain [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express free of charge from the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express Documentation and Samples [Web site](https://www.microsoft.com/sql-server/sql-server-editions-express) - The AdventureWorks database that is available at the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Developer [Web site](https://go.microsoft.com/fwlink/?linkid=62796) - .NET Framework SDK 2.0 or later or Microsoft Visual Studio 2005 or later. You can obtain .NET Framework SDK free of charge. - In addition, the following conditions must be met: - The [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using must have CLR integration enabled. - In order to enable CLR integration, perform the following steps: #### Enabling CLR Integration - Execute the following [!INCLUDE[tsql](../../includes/tsql-md.md)] commands: `sp_configure 'clr enabled', 1` `GO` `RECONFIGURE` `GO` > [!NOTE] > To enable CLR, you must have `ALTER SETTINGS` server level permission, which is implicitly held by members of the `sysadmin` and `serveradmin` fixed server roles. - The AdventureWorks database must be installed on the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using. - If you are not an administrator for the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using, you must have an administrator grant you **CreateAssembly** permission to complete the installation. ## Building the Sample #### Create and run the sample by using the following instructions: 1. Open a Visual Studio or .NET Framework command prompt. 2. If necessary, create a directory for your sample. For this example, we will use C:\MySample. 3. In c:\MySample, create `HelloWorld.vb` (for the Visual Basic sample) or `HelloWorld.cs` (for the C# sample) and copy the appropriate Visual Basic or C# sample code (below) into the file. 4. Compile the sample code from the command line prompt by executing one of the following, depending on your choice of language. - `vbc C:HelloWorld.vb /target:library` - `csc /target:library HelloWorld.cs` 5. Copy the [!INCLUDE[tsql](../../includes/tsql-md.md)] installation code into a file and save it as `Install.sql` in the sample directory. 6. Deploy the assembly and stored procedure by executing - `sqlcmd -E -I -i install.sql -v root = "C:\MySample\"` 7. Copy [!INCLUDE[tsql](../../includes/tsql-md.md)] test command script into a file and save it as `test.sql` in the sample directory. 8. Execute the test script with the following command - `sqlcmd -E -I -i test.sql` 9. Copy the [!INCLUDE[tsql](../../includes/tsql-md.md)] cleanup script into a file and save it as `cleanup.sql` in the sample directory. 10. Execute the script with the following command - `sqlcmd -E -I -i cleanup.sql` ## Sample Code The following are the code listings for this sample. C# ``` using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld() { Microsoft.SqlServer.Server.SqlMetaData columnInfo = new Microsoft.SqlServer.Server.SqlMetaData("Column1", SqlDbType.NVarChar, 12); SqlDataRecord greetingRecord = new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo }); greetingRecord.SetString(0, "Hello world!"); SqlContext.Pipe.Send(greetingRecord); } }; ``` Visual Basic ``` Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public NotInheritable Class StoredProcedures _ Public Shared Sub HelloWorld() Dim columnInfo As New Microsoft.SqlServer.Server.SqlMetaData("Column1", _ SqlDbType.NVarChar, 12) Dim greetingRecord As New SqlDataRecord(New _ Microsoft.SqlServer.Server.SqlMetaData() {columnInfo}) greetingRecord.SetString(0, "Hello World!") SqlContext.Pipe.Send(greetingRecord) End Sub End Class ``` This is the [!INCLUDE[tsql](../../includes/tsql-md.md)] installation script (`Install.sql`), which deploys the assembly and creates the stored procedure in the database. ``` USE AdventureWorks GO IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorld') DROP PROCEDURE usp_HelloWorld; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorld') DROP ASSEMBLY HelloWorld; GO DECLARE @SamplesPath nvarchar(1024) set @SamplesPath = '$(root)' CREATE ASSEMBLY HelloWorld FROM @SamplesPath + 'HelloWorld.dll' WITH permission_set = Safe; GO CREATE PROCEDURE usp_HelloWorld --( -- @Greeting nvarchar(12) OUTPUT --) AS EXTERNAL NAME HelloWorld.[StoredProcedures].HelloWorld; GO ``` This is `test.sql`, which tests the sample by executing the stored procedure. ``` use AdventureWorks go execute usp_HelloWorld USE AdventureWorks; GO IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorld') DROP PROCEDURE usp_HelloWorld; GO ``` The following [!INCLUDE[tsql](../../includes/tsql-md.md)] removes the assembly and stored procedure from the database. ``` USE AdventureWorks GO IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorld') DROP PROCEDURE usp_HelloWorld; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorld') DROP ASSEMBLY HelloWorld; GO ``` ## See Also [Usage Scenarios and Examples for Common Language Runtime (CLR) Integration](../../../2014/database-engine/dev-guide/usage-scenarios-and-examples-for-common-language-runtime-clr-integration.md)