I am working to create a stored procedure that will read data from a table, do some trivial logic, and update another table I have theuser stored procedure (usp) called from a trigger when the record is designated by the user to be "locked." The trigger calls the proper usp when it executes. For my first testing i just printed the name of the usp. This worked fine.
Then i added the initial database manipulation statements and select for testing.
It returns the values in the database, but does not update the table (QCLIEvalResidents) or print the debugging statement.
It compiles and executes without error.
I am using MS SQL 2008 R2.
Here is the code:
/****** Object: StoredProcedure [dbo].[uspQCLI12CalcResidentInterview] Script Date: 04/23/2011 13:25:23 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[uspQCLI12CalcResidentInterview]
WITH EXECUTE AS CALLER
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @InstrumentInstrumentID int
DECLARE @Resident int
DECLARE @Facility int
DECLARE @QCLIID int
SELECT [Resident], [Facility], [Instrument]
WHERE InstrumentInstanceID = @InstrumentInstanceID
SET @QCLIID = 1
SET QCILID = @QCLIID, Resident = @Resident, Facility = @Facility, InstrumentInstance = @InstrumentInstanceID
Print N'Resident = ' + CAST(@Resident AS varchar(8)) +
' Facility = ' + CAST(@Facility AS varchar(8)) +
' InstrumentInstance = ' + CAST(@InstrumentInstanceID AS varchar(8)) +
' QCLIID = ' + CAST(@QCLIID AS varchar(8))
Thanks for your help,