Microsoft’s Visual Studio 2008 is amazing. It truly is an integrated development environment, because EVERYTHING is integrated. I just learnt about stored procedures today and how creating stored procedures is so easy in Visual Studio. Here’s the steps that you need.
- In Visual Studio, create a solution if you haven’t, and to that solution, add a project of the type “SQL Server Project”
- Next, right click on that project (not the solution) and add an item of the type “Stored Procedure”, and choose “Stored Procedure” as the sub-type that we want.
- You should see the stored procedure added to your project as a file. I’m using C#, so it’s added as a new C# file, with some template code added already.
- Try adding this code chunk:
SqlPipe p = SqlContext.Pipe;
p.Send(“Hello!”); - Now we can test out our new stored procedure by right-clicking on the project and selecting “Deploy”. This will create a DLL file and copy it over the SQL Server.
- Open up SQL Server Management Studio and click on the button titled “Create a New Query’. You should see a screen pop up on the right.
- Enter this code:
USE [YourDatabaseName]
GO
EXEC [dbo].[WhatYouNamedYourStoredProcedure]
GO - Now right-click in that code space and select “Execute”. If you experience this error: “Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option”, then execute the following query first:
sp_configure ‘clr enabled’, 1
go
reconfigure
go - You should see the text “Hello!” appearing in your output window 🙂
- To do something that’s actually useful, we can add in the following code to our stored procedure, which essentially selects all records in our database and outputs them:
SqlConnection conn = new SqlConnection(“Context Connection=true”);
SqlCommand cmd = new SqlCommand(@”SELECT * FROM YourTable”, conn);conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);rdr.Close();
conn.Close(); - Done! You have just created and run your first stored procedure. Wasn’t that easy?