valkyrie_nc
asked on
Help debug a stored procedure
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.EmpHiera rchy 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('POAuthoriza tions')
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].[Cre ditCard] WHERE ID = @CCID
SELECT @CCAuthUser = UserID FROM [Normalization].[dbo].[Cre ditCardAut horizedUse rs] WHERE CCID = @CCID AND IsAuthorized = '1'
SELECT @CCUserEmail = userEmail, @CCUserName = userFullName FROM [Normalization].[dbo].[Use rs] 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=@C CSubj,@bod y=@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
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.EmpHiera
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('POAuthoriza
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].[Cre
SELECT @CCAuthUser = UserID FROM [Normalization].[dbo].[Cre
SELECT @CCUserEmail = userEmail, @CCUserName = userFullName FROM [Normalization].[dbo].[Use
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,@
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TimCottee,
Glad to have helped.
Tim
Glad to have helped.
Tim
ASKER
valkyrie_nc