Alvin Poh’s Blog

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.

1. In Visual Studio, create a solution if you haven’t, and to that solution, add a project of the type “SQL Server Project”
2. 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.
3. 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.
4. Try adding this code chunk:
   

   SqlPipe p = SqlContext.Pipe;
   p.Send(“Hello!”);

5. 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.
6. 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.
7. Enter this code:
   

   USE [YourDatabaseName]
   GO
   EXEC [dbo].[WhatYouNamedYourStoredProcedure]
   GO

8. 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

9. You should see the text “Hello!” appearing in your output window
10. 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();

11. Done! You have just created and run your first stored procedure. Wasn’t that easy?

Similar Posts:

• How To Edit .htaccess So That SSI Include Code Will Run
• How To Fix The Problem of Suddenly Not Being Able To Send Messages With Your Sony Ericsson Phone
• Send Free SMS to AT&T Users
• How To Resolve 500 Internal Server Error
• How To Make and Run Batch Files In Terminal In Mac OSX

Be Sociable, Share!

•