declare data source variable in a stored procedure

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

General SQL Server Forums

New to SQL Server Programming

declare data source variable in a stored procedure

Author

Topic

igalbk
Starting Member

9 Posts

Posted - 2011-03-22 : 06:10:20

Hi,
I'm using OPENDATASOURCE to access external database inside stored procedure.
I'm using this function 10 times in the stored procedure.
how can I use declare variable or other method so I'll not have to open data source each time when I accessing the external DB.
Thanks,
Igal.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 06:31:17

Once access the data and store it in temporary table variable or tamporary tables
and use across the stored procedure.

Vaibhav T

If I cant go back, I want to go fast...

igalbk
Starting Member

9 Posts

Posted - 2011-03-22 : 06:56:22

Yes,
but this table declaration have to be with the columns name and types,
for example:


DECLARE @ProductTotals TABLE
(
  ProductID int,
  Revenue money
)

The table definition will be depended...
do you know other method?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 07:37:36

You can use "SELECT * INTO #temptable FROM <ResultSet>".
So need of Column Names and datatypes required.
It will just create replica of result set.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources