Are Stored Procedures still used in SQL SERVER 2005? 2

[RotorTorque]

Hi all,
I have done most of my programming VB6 and VB.NET with SQL 2000 Databases. I am now working for a company that uses SQL Server 2005.
I am used to writing Stored Procedures and querying those Stored Procedures from my applications. I can't seem to find a way to add Stored Procedures in SQL SERVER 2005. Have they been replaced? Is there now as better way to query the DB from within a VB.Net application?

Thanks in advance,
RotorTorque

 

[jhaith]

Try this link.

http://msdn2.microsoft.com/en-us/library/ms345393.aspx

-jhaith

 

[nickdel]

Rotor, yes SP's are still available within 2005. Infact, if you use Visual Studio you can now step into your stored procedures, one statement at a time, which is rather funky!

 

[ESquared]

can't seem to find a way to add Stored Procedures in SQL SERVER 2005.

Are you kidding? You do it the same way you did before:

CREATE PROCEDURE MyProc
AS
SELECT 'you still create procedures the same way as before.'
GO

 

[RotorTorque]

Thanks jhaith and nickdel. I took a look at the link that jhaith provided and it seems that this is exactly what i needed.
Nickdel I can't wait use the functionality of steping into stored procedures, this sounds cool.

Thank guys once again.
RotorTorque

 

[Crowley16]

nickdel,

you can?

can you tell me how?

bearing in mind I've only been using visual studio for like a few weeks...

--------------------
Procrastinate Now!

 

[ca8msm]

can you tell me how?

Go to Server Explorer, setup a connection to your database. Expand the Stored Procedures list, right click and click Step Into Stored Procedure.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

Keep in mind that you need Professional edition or higher of Visual Studio to debug procs

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

You could step into your stored procedures in SQL 2000, too, but it was devilishly difficult to get going correctly.

 

[RotorTorque]

Hi all,
I added the following stored procedure to my SQL SERVER table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	Retrieves Search Results
-- =============================================
CREATE PROCEDURE KnowledgeBaseSearch 
	-- Add the parameters for the stored procedure here
	@Description nvarchar(100) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM Cases WHERE Description LIKE @Description ORDER BY OpenedDate DESC
END
GO

in my VB.NET 2005 application i am trying to call the stored procedure using the following code,:

Dim strTest As String = "%Test%"
cmd.CommandText = "KnowledgeBaseSearch('" & strTest & "')"
_rdr = cmd.ExecuteReader

BUT I get the following error when I run the application.

Error Reading SQL
Incorrect Syntax near 'Test'

Can anyone please tell me what I am doing wrong.

thanks in advance,
RotorTorque ;-)

 

[gmmastros]

Try removing the parenthesis.

[tt]cmd.CommandText = "KnowledgeBaseSearch '" & strTest & "'"[/tt]




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

Or use the correct command type for a stored procedure in your VB.NET code and add the parameters via the Parameters.Add method.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ESquared]

and if you're not setting the command type you ought to use "EXEC" too

 

[RotorTorque]

Thanks gmmastros. Removing the paranthesis worked.


ca8msm, what's the correct command type for a stored procedure in VB.NET code?

RotorTorque

 

[ca8msm]

Strangely enough, it's StoredProcedure.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bigfoot]

You could step into your stored procedures in SQL 2000, too, but it was devilishly difficult to get going correctly.

I never knew this! Can you point me in the right direction please?
I'm stuck with SQL 2000 in my company.

 

[RotorTorque]

Hi all,
I used the following code for filling a Dataset with a Stored Procedure

'Declare and Set the connection property
Dim SQLConn As New SqlClient.SqlConnection
SQLConn = New SqlClient.SqlConnection
SQLConn.ConnectionString = My.Settings.HelpDeskConnection 
SQLConn.Open()

'Declare a SQL DataAdapter object
Dim objDataAdapter As New SqlDataAdapter()

'Assign a new SQLCommand to the SelectCommand property
objDataAdapter.SelectCommand = New SqlCommand

'Set the SelectCommand properties
objDataAdapter.SelectCommand.Connection = SQLConn
Dim strLogin As String = Me.BusCase.RequesterID
objDataAdapter.SelectCommand.CommandText = "FormLoad '" & strLogin & "'"
'objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure  
'automatically create update/delete/insert commands
Dim objCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)
Dim objDataSet As DataSet = New DataSet()
'Fill the DataSet object with data
objDataAdapter.Fill(objDataSet, "Cases")

I get the following error:
Stored Procedure "FormLoad" does not exist.

But if I comment out the following syntax it works fine. Why is that?

'objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

Thanks in advance,
RotorTorque

 

[AlexCuse]

If you set the CommandType to StoredProcedure, you need to use the myCommand.Parameters.AddWithValue("@MyParm","My Parm's Value") syntax (Think I got it right) to add your parameters rather than String concatenation.

HOpe this helps,

Alex

[small]----signature below----[/small]
You can't fit a square data in a round table

My Crummy Web Page

 

[TysonLPrice]

I never knew this! Can you point me in the right direction please? I'm stuck with SQL 2000 in my company."

There is some set ups required but if your DBAs have it installed:

1. Pull up Query Analyzer object viewer.
2. Drill down to the stored procedure you want to debug.
3. I think it's right click on the SP.
4. Click on Debug.
5. Fill in the parameters and click execute.
6. Hover over the icons to see what they do.

I can't check the steps since we are on 2005 now. There are tutorials if you Google for them.

 

[chrissie1]

myCommand.Parameters.AddWithValue("@MyParm","My Parm's Value")

Wow. ;-)

@RotorTorque

Commandbuilder won't work for these kinds of things.



Christiaan Baes
Belgium

My Blog