Link to home
Start Free TrialLog in
Avatar of KWDave
KWDaveFlag for United States of America

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].[uspQCLI12CalcResidentInterview]    Script Date: 04/23/2011 13:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspQCLI12CalcResidentInterview]
      @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,
ASKER CERTIFIED SOLUTION
Avatar of brutaldev
brutaldev
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KWDave

ASKER

Saved my hours of putzing! Very succint and well written.