Stored Procedure question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure question

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-14 : 12:12:04

I am fairly new to SQL. I have a stored procedure that is confusing me.
CREATE PROCEDURE InsertOne @fld1 varchar(50), @fld2 varchar(50), @fld3 varchar(50), @fld4 varchar(50), @fld5 varchar(50) AS
DECLARE @gtime SMALLDATETIME SELECT @gtime = [Gtime] FROM [SelectB] WHERE [IDNum] = @fld1 INSERT INTO [TestFour]([fld1],[fld2],[fld3],[fld4],[fld5],[fld6]) VALUES (@fld1, @fld2, @fld3, @fld4, @fld5, @gtime)
GO
Now the part from "DECLARE.... up to but not including GO" works just fine when in a querystring in my VB.net coding. In my VB.net code I simply have queryString ="InsertOne". My runtime error is "Incorrect syntax near 'InsertOne'. I have tried adding parentheses Ex: "(@fld1 varchar(50), etc"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 12:30:34

Where are you trying to create the stored procedure - inside Query Analyzer?

Here is a sample stored procedure:

CREATE PROCEDURE usp_SampleStoredProc
(@var1 varchar(50), @var2 int)
AS

DECLARE @var3 int

SELECT @var3 = COUNT(*)
FROM Table1

SELECT @var1 = Column1, @var2 = Column2
FROM Table2
WHERE Column1 BETWEEN 0 AND 100

RETURN

GO

Tara

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-14 : 12:37:14

Thanks for replying. I am using Web Matrix Tool. The control is Edit Stored Procedure. Can you tell me if the code I displayed looks like it should work? Using the part from "Declare.... up to but not including GO" in my queryString(in my vb.net) works just fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 12:41:26

The code looks for to me. I put it in Query Analyer and hit the check syntax button and it didn't find any problems, so the problem that you are having is probably associated with the Web Matrix Tool. How is it connecting to SQL Server (DSN, etc.)? Are you able to use Query Analyzer instead since that is the tool that you should be using anyway for this type of thing?

Tara

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-14 : 12:47:57

Tara-
I guess it's time for me to download Enterprise Manager. When I started with this project, it was not available. I have become accustomed to doing everything with this Web Matrix guy. Maybe that is my problem with this particular hurdle.
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 12:57:47

Well Enterprise Manager is not the tool to use to create stored procedure. You will want to use Query Analyzer. I seldom use Enterprise Manager except for some administrative tasks. I will use Enterprise Manager to quickly look at a particular stored procedure, but if I am going to be modifying it or creating a new one, I always use Query Analyzer since it is a more powerful tool.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources