Stored Procedure execution syntax

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 2005 Forums

Transact-SQL (2005)

Stored Procedure execution syntax

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-10-29 : 07:59:50

I want to execute a stored procedure (SQL Server 2005 Express) with the script:

USE [debt]
GO

DECLARE @return_value int,
@TimeStamp timestamp

EXEC @return_value = [dbo].[InsertDebtorKeyEvents]
@DebtorID = 99482,
@KeyEventCode = 1,
@Notes = N'Notes here',
@CreatedBy = 93,
@CreatedOn = 20081029,
@ModifiedBy = NULL,
@ModifiedOn = NULL,
@KeyEventStartDate = 20081029,
@KeyEventEndDate = 20081130,
@AlertDays = 10,
@TimeStamp = @TimeStamp OUTPUT

SELECT @TimeStamp as N'@TimeStamp'

SELECT 'Return Value' = @return_value

GO

but I receive the error 'Error converting data type int to datetime' for the datetime fields. What should the correct syntax be please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:02:22

you need another output parameter in your sp to get returnvalue and use it.then you can get it as below


DECLARE @return_value int,
@TimeStamp timestamp

EXEC [dbo].[InsertDebtorKeyEvents]
@DebtorID = 99482,
@KeyEventCode = 1,
@Notes = N'Notes here',
@CreatedBy = 93,
@CreatedOn = 20081029,
@ModifiedBy = NULL,
@ModifiedOn = NULL,
@KeyEventStartDate = 20081029,
@KeyEventEndDate = 20081130,
@AlertDays = 10,
@TimeStamp = @TimeStamp OUTPUT,
@retvalparameter=@return_value OUTPUT

SELECT @TimeStamp as N'@TimeStamp'

SELECT @return_value AS 'Return Value' 

GO

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-10-29 : 08:08:57

But now that gives me:

'Procedure or function InsertDebtorKeyEvents has too many arguments specified.'

Are you sure visakh16?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:11:43

quote:
Originally posted by OldMySQLUser

But now that gives me:

'Procedure or function InsertDebtorKeyEvents has too many arguments specified.'

Are you sure visakh16?

did you add that parameter to your sp? adding it just to EXEC will throw error. What i asked you was to add parameter in stored procedure code and then use it in EXEC

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-10-29 : 08:20:21

OK. Sorry for that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources