Issue with stored procedure

Question

I have a sybase stored procedure which is having an issue.

Here If I use the insert statement directly the insert works perfectly as shown:

insert 
  into dbo.StudentData
     ( studID
     , studLetters
     , studCode 
     , studTelecast 
     , studName
     , monthCode
     , reportDate
     , startTime
     , endTime
     , startDateTime
     , endDateTime
     , cost
     , duration
     , creationTime
     )
values
     ( 113
     , 'ABCD'
     , 222
     , 333
     , 'One'
     , 02
     , getDate()
     , getdate()
     , getdate()
     , getdate()
     , getdate()
     , 999
     , 11
     , getdate()
     )
     ;

But if I use the stored proc I'm getting the following error :

[EXEC - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42000]  Incorrect syntax near ')'.

exec dbo.sp_loadStudData(113, 'ABCD', 222, 333, 'One', 02, getDate(), getdate(), getdate(), getdate(), getdate(), 999, 11, getdate() )

I am not able to find out what the issue is but the stored proc got created successfully without any errors. The stored Proc is as foll:

create proc dbo.sp_loadStudData
(
@studID int,                   
@studLetters varchar(255),
@studCode int,
@studTelecast int,
@studName varchar(25),
@monthCode int,
@reportDate datetime,
@startTime datetime,
@endTime datetime,
@startDateTime datetime,
@endDateTime datetime,
@cost int,
@duration int,
@creationTime datetime
)
as
begin
set nocount on


insert into dbo.StudentData(studID,studLetters,studCode ,studTelecast ,studName ,monthCode,reportDate,startTime,endTime,startDateTime,endDateTime,cost,duration,creationTime)
values(@studID,@studLetters,@studCode ,@studTelecast ,@studName ,@monthCode,@reportDate,@startTime,@endTime,@startDateTime,@endDateTime,@cost,@duration,@creationTime)

end
go
EXEC sp_procxmode 'dbo.sp_loadStudData', 'unchained'
go
IF OBJECT_ID('dbo.sp_loadStudData') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp_loadStudData >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_loadStudData >>>'
go
GRANT EXECUTE ON dbo.sp_loadStudData TO developers
go
GRANT EXECUTE ON dbo.sp_loadStudData TO web_group
go
GRANT EXECUTE ON dbo.sp_loadStudData TO crd_group
go
GRANT EXECUTE ON dbo.sp_loadStudData TO wd_group
go

Maybe you need a ';' after the insert statement after the .....@creationTime); — bAN, Apr 25 '13 at 12:15
Since that is the last receiving parameter I have always endedn this as shown.I have neever used ';' at the end.Even if I use it it creates error while creating the procedure. — Dark Matter, Apr 25 '13 at 12:17
Do you need the ) before 'as begin set'? And do u need also the opening ( after the stored proc name? Looking at the example here http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug668.htm looks like you dont need those. — Edper, Apr 25 '13 at 12:20
@Thirdy: Tried it, the stored proc gets created successfully without ( and ) as you point out but still getting the same error.I'm not able to point out where the error near ')' is occuring at. — Dark Matter, Apr 25 '13 at 12:27
Have you tried printing the variables in your insert to see what are its actual values? Also do you have a space before the word VALUES in your insert? — Edper, Apr 25 '13 at 12:38
Yes I tried having a print statement and yes I have a space after the values in my insert.But still getting the same error. — Dark Matter, Apr 25 '13 at 12:41

Rich Andrews · Accepted Answer · 2013-04-25T13:39:47.453

1

I'm no expert on Sybase but certainly in SQL Server you need to store the getdate() return value in a variable first.

i.e. you cant...

EXEC MyProc getdate(), getdate(), getdate()

but you can

DECLARE @Now DATETIME
SET @Now = GETDATE()
EXEC MyProc @Now, @Now, @Now

edited Apr 25 '13 at 13:39

answered Apr 25 '13 at 13:27

Rich Andrews

4,168
3
35
48

score 1 · Answer 2 · answered Apr 25 '13 at 16:01

1

Remove braces here and run

exec dbo.sp_loadStudData 113, 'ABCD', 222, 333, 'One', 02, getDate(), getdate(), getdate(), getdate(), 999, 11, getdate()

answered Apr 25 '13 at 16:01

Gopal Sanodiya

106
3

Issue with stored procedure

2 Answers2