July 24

How To Create a Stored Procedure using C#, SQL Server 2008, Visual Studio 2008


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;

  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]
    EXEC [dbo].[WhatYouNamedYourStoredProcedure]

  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

  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);


    SqlDataReader rdr = cmd.ExecuteReader();


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

Other posts about scaling up businesses

How To Choose Digital Marketing Channels (A Powerful 6 Step Process)

Leave a Reply

Your email address will not be published. Required fields are marked

  1. thanks a lot!
    as i am new to these concepts i really appreciate step by step approach.I request you to kindly provide some more examples.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
free playbook

Get Your Free 5E Playbook

My 5E playbook is now available for a limited time!

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
Get Your Playbook