Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

StoredProc Incorrect syntax when executing another StoredProc

Posted on 2009-02-10
6
Medium Priority
?
312 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

0
Comment
Question by:longbloke69
6 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23599797
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23599802
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.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 23599817
Hi,

Try converting @nPortalUID to a VARCHAR:

'Portal ID passed does not exist: '+ CAST(@nPortalUID AS VARCHAR(50))
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Expert Comment

by:adathelad
ID: 23599822
Beaten to it :)
0
 

Author Closing Comment

by:longbloke69
ID: 31545020
Thanks for the quick response!
0
 

Author Comment

by:longbloke69
ID: 23600743
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!


0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

571 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