calling one stored proc from another using command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calling one stored proc from another using command

Author

Topic

vihrao
Starting Member

1 Post

Posted - 2010-08-17 : 15:42:38

In my stored procedure I am passing a table and a column name as inputs which may change everytime. So I build command and execute it. I want the output in another variable @curr_id. This stored procedure will be called by second stored procedure by using @curr_id as input.

My problem is populating the @curr_id vriable. It is returned as zero. If I remove the curr_id variable then it works(see commented line)

Can someone pl. tell me - how to get @curr_id populated and returned as OUTPUT? - how to call the first stored proc from a second stored proc?

ALTER PROCEDURE [dbo].[sp_astm_getcurrid]
@ColName as nvarchar(250),
@TblName as nvarchar(250),
@curr_id nvarchar(max) OUTPUT
AS
BEGIN
DECLARE @cmd nvarchar(max)

SET @cmd =N'SET '+@curr_id+'= SELECT MAX('+@ColName+') FROM '+@TblName;
--SET @cmd =N'SELECT MAX('+@ColName+') FROM '+@TblName;

EXEC (@cmd)

END

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-17 : 16:04:30

You're almost there. Try this

alter PROCEDURE [dbo].[sp_astm_getcurrid]
@ColName as nvarchar(250),
@TblName as nvarchar(250),
@curr_id nvarchar(max) output

AS
BEGIN
DECLARE @cmd nvarchar(max)
declare @vs_parms nvarchar(max)

set @vs_parms = '@currID nvarchar(max) output'

SET @cmd =N' SELECT @currID = MAX('+@ColName+') FROM '+@TblName;

exec sp_executeSQL @cmd, @vs_parms, @currID = @curr_Id output

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources