Problem with INSERT Trigger

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.

Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.

In case I didn't describe it clearly:

Call storedproc1

storedproc1 inserts a record into table1

table1 has an INSERT trigger

expected behavior is: storedproc1 returns value1 and value2

actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2(timothy.alvis@.gmail.com) writes:

Quote:

Originally Posted by

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.
>
Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.
>
In case I didn't describe it clearly:
>
Call storedproc1
>
storedproc1 inserts a record into table1
>
table1 has an INSERT trigger
>
expected behavior is: storedproc1 returns value1 and value2
>
actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2

Returns? This needs some clarification. A stored procedure can return
data in three different ways:
o Result set
o Output parameters
o Return value. as in EXEC @.ret = some_sp

Which do you mean?

Overall, it would help if you posted the code of the procedure, so we know
what you are talking about. Please also include the output when run the
procedure in Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David