need second set of eyes - 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)

need second set of eyes

Author

Topic

sunbeam
Starting Member

8 Posts

Posted - 2004-08-14 : 11:24:19

i have a simple stor proc that does couple insert, the 1st insert has no errors but the second one is really torturing me with " Incorrect syntax near keyword convert" error

been messing for this for more than 8 hours, appreciate any help
below is the code
-----------------

ALTER procedure dbo.InsertRecordsProc
@prj_name varchar(100), -- for tblCore
@up_by varchar(100), -- for tblCore
@intStatus int,
@intBudget int,
@intScope int,
@intQuality int,
@intValue int,
@intActivity int,
@sSubmitDate datetime,
@sEstDate datetime,
@sCommitDate datetime,
@sActualDate datetime

AS
begin
-- declare variables for storing current id and updated date
declare @current_id int
declare @Latest_Updated_Date datetime

-- get latest id
select @current_id = (select current_id from dbo.tblSequence)

-- insert values into tblCore
INSERT INTO [ADC_PMO].[dbo].[tblCore]
([prj_id], [Project_Name],[Updated_Last_by],[Updated_Date])
VALUES(@current_id, @prj_name, @up_by, getdate())

-- retrieve updated date from above insert
SELECT @Latest_Updated_Date = (select c.Updated_Date from tblCore c
where
(c.Prj_ID = @current_id) AND
(c.Updated_Date = (select max(c1.updated_date) from tblCore c1
where c1.Prj_ID = c.Prj_ID)))

-- insert values into tblProj
INSERT INTO
dbo.tblProject([prj_id],[status_id],[budget_id],[scope_id],[quality_id],[value_id],[activity_id],[Submission_Date],[Est_complete_Date],[commit_complete_date],[Actual_Complete_Date],[Updated_Date])
VALUES
(@current_id,
@intStatus,
@intBudget,
@intScope,
@intQuality,
@intValue,
@intActivity,
@sSubmitDate,
@sEstDate,
@sCommitDate,
@sActualDate,
@Latest_Updated_Date)

end
GO

dbo.InsertRecordsProc 'xzzcxxc', 'susan', NULL, 0, 0, 0, 0, 1,
convert(datetime,'14 Aug 2004',106), convert(datetime,NULL,106),
convert(datetime,NULL,106), convert(datetime,NULL,106)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-14 : 11:45:31

[code]
DECLARE
@sSubmitDate datetime,
@sEstDate datetime,
@sCommitDate datetime,
@sActualDate datetime

SELECT
@sSubmitDate = convert(datetime,'14 Aug 2004',106),
@sEstDate = convert(datetime,NULL,106),
@sCommitDate = convert(datetime,NULL,106),
@sActualDate = convert(datetime,NULL,106)

SELECT @sSubmitDAte, @sEstDate, @sCommitDate, @sActualDate

EXEC dbo.InsertRecordsProc
'xzzcxxc',
'susan',
NULL,
0,
0,
0,
0,
1,
@sSubmitDAte,
@sEstDate,
@sCommitDate,
@sActualDate
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sunbeam
Starting Member

8 Posts

Posted - 2004-08-14 : 11:53:20

apologize for the cross post.

i am slightly confused.the following code is generated in my asp page and calls the stor procedure , eg., convert(datetime,'14 Aug 2004',106), is generated by certain functions in the asp page

dbo.InsertRecordsProc 'xzzcxxc', 'susan', NULL, 0, 0, 0, 0, 1,
convert(datetime,'14 Aug 2004',106), convert(datetime,NULL,106),
convert(datetime,NULL,106), convert(datetime,NULL,106)

appreciate if you could clarify - thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-14 : 12:04:30

I can't help you correct the problem you have in your ASP page. I'm sure there are people on here who can. I can tell you it won't work calling the proc that way though. Here's an example:



CREATE PROCEDURE show_dates
@date1 DATETIME,
@date2 DATETIME
AS

SELECT @date1, @date2
GO

DECLARE 
	@date1 DATETIME,
	@date2 DATETIME

SELECT 
	@date1 = convert(datetime,'14 Aug 2004',106),
	@date2 = convert(datetime,NULL,106)

EXEC show_dates @date1, @date2
GO
EXEC show_dates convert(datetime,'14 Aug 2004',106), convert(datetime,NULL,106)
GO
DROP PROCEDURE show_dates
GO

This produces the following output:


                                                                                                              
------------------------------------------------------ ------------------------------------------------------ 
2004-08-14 00:00:00.000                                NULL

(1 row(s) affected)

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'convert'.

Notice that the first return worked. The second did not. You need to fix your ASP code so it calls the procedure properly.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sunbeam
Starting Member

8 Posts

Posted - 2004-08-14 : 12:15:26

well thanks for your insight, i fixed my asp code, seems to work

appears that you know your sql/stor proc, i am relatively new to programming sql / stor proc's, any suggestions about resources- where i can sharpen these skills in addition to everyday work

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-14 : 14:51:24

http://www.sqlteam.com/store.asp

Anything written by Ken Henderson is incredible. Also, believe it or not, the "Teach Yourself Transact-SQL in 21 Days" by Sam's Publishing is a great book. I would buy those two to get you started. Also, browse these forums:

www.sqlteam.com
www.sqlservercentral.com
www.dbforums.com
www.sql-server-performance.com
www.sqljunkies.com

Real-life examples are many times better than the books. These forums are a great way to learn SQL and a lot of other things.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources