Stored procedure to create a view in ANOTHER database.

Question

Stored procedure to create a view in ANOTHER database.

ben.brugman

SSChampion

Points: 13369
More actions
July 1, 2014 at 9:34 am

#396986

From within a stored procedure I want to generate a View in ANOTHER database.
I can do that from management studio.
Can I do this from a stored procedure?
See the example below (simplified).
Ben
--
--
declare @work1 varchar(8000)
set @work1 =
'
USE AnotherDatabase
GO
create view The_view as select
A.field1 AS A1,
A.field2 AS A2,
A.field3 AS A3,
A.field4 AS A4,
B.field1 AS B1,
B.field2 AS B2,
B.field3 AS B3,
B.field4 AS B4,
FROM AAAA AS A Right outer join [YetAnotherDatabase].[dbo].BBBB On A.x = B.y
GO
'
exec (@work1)
-- Using a stored procedure produces (linenumbers do not match):
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'GO'.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You can't use GO in an EXEC statement for dynamic code.

You could use the SP sp_executesql from the destination database. You could complicate it more and use dynamic code to run sp_executesql to change the database.

USE master

GO

EXEC sp_executesql N' Test.dbo.sp_executesql N''CREATE VIEW Sometest

AS

SELECT ''''This is a Test'''' AS String'''

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

x SSC-Insane Points: 23660 More actions · Answer 2

declare @work1 varchar(8000)

set @work1 =

'

use destination_database;

declare @work2 varchar(8000);

set @work2 = ''create view The_view as select * FROM The_table'';

exec (@work2);

'

exec (@work1)

Just thought I'd post something ridiculously impractical today. But I'd mark Luis's post as the solution :hehe:

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Thanks for both answers.

I'll probably will go for Luis solution.

This will solve my problem of generating views for more than one database using a single stored procedure.

Thx

Ben