We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MS SQL Stored procedure ignoring statements

Medium Priority
512 Views
Last Modified: 2012-05-11
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,
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Saved my hours of putzing! Very succint and well written.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.