Solved

SQL Server 2000 Cursor Issue with Variable Values adding concatenated text

Posted on 2010-08-30
9
409 Views
Last Modified: 2012-05-10
Scenario:
I have a stored procedure that I need to loop through records in a log table and send out email messages to users.   I am using a CURSOR to loop through each record in the table and getting 2 field values that I am trying to concatenate a variable declare within the stored procedure.

I am using a SET statement Like this  SET @Body = @Body + 'Table Name: ' + @TableName  See full code below.

The problem is when I use this method in the CURSOR the final results  come out blank.  I am using the PRINT function to view the results.   If I just use SET @Body = 'Table Name:' + @TableName and PRINT out each line, it works fine.  Don't Understand why using the @Body = @Body + "Additional Text'  does not work, I use this type on Concatenation all the time, not sure why it does not work within the cursor.   Here is my code:

ALTER PROCEDURE [dbo].[spEmailControlTableChange]
      -- Add the parameters for the stored procedure here


AS
DECLARE @Comments varchar(500)
DECLARe @TableName varchar(75)
DECLARE @emailFrom varchar(75)
DECLARE @EmailTo varchar(500)
DECLARE @Subject varchar(255)
DECLARE @Body varchar(8000)
DECLARE @BodyLine varchar(8000)
DECLARE @RecCount int
DECLARE @TotalCount int

/********** Get Total Count of all Records that Have Been Updated*/
SET @TotalCount = (Select Count(recID) as RecCount from dbo.LVRG_CTRL_ControlTableChangeLog WHERE UpdateFlag = 1)

SET @RecCount = 1
SET @emailFrom = 'ABMTools@Hp.com'
SET @emailTo = 'jesse.holmes@hp.com'
--SET @emailTo = 'jesse.holmes@hp.com, Pramod.bohra@hp.com, Rachael.Mclarty@hp.com, Angel.Garza@hp.com'
SET @subject = 'SLA Control Table Change Notification - Change occured on the Folowing Table(s): '
SET @Body = 'The Following Tables Have been Changed' + CHAR(13)
SET @Body = @Body + '===================================================='

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;



DECLARE @TableCursor CURSOR

SET @TableCursor = CURSOR -- FAST_FORWARD
FOR
Select TableName, Comments
From dbo.LVRG_CTRL_ControlTableChangeLog
WHERE UpdateFlag = 1

OPEN @TableCursor
FETCH NEXT FROM @TableCursor
INTO @TableName,@Comments

WHILE @@FETCH_STATUS = 0
BEGIN

--******* Add a new Line to Body Text *************
SET @Body = @Body + 'Table Name: ' + @TableName + ' - Change Desc: ' + @Comments + CHAR(13)
PRINT @RecCount
PRINT @Body

--******* Increment Record Count **********
SET @RecCount = @RecCount + 1


IF @RecCount > @TotalCount
BEGIN      
      PRINT 'Final Body: = ' + @Body
      PRINT 'DONE'
END      
0
Comment
Question by:jholmes0724
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 

Author Comment

by:jholmes0724
ID: 33560976
One other note:   Here is what the output looks like when i DO NOT concatenate and just Print out each RecCount Value and @Body value usint SET @Body = 'TEXT HERE'   So I know I am getting values from the cursors select statement:

1
Table Name: AARP_DATA_D1_BillMatrix - Change Desc: TeST
2
Table Name: AARP_DATA_D1_BillMatrix - Change Desc: Record Updated where Company = AARP and Region = CRBLA and Model Name = 3810S and TranslateModel = TestModel
3
Table Name: AARP_DATA_D1_BillMatrix - Change Desc: Record Updated where Company = AARP and Region = CRBLA and Model Name = 3810S
4
Table Name: LVRG_CTRL_DelivLookupTable - Change Desc: Record Updated for Company AALP and Deliv = ASDT001
5
Table Name: LVRG_CTRL_DelivLookupTable - Change Desc: Record Updated for Company AARP and Deliv = DT004
6
Table Name: LVRG_CTRL_DelivLookupTable - Change Desc: Record Updated for Company AARP and Deliv = DT004
7
Table Name: LVRG_CTRL_DelivLookupTable - Change Desc: Record Updated for Company AALP and Deliv = ASDT001
8
Table Name: LVRG_CTRL_DelivLookupTable - Change Desc: Record Updated for Company AALP and Deliv = ASDT001
9
Table Name: LVRG_CTRL_SiteLookupTable - Change Desc: Record Updated for Company AARP and Location_Code value: RFD
Final Body: = Table Name: LVRG_CTRL_SiteLookupTable - Change Desc: Record Updated for Company AARP and Location_Code value: RFD
DONE
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33561003
when the result is blank, any of your values should be null. kindly check the values
0
 

Author Comment

by:jholmes0724
ID: 33561029
I have, I did have some that where null and I fixed that but I still got the same issue.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Expert Comment

by:Jini Jose
ID: 33561040
if you get a blank value on a set stament, then it should be a null value in the string value.
0
 
LVL 3

Accepted Solution

by:
jvejskrab earned 500 total points
ID: 33561146

try

SET @Body = @Body + 'Table Name: ' + ISNULL(@TableName,'') + ' - Change Desc: ' + ISNULL(@Comments,'') + CHAR(13)
0
 

Author Comment

by:jholmes0724
ID: 33561173
Not sure what you are getting at, The variable @Body Gets a string value right in the beginning of the SP.  The Final Output of the @Body variable would never be null.   I am trying to add another line to the string Variable @Body on every iteration of the cursor loop.  I DO NOT Get a blank value if I simply set the variable like this @Body = @TableName.  It will store the value of @TableName in the @Body Variable but as soon as I try to add to the @Body Varialbe using SET @Body = @Boday + 'Additionl values'  it does not work.   That is what I am trying to get answered.  Please explain why you keep asking me about the null values, there are no null values.
0
 
LVL 3

Expert Comment

by:jvejskrab
ID: 33561318
weird, there must be some NULL values in comments or tableName. Concatenating string and NULL = NULL
0
 

Author Closing Comment

by:jholmes0724
ID: 33561333
Thank you very much.  Sorry I didn't understand why the null was an issue but using this code makes it work.  I still don't fully understand why.  But that's why your the expert.   Thanks again.
0
 
LVL 3

Expert Comment

by:jvejskrab
ID: 33561421

It depend's on database setting CONCAT_NULL_YIELDS_NULL (right click on database in MS -> Properties -> options -> Concatenate.... )

or you can write in your script:

SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL OFF

another solution could be SET CONCAT_NULL_YIELDS_NULL OFF in the beginning of the script and then you can use the SET command without ISNULL
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question