Solved

Help debug a stored procedure

Posted on 2006-06-22
3
406 Views
Last Modified: 2008-02-01
I'm getting an error on this sp that I can't figure out.  It says line 46 (the declaration of the variable @AuthEmail varchar(200)) cannot call method on nvarchar, but the stored procedure will compile if line 73 (setting the value of @NextBody) is commented out.  Can anyone figure out what the problem is?

Thanks!

valkyrie_nc



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE sp_markPOAuthorized
      @POID int
      ,@ID int
      ,@Authorized bit
      ,@Rejected bit
      ,@Approved bit
      ,@Comments varchar(2000)
      ,@UserID int
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE
            @NextSubject nvarchar(255)
            ,@NextBody nvarchar(4000)
            ,@userEmail varchar(200)
            ,@EmailID int
            ,@MinLimit money
            ,@CCID int
            ,@CCAuthUser int
            ,@TotalAmount money
            ,@CCUserEmail varchar(200)
            ,@CCUserName varchar(200)
            ,@CCSubj varchar(500)
            ,@CCBody varchar(2000)
            ,@CCLastFour int
            ,@NotifySubject nvarchar(255)
            ,@NotifyBody nvarchar(4000)
            ,@RejectSubject nvarchar(255)
            ,@RejectBody nvarchar(4000)
            ,@AuthID int
            ,@Limit money
            ,@AuthEmail varchar(200)
            ,@AuthUserID int
            ,@CharPOID varchar(10)

      SELECT @userEmail = User.userEmail FROM Normalization.dbo.Users Users INNER JOIN PurchaseOrders ON PurchaseOrders.UserID = Users.userID WHERE PurchaseOrders.POID = @POID
      SELECT @TotalAmount = TotalAmount FROM PurchaseOrders WHERE POID = @POID
      SET @CharPOID = CONVERT(varchar, @POID)

      IF (@Approved = 'True')
      BEGIN
            UPDATE POAuthorizations SET IsApproved = @Approved, DateAuthorized = GetDate(), RejectionComments = @Comments WHERE ID = @ID
            -- seek next auth up the chain
            SELECT TOP 1 @AuthID = Auth.AuthID, @Limit = Auth.Limit, @AuthUserID = EH.UserID FROM Authorizations Auth INNER JOIN Normalization.dbo.EmpHierarchy EH ON Auth.UserID = EH.UserID WHERE EH.empUserID = @UserID AND IsPrimary = 'True'
            IF (@Limit >= @TotalAmount)
            BEGIN
                  -- user can authorize
                  INSERT INTO POAuthorizations (POID, AuthID, SignOffRequired, AuthorizationRequired) VALUES (@POID, @AuthID, 'False', 'True')
            END
            ELSE
            BEGIN
                  -- user can approve but not authorize
                  INSERT INTO POAuthorizations (POID, AuthID, SignOffRequired, AuthorizationRequired) VALUES (@POID, @AuthID, 'True','False')
            END

            -- send email to new authorizer
            SELECT @AuthEmail = Users.userEmail FROM Normalization.dbo.Users Users INNER JOIN Authorizations Auth ON Users.userID = Auth.UserID WHERE Auth.AuthID = @AuthID
            SET @NextSubject = 'A Purchase Order Requires Your Authorization'
            SET @NextBody = 'Please visit your Purchase Order portal on the Intranet and <a href=http://www.url.com/AuthorizePO.aspx?id=' + @CharPOID + '>review PO# ' + @CharPOID + '</a> for authorization or rejection.<br><br>Please do not reply to this email address. No replies will be delivered'
            EXEC msdb..sp_send_dbmail @recipients=@AuthEmail, @subject=@NextSubject, @body=@NextBody, @body_format='HTML'

            DECLARE @POAuthID int
            SET @POAuthID = IDENT_CURRENT('POAuthorizations')
            INSERT INTO EmailSent (DateSent, UserID) VALUES (GetDate(), @AuthUserID)
            SET @EmailID = IDENT_CURRENT('EmailSent')
            INSERT INTO AuthEmails (POAuthID, EmailID) VALUES (@POAuthID, @EmailID)

      END
      ELSE IF (@Authorized = 'True')
      BEGIN
            UPDATE POAuthorizations SET IsAuthorized = @Authorized, DateAuthorized = GetDate(), RejectionComments = @Comments WHERE ID = @ID
            UPDATE PurchaseOrders SET StatusID = '9' WHERE POID = @POID
            -- send email to creator
            SET @NotifySubject = N'Purchase order has been authorized'
            SET @NotifyBody = N'Purchase order #' + @CharPOID + ' has been authorized.<br><br>Please do not reply to this email address.  No replies will be delivered.'
            EXEC msdb.dbo.sp_send_dbmail @recipients=@userEmail, @subject=@NotifySubject, @body=@NotifyBody, @body_format='HTML'

            SELECT ISNULL(CCID,'0') AS CCID FROM PurchaseOrders WHERE POID = @POID      
            IF NOT (@CCID = '0')
            BEGIN
                  SELECT @CCLastFour = LastFour FROM [Normalization].[dbo].[CreditCard] WHERE ID = @CCID
                  SELECT @CCAuthUser = UserID FROM [Normalization].[dbo].[CreditCardAuthorizedUsers] WHERE CCID = @CCID AND IsAuthorized = '1'
                  SELECT @CCUserEmail = userEmail, @CCUserName = userFullName FROM [Normalization].[dbo].[Users] WHERE userID = @CCAuthUser
                  SET @CCSubj = N'Credit card funds used'
                  SET @CCBody = N'Your credit card ending with ' + CONVERT(varchar, @CCLastFour) + ' was used for PO # ' + @CharPOID + ' for the amount of $' + CONVERT(varchar, @TotalAmount) + '. This purchase order has been authorized.<br><br>Please do not reply to this email address.  No replies will be delivered.'
                  EXEC msdb.dbo.sp_send_dbmail @recipients=@CCUserEmail,@subject=@CCSubj,@body=@CCBody, @body_format='HTML'
            END
      END
      ELSE IF (@Rejected = 'True')
      BEGIN
            UPDATE POAuthorizations SET IsRejected = @Rejected, DateRejected = GetDate(), RejectionComments = @Comments WHERE ID = @ID
            UPDATE PurchaseOrders SET StatusID = '6' WHERE POID = @POID
            -- send email to creator
            SET @RejectSubject = N'Purchase order has been rejected'
            SET @RejectBody = N'Purchase order #' + @CharPOID + ' has been rejected.  If you wish to edit this purchase order, please visit <a href=http://www.url.com/EditPO.aspx?action=edit&id=' + @CharPOID + '>the edit page</a>.<br><br>Please do not reply to this email address.  No replies will be delivered.'
            EXEC msdb.dbo.sp_send_dbmail @recipients=@userEmail, @subject=@RejectSubject, @body=@RejectBody, @body_format='HTML'      
      END      

END
GO
0
Comment
Question by:valkyrie_nc
  • 2
3 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 16959237
Hi valkyrie_nc,

Not necessarily an answer, but shouldn't the line be:

     SELECT @userEmail = Users.userEmail FROM Normalization.dbo.Users Users INNER JOIN PurchaseOrders ON PurchaseOrders.UserID = Users.userID WHERE PurchaseOrders.POID = @POID

You have User.userEmail which may confuse things.


Tim Cottee
0
 
LVL 9

Author Comment

by:valkyrie_nc
ID: 16959249
That was it, Tim!  One teensy typo my tired eyes couldn't find.  Bless you!

valkyrie_nc
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 16959343
TimCottee,

Glad to have helped.

Tim
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

837 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