KWDave
asked on
MS SQL Stored procedure ignoring statements
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:
USE [gsqisdev]
GO
/****** Object: StoredProcedure [dbo].[uspQCLI12CalcReside ntIntervie w] Script Date: 04/23/2011 13:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspQCLI12CalcReside ntIntervie w]
@InstrumentInstanceID [int]
WITH EXECUTE AS CALLER
AS
BEGIN
-- 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]
FROM InstrumentInstances
WHERE InstrumentInstanceID = @InstrumentInstanceID
SET @QCLIID = 1
UPDATE QCLIEvalResidents
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))
END
GO
Thanks for your help,
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:
USE [gsqisdev]
GO
/****** Object: StoredProcedure [dbo].[uspQCLI12CalcReside
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspQCLI12CalcReside
@InstrumentInstanceID [int]
WITH EXECUTE AS CALLER
AS
BEGIN
-- 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]
FROM InstrumentInstances
WHERE InstrumentInstanceID = @InstrumentInstanceID
SET @QCLIID = 1
UPDATE QCLIEvalResidents
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
' QCLIID = ' + CAST(@QCLIID AS varchar(8))
END
GO
Thanks for your help,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER