Skip to main content
Known Participant
March 12, 2009
Question

My cfstoredproc is there but nothing happen on the db

  • March 12, 2009
  • 1 reply
  • 632 views
I created a temp. error log, what I did, I wrote a detele statement to clear up all the old error records in the begining and after that, I'm inserting new error(s) if they become available.
I use stored procedure and the reason for doing this is just to practise with <cfstoredproc tag.
The funny thing is, I can see the stored proc was run from the debug view but the record did not get deleted? Have I done something wrong with the code? no error on the screen.
I use CF8, DB Sybase & windows

<!--- start with a clean up older error records --->
<cfstoredproc procedure="sp_deletedOldError" datasource="#mydb#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="@user1" value="#Trim(session.user)#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="@user2" value="">

</cfstoredproc>

Here is my simple procedures:
CREATE PROCEDURE dbo.sp_deletedOldError

@7329667 varchar (2),
@user@mac varchar (2)

AS
BEGIN
Delete from tbl_error
Where users IN ('@user1','@user2')
END












This topic has been closed for replies.

1 reply

Inspiring
March 12, 2009
alecken wrote:
> I can see the stored proc was run from the debug view but the record did not get deleted?

That is feasible. Your statement is structured to delete records that match the conditions in your WHERE clause. If nothing is being deleted, then no records matching those conditions were found.

> Where users IN ('@user1','@user2')

Because your variables are enclosed in single quotes, they will be treated as literal strings, not variables. So the query will search for the literal words "@user1" and "@user2", not the _values_ of those variables. Remove the single quotes.

> CREATE PROCEDURE dbo.sp_deletedOldError

I do not know if it still applies, but with MS SQL databases it was usually recommended that you _not_ name stored procedures with the "sp_" prefix, as it is the naming convention used for system procedures.
Inspiring
March 12, 2009
> with MS SQL databases

Oh, nevermind. I see you are using Sybase, not MS SQL. I do not know if the unwritten rule about stored procedure naming conventions applies to Sybase as well.