How To Create Stored Procedure In Vb Net

1. Oct 5th, 2007,11:00 AM #1

   

   Thread Starter

   Junior Member

   ---

   How to create a SQL 2005 stored procedure in vb.net?

   One of the big changes in SQL Server 2005 is that stored procedures no longer are limited to T-SQL, but can be developed in vb.net et. al.

   I'm a complete newbie in the vb.net world. I've got some procedures that I want to port over from VBA, make them work under vb.net and then use them as stored procedures in my SQL Server 2005 database. How exactly does one go about writing an SP in vb.net? Can someone point me at some sample code that I could use as a template?

   Thanks in advance!

   ---

2. Oct 5th, 2007,02:49 PM #2

   Re: How to create a SQL 2005 stored procedure in vb.net?

   Code:

   Dim cmd as New System.Data.SqlClient.SqlCommand() Dim conn as New System.Data.SqlClient.SqlConnection(connectionString) cmd.Connection = conn cmd.CommandText = "CREATE PROCEDURE dbo.<NameOfProcedure>" & _                               "@Param1 int" & _                               "AS" & _                               "BEGIN" & _                               "   <SQLHERE>" & _                               "END" conn.Open() cmd.ExecuteNonQuery() cmd.Close()

   You may want to add some Try/Catch logic here. You might also want to do a DROP PROCEDURE <StoredProceName> before creating it, too. Or just ALTER it instead.

   If your problem is solved, please use the Mark Thread As Resolved under Thread Tools!

   Show Appreciation. Rate Posts !

   ---

3. Oct 8th, 2007,11:05 AM #3

   

   Thread Starter

   Junior Member

   ---

   Re: How to create a SQL 2005 stored procedure in vb.net?

   What I was referring to was the fact that SPs can now be written in vb.net, not in T-SQL. However, the "how-to" part of that has proven very difficult to find. That's what I'm looking for.

   ---

4. Oct 8th, 2007,11:35 AM #4

   Re: How to create a SQL 2005 stored procedure in vb.net?

   Cloth - what you are looking for is "SQL CLR" ... try searching MSDN for that.... should help... also if you can, grab a copy of "Programming Microsoft SQL Server 2005" by Andrew Brust and Stephen Forte.... they've got a section devoted to SQL CLR.

   It's not as great as it sounds.... basically what happens is you write .NET code (VB.NET or C#)... compile it into a DLL, then load the DLL into SQL Server with special commands.... then you can write a stored proc that utilizes that DLL and the exposed methods.... from what I've read, it's not as much fun as one would think, debugging is a pain, and it's hardly worth the effort.

   -tg

   ---

5. Oct 8th, 2007,04:37 PM #5

   

   Thread Starter

   Junior Member

   ---

   Re: How to create a SQL 2005 stored procedure in vb.net?

   Thanks for the clarification. Unfortunately, what I need to do, I can't do with T-SQL, so it looks like I'll be heading down that path.

   ---

6. Oct 8th, 2007,04:48 PM #6

   Re: How to create a SQL 2005 stored procedure in vb.net?

   Are you sure it can't be done with T-SQL?
   And even if not, why put it in the database?

   -tg

   ---

How To Create Stored Procedure In Vb Net

Source: https://www.vbforums.com/showthread.php?491373-How-to-create-a-SQL-2005-stored-procedure-in-vb-net

Posted by: hangersaisuatecous1950.blogspot.com

Share this post