SQL Server - Getting the return from a stored procedure from within a stored procedure

Im trying to get stp1 to get the return value from stp2. stp2 returns
a int. Here is the code that I used in stp1

delcare @return as in

set @return = (dbo.stp2 (PARAM1, PARAM2))

I have tried different way to execute this but I get a error:
either column "dbo" or the user-defined function or aggregate

Is there a way to get the return value of a stored procedure from
within a stored procedure?

  Jack Vamvas replied...

Try :

declare @return int
EXEC stp2  @ValuePassed, @return OUTPUT

PRINT @return

Also , you need to make sure stp2 , is set up appropriately



--

Jack Vamvas
___________________________________
Search  IT jobs from multiple sources-   http://www.ITjobfeed.com

  SQL Menace replied...

declare @return  int

exec @return =3D dbo.stp2 @PARAM1, @PARAM2

select @return


Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

got the following errors. Why object_schema_name is not recognized? Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27 'object_schema_name' is not a recognized built-in function name. Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42 Incorrect syntax near the keyword 'as'. Msg 15151, Level 16, State 1 check go use msdb go if not exists (select * from sys.schemas where name = 'MS_PerfDashboard') exec('create schema MS_PerfDashboard') go if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_WaitTypeCategory'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_WaitTypeCategory go create function MS_PerfDashboard.fn_WaitTypeCategory(@wait_type nvarchar(60)) returns varchar(60) as begin declare @category nvarchar(60) select @category = case when @wait_type like N'LCK_M_%' then N'Lock' when EXECUTE ON MS_PerfDashboard.fn_WaitTypeCategory TO public go if OBJECTPROPERTY(object_id ('MS_PerfDashboard.fn_QueryTextFromHandle'), 'IsTableFunction') = 1 drop function MS_PerfDashboard.fn_QueryTextFromHandle go CREATE function MS_PerfDashboard.fn_QueryTextFromHandle(@handle varbinary(64), @statement_start_offset int, @statement_end_offset int) RETURNS

Database Engine Tuning Advisor Failed to open a new connection. Additional information: Could not find stored procedure 'msdb. . sp_DTA_help_session'. (Microsoft SQL Server.Error:2812) I have checked and the stored procedure does not exist in msdb for either SQL05 or 08. - - Regards, James M SQL Server of the issue seems to be the DTAEngine not able to initialize MSDB tables when user is not a sys admin. To work around, we need to log in as sysadmin Database Engine Tuning Advisor Failed to open a new connection. Additional information: Could not find stored procedure 'msdb. . sp_DTA_help_session'. (Microsoft SQL Server.Error:2812) I have installed the latest service packs as have the exact same issue on my laptop. If i look at the tables and stored procs in MSDB I can see that they are indeed missing. I was able to

SQL Server Profiler 2005 * / / * Date: 05 / 21 / 2008 02:39:56 PM * / / * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * / - - Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 - - Please replace the text InsertFileNameHere, with an appropriate - - filename prefixed please use UNC path and make sure server has - - write access to your network share exec @rc = sp_trace_create @TraceID output, 6, N' \ vmdevserver4 \ TraceFiles \ MyTrace', @maxfilesize, NULL if (@rc ! = 0) goto error - - Client side File and Table cannot be scripted - - Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 14, 7, @on exec sp_trace_setevent @TraceID, 14, 23, @on exec sp_trace_setevent @TraceID, 14, 8, @on exec sp_trace_setevent @TraceID, 14, 64, @on exec sp_trace_setevent @TraceID, 14

Can someone take a look and help me out? Database 1 (named [dispatcherDB]) has a stored procedure called [dispatch]. This stored procedure executes a procedure in database 2 (named [providerDB]). I follow these steps to create everything and grant cross a second database [providerDB], and a login [provider], 1.3 in the [dispatcherDB] create a user [dispatcher], 1.4 make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner', N'dispatcher') 2. Executing as user = 'dispatcher': 2.1 create a stored

belong to a new domain domain2, The users in sql server 2000 SP4 are still defined as part of domain1, now I needs to some how change all sql server 2000 to reflect the new domain name. Thank you. SQL Server Discussions SQL Server (1) CREATE PROCEDURE (1) Stored procedure (1) Reconfigure (1) Deallocate (1) Raiserror (1) Varbinary (1) Nocount (1) The user name is not really your problem. It is not used during the authentication process in SQL Server. When you add a domain user to SQL Server it stores the SID and uses that. If you created new users uncheck column headers) - - to get nice output use master go IF OBJECT_ID ('sp_hexadecimal') IS NULL exec(' CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUT AS DECLARE @charvalue varchar(256) DECLARE @i