Store Proc. Return value invalid Error

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)

Store Proc. Return value invalid Error

Author

Topic

bkbinani
Starting Member

15 Posts

Posted - 2011-11-26 : 01:19:49

Dear Experts,

I had created a store procedure to get the date but I failed.
My Codes:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Alter procedure [dbo].[get_ref_date] 
 (@lcuid uniqueidentifier, @dref_date datetime output)
as

set @dref_date = 
(Select ditem_trnDate from dat_itm_detail where uitem_trnid = @lcuid)

Return @dref_date

Here Uitem_trnid is pkey.

The above procdure cudn't be save as stating error about conversion
date.
Error"Msg 257, Level 16, State 3, Procedure get_ref_date, Line 11
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."

Please Help me.
Thanx in Advance.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-26 : 06:18:45

RETURN expects an integer to be returned
normally used to return a status value

one way to return the date value

declare @temprable table
(
dref_date datetime
)

insert into @temprable
execute [dbo].[get_ref_date] @lcuid

remove the dref_date from the stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Alter procedure [dbo].[get_ref_date]
(@lcuid uniqueidentifier)
as

Select ditem_trnDate from dat_itm_detail where uitem_trnid = @lcuid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-26 : 12:20:23

see

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bkbinani
Starting Member

15 Posts

Posted - 2011-11-28 : 00:21:56

Thanx for Sharing such important thing.

It was unknow to me about RETURN value of a SP.

Nice

Hope to c u again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 03:23:52

quote:
Originally posted by bkbinani

Thanx for Sharing such important thing.

It was unknow to me about RETURN value of a SP.

Nice

Hope to c u again

for your case you should be using OUTPUT parameter as suggested in posted link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources