• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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,
0
KWDave
Asked:
KWDave
1 Solution
 
brutaldevCommented:
All the variables that you declare will be NULL.

After you select values from the table you update the entire QCLIEvalResidents table wilt NULL values (except for @QCLIID). As soon as you try an concatenate a NULL to a string in the PRINT statement the whole thing will return NULL so that explains why that is not being displayed either because NULL just prints as a blank string.

1. Assign the variables somewhere either by selecting into them or setting them as you do with @QCLIID (EG: SELECT @Resident = Resident, @Facility = Facility FROM InstrumentInstances WHERE InstrumentInstanceID = @InstrumentInstanceID).
2. Always wrap possible NULL string in ISNULL to make sure your PRINT statements work (EG: Print N'Resident = ' + ISNULL(CAST(@Resident AS varchar(8))), 'None')
3. Make sure you add a WHERE onto your UPDATE statement unless you want to update the entire table.

0
 
KWDaveAuthor Commented:
Saved my hours of putzing! Very succint and well written.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now