SP miss fire

Microsoft Windows
SQL Server
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[swr_get_work_requ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[swr_get_work_requ] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.swr_get_work_requ ----------------------------------------------------------------------------------------- -- DESCRIPTION: Saves a Work Order Trade for a Work Order into f_WO_Trades -- Outputs: -- Two output fields -- WOT_PK int Primary Key of Work Request Trade. -- Being returned as output parm instead of recordset so it's easier -- to use value inside other stored procedures. -- RTN_CODE int 0 = success. -- $Header: X:cvsroot/SOB/Database/TMA/script/swr_get_work_requ.sql,v 1.1 2006/04/08 17:27:27 hstaples Exp $ -- $Revision: 1.1 $ -- $Date: 2006/04/08 17:27:27 $ -- $Log: swr_get_work_requ.sql,v $ -- Revision 1.1 2006/04/08 17:27:27 hstaples -- Initial CheckIn. Tested OK. -- ----------------------------------------------------------------------------------------- ( @Effective_Date datetime ) AS SELECT DECLARE @LOG_PK int DECLARE @SWR_STATUS varchar(45) SET @SWR_STATUS = '' -- TODO: Create temp table (wide enough to hold all fields in SELECT) LOG_PK, LOG_REQUESTOR, LOG_REQ_PHONE, LOG_CHAR_60_1, LOG_CHAR_30_4, LOG_CN_FK, LOG_CREATE_DATE, LOG_REQUEST, (LOG_STATUS), 'TBD' As LOG_TYPE, LOG_FU_FK, LOG_FB_PK, LOG_LT_FK, LOG_CREATE_DATE DECLARE crsLog As SELECT LOG_PK FROM f_Requests FETCH NEXT FROM crsLog INTO @LOG_PK WHILE @@FETCH_STATUS <> -1 BEGIN EXEC swr_get_work_requ_stat @LOG_PK, @SWR_STATUS out -- TODO; INSERT RECORD INTO TEMPTABLE, all other fields and the value @SWR_STATUS plugged into the field @LOG_STATUS FETCH NEXT FROM crsLog INTO @LOG_PK END CLOSE crsLog DEALLOCATE crsLog -- Return Result Set SELECT * FROM whatevertemptablenameis ORDER BY LOG_PK DROP whatevertemptablenameis -- TODO: Set Return Code SET @Rtn_Code = -990 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

Answer Wiki

Thanks. We'll let you know when a new response is added.

I would say your problem resides with the exec statement and the stored procedure. You also do not need to set @SWR_STATUS as its an output variable and not used prior to stored proc execution – just drop it.

Try this:

EXEC ( “swr_get_work_requ_stat ” + @LOG_PK + “, ” + “@SWR_STATUS out” )

If this does not point you in the right direction or resolve the issue, please attach a copy of the stored procedure.


Discuss This Question:  

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: