what is allowed inside a stored procedure/view

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

what is allowed inside a stored procedure/view

Author

Topic

JimWashburn
Starting Member

2 Posts

Posted - 2011-06-21 : 14:48:45

I am a bit unclear on what can and cannot be inside a procedure/view declaration. The following code produces a syntax error around the SELECT statement. However simply copy and pasting the select statement into Query Analyzer yields the correct results. I am using SQL Server 2000 and the Pubs database as a test for this.

CREATE PROCEDURE dbo.sp_TestConnection(@From datetime,
@To datetime)
SELECT SALES.stor_id FROM
OPENDATASOURCE('SQLOLEDB',
'Data Source=MyServer;User ID=MyID;integrated security=SSPI').pubs.dbo.sales as SALES
INNER JOIN OPENDATASOURCE('SQLOLEDB',
'Data Source=MyServer;User ID=MyID;integrated security=SSPI').pubs.dbo.stores as STORES ON SALES.stor_id = STORES.stor_id

-- Jim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-21 : 14:51:55

CREATE PROCEDURE dbo.sp_TestConnection(@From datetime,
@To datetime) AS
SELECT SALES.stor_id FROM
OPENDATASOURCE('SQLOLEDB',
'Data Source=MyServer;User ID=MyID;integrated security=SSPI').pubs.dbo.sales as SALES
INNER JOIN OPENDATASOURCE('SQLOLEDB',
'Data Source=MyServer;User ID=MyID;integrated security=SSPI').pubs.dbo.stores as STORES ON SALES.stor_id = STORES.stor_id

JimWashburn
Starting Member

2 Posts

Posted - 2011-06-21 : 15:00:05

Sigh, don't I feel stupid now. Thanks for the very quick answer, I appreciate it. Now I need to look over the forums also and try to return the favor to someone.

-- Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources