SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 exec sp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1422 Posts

Posted - 02/19/2013 :  02:33:11  Show Profile  Reply with Quote
Hi,
inside a stored procedure which is created in databaseA I would like to pull the data from databaseB and place it into a table variable as follows

create procedure uspGetData

@Date date, @value bit

as

declate @tblData table
(
field1 varchar(50), field2 date
)

--insert into tblData
exec DatabaseB.dbo.uspAnotherProcedure @Date=@Date, @value=@value

Note that the above sp does indeed execute the stored proc in databaseB BUT if I take out the commented line i.e. insert into, then it fails and the message is:
A severe error occurred on the current command. The results, if any, should be discarded.

Any thoughts please?
Thanks

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 02/19/2013 :  03:58:25  Show Profile  Reply with Quote
That statement should be
insert into @tblData


USE study
GO
CREATE PROCEDURE uspAnotherProcedure 
AS
SELECT employee_id, department_id, salary FROM employees WHERE department_id Is null

USE DB2
GO
CREATE procedure uspGetData
As
BEGIN
	declare @tblData table( empid INT, deptid INT, sal DEC(8,2))
	insert into @tblData 
	exec study.dbo.uspAnotherProcedure 
	SELECT * FROM @tblData
END
EXEC uspGetData


--
Chandu
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1422 Posts

Posted - 02/19/2013 :  04:06:37  Show Profile  Reply with Quote
Thanks
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 02/19/2013 :  04:54:29  Show Profile  Reply with Quote
quote:
Originally posted by arkiboys

Thanks

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000