We help IT Professionals succeed at work.

StoredProc Incorrect syntax when executing another StoredProc

Medium Priority
323 Views
Last Modified: 2012-05-06
I have an issue where I want to execute a stored procedure within another procedure and pass a Unique Identifier through to that second StoredProc.

The receiving StoredProc is a error reporting procedure that records when an error has occurred for bug fixes requirements. One of those fields is a Decription of the error. I want to pass through, as shown in the code: 'Portal ID passed does not exist: ' + @nPortalUID, but I get the error: Line 23: Incorrect syntax near '+'.

I have also tried to send through just the nPortalUID value but get the response: 'Operand type clash: uniqueidentifier is incompatible with text'.

I have tried converting the data type to nvarchar and text (the receiving fields data type is Text), but nothing seems to be working.
ALTER PROCEDURE [dbo].[FeatureSettings]
	@nFeatureId		int = 0, 
	@nPortalUID		uniqueidentifier = ''
AS
BEGIN
    -- Check for UID and set portal id
	DECLARE @nPortalId int
	SET @nPortalId = ISNULL((SELECT aPortalId FROM System_Portals WHERE nPortalUniqueId = @nPortalUID),0)
 
	-- get features details
	IF @nPortalId <> 0
		BEGIN
			SELECT @nPortalId
		END
	ELSE
		BEGIN
			-- Catch error
			EXEC System_ErrorHandle 'FeatureSettings','StoredProc','Portal ID passed does not exist: '+@nPortalUID,0
		END
	
END

Open in new window

Comment
Watch Question

Commented:
In the ELSE segment try -

BEGIN
      -- Catch error
      DECLARE @errMsg nvarchar(500)
      SET @errMsg = 'FeatureSettings','StoredProc','Portal ID passed does not exist: '+CAST(@nPortalUID AS nvarchar(50))
      EXEC System_ErrorHandle , @errMsg, 0
END

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
You have declared @nPortalId  as int.

DECLARE @nPortalId int

Kindly convert it into Varchar / char and then concat it with your string.
Concating integer with a string will give you this error.
Hi,

Try converting @nPortalUID to a VARCHAR:

'Portal ID passed does not exist: '+ CAST(@nPortalUID AS VARCHAR(50))
Beaten to it :)

Author

Commented:
Thanks for the quick response!

Author

Commented:
rrjegan17 and adathelad: I have declared@nPortalId as INT because it is an integer coming from the Portals table. You were mixing up @nPortalId with @nPortalUID!


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*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.