How to reference external 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

How to reference external stored procedure

Author

Topic

vini
Starting Member

11 Posts

Posted - 2007-01-15 : 02:55:02

Hi all,

I am trying to reference the following stored procedure. I was just wondering if it can be done.
I get the following error when i try to save it.
Error: The Column prefix 'CRMBackOffice.dbo' does not match with the table name or alias name used in the query

SELECT BRN_CustCode, BRN_Name1, BRN_Name2, BRN_Status
FROM [VENUS_II].[dbo].[Branches]
WHERE BRN_CustCode NOT IN ([CRMBackOffice].[dbo].[csp_GetCRMIIICustomerList])

Thanks in advance,
Vini

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-15 : 03:20:13

If your SP's output is result set, you need to save it to temporary table like this:

Create Table #temp
(
 col1 ...
 col2 ...
)

Insert #temp
Exec [CRMBackOffice].[dbo].[csp_GetCRMIIICustomerList]

Then you can refer to the temp table in your query:

SELECT BRN_CustCode, BRN_Name1, BRN_Name2, BRN_Status
FROM [VENUS_II].[dbo].[Branches]
WHERE BRN_CustCode NOT IN (Select BRN_CustCode from #temp)

Remember, your table structure should exactly match the result set in terms of data type and size. Also, I am suggesting this assuming csp_GetCRMIIICustomerList is an SQL Server SP and resides on the same server.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vini
Starting Member

11 Posts

Posted - 2007-01-15 : 03:27:18

Thanks for the reply,
indeed the second sp resides on the same sqlserver.

That is a good idea, will try that.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources