Sql Stored Procedure INSERT INTO help!

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)

Sql Stored Procedure INSERT INTO help!

Author

Topic

supeg
Starting Member

6 Posts

Posted - 2004-11-22 : 23:22:15

Hello.

Having a little problem with a stored procedure.

Trying to do one that just adds a record with the given parameters.

Table is tblTimeCards with 3 Fields.

I tried it first this way.

CREATE PROCEDURE A8_INSERTTIMECARD

INSERT INTO tblTimeCards
(TimeCardID,TimeCardDate, EmployeeNo)
VALUES
(@TimeCardID,@TimeCardDate,@EmployeeNo);

Getting this error

Server: Msg 156, Level 15, State 1, Procedure A8_INSERTTIMECARD, Line 4
Incorrect syntax near the keyword 'INSERT'.
Server: Msg 137, Level 15, State 1, Procedure A8_INSERTTIMECARD, Line 7
Must declare the variable '@TimeCardID'.

So then delared the variables:

CREATE PROCEDURE A8_INSERTTIMECARD
@TimeCardID nvarchar(50),
@TimeCardDate datetime,
@EmployeeNo nvarchar(50)

INSERT INTO tblTimeCards
(TimeCardID,TimeCardDate, EmployeeNo)
VALUES
(@TimeCardID,@TimeCardDate,@EmployeeNo);

And get this error...

Server: Msg 156, Level 15, State 1, Procedure A8_INSERTTIMECARD, Line 6
Incorrect syntax near the keyword 'INSERT'.

I know it is something simple but it is driving me nuts.

Please help!

Thanks!

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-11-22 : 23:26:53

CREATE PROCEDURE A8_INSERTTIMECARD
@TimeCardID nvarchar(50),
@TimeCardDate datetime,
@EmployeeNo nvarchar(50)
as
begin
INSERT INTO tblTimeCards
(TimeCardID,TimeCardDate, EmployeeNo)
VALUES
(@TimeCardID,@TimeCardDate,@EmployeeNo)
end

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com
http://www.sqljunkies.com/weblog/sqldude

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-22 : 23:31:22

It's a simple syntax error
CREATE PROCEDURE
<parameter list declaration>
AS
<code>

rockmoose

supeg
Starting Member

6 Posts

Posted - 2004-11-22 : 23:32:09

Arggg Begin/End.

Thank you very much!

I did a lot of searching to no avail on the net.

Thanks again.

supeg
Starting Member

6 Posts

Posted - 2004-11-22 : 23:41:08

Doh it was the AS

Did not even notice I forgot it.

Im dumb.

Thanks guys!

supeg
Starting Member

6 Posts

Posted - 2004-11-22 : 23:43:15

Actually A question. Say I wanted to make this procedure an UPDATE query so when I put in the TimeCard ID it checks to see if one matches, if not it runs this query CREATE PROCEDURE A8_INSERTTIMECARD that adds a new one?

if Statement?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-23 : 00:04:25

Hi,

IMO it is better to keep separate procedures for INSERT / UPDATE,
the client should know which one to call.

Otherwise write something like this:
CREATE PROCEDURE xxx
<parameter list declaration>
AS
IF EXISTS(...) -- check if TimeCardID matches current data
BEGIN ..UPDATE.. END -- update current data
ELSE
BEGIN ..INSERT.. END -- insert new record

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources