create view within 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

SQL Server 2005 Forums

Transact-SQL (2005)

create view within a stored procedure

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 08:48:38

hi,

Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go

go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?

thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-21 : 08:57:00

you can use dynamic sql and Exec()

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-21 : 08:57:15

Prefix view with db name.

Exec('Create View [myDb1].dbo.myv1
as
....')

But why you want to create view inside a stored proc?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 09:18:51

ha,spirit,
thats what ive been trying to do but
when i use exec and preapend the name of the database I get the following message:
"'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name."

the stored procedure generates dimension tables for further use in analysis server, some are views

thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-21 : 09:40:20

exec(' use yourDBName; go; create view ....')

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 09:50:59

spirit, I get the following message when using:
exec(' use [myDb]; go; create view dimDates_1 as select * from [myDb].dbo.dimDates')

--------
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.

-------
thank you

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-21 : 09:53:31

This sounds crazy. If you're doing it so you can query it a couple of times within the stored proc, then I'd think you'd be much better off inserting data into a table variable or temp table, and querying off of it.

But then again, why not make the view a permanent object?

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 09:58:37

its not a temp object rud. its a permanent object, I will use the views produced in analysis services, I could easily accomplish this by simply crating tables since they allow me to preapend the target database name, but I want these specifically to be views because they can all be derived easily from a single underlying table,

thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 10:01:08

Since it is a permanent object can't you create the view before hand ? Must you do it in a stored procedure ? What if the stored procedure is run twice ?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-21 : 10:06:54

Another work around to create a view on another database is to use the sp_executesql but with the database name included with it:

EXEC myDb1..sp_executesql N'CREATE VIEW myView1 AS ...'

SQL Server Helper
http://www.sql-server-helper.com

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 10:08:19

k, I could create beforehand but I want to reduce the number of steps in my overall script to the minimum, also I would like to wrap all my code into stored procedures, why should I need
---
create object myPermanentObject
create proc myProc doSomething...
---
when I can have
--
create proc myProc create object myPermanentObject; doSomething...
------
in this way, all my scripts reside in stored procedures so I can easily schedule them in a job, instead of having to write down sql code in the job scheduler or open the file everytime I need to run a script

thank you

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 10:16:44

ssh:

thank you the

EXEC myDb1..sp_executesql N'CREATE VIEW myView1 AS ...'

worked!! problem solved

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-21 : 10:23:12

Sorry, but this kind of maintenance task should not reside in a stored proc. You should keep in a .sql file. The purpose of a stored proc is to encapsulate reusable code.

heze
Posting Yak Master

192 Posts

Posted - 2007-05-21 : 10:34:08

Rude, when you say "should not", according to what standard? I agree in the reusability part but what about the handling and automation part, isnt it easier to schedule stored procedures than .sql files? also I can allways call this proc frfom a another proc in a simple way, why do you think I should stick to your statement at all times?

"this kind of maintenance task should not reside in a stored proc"

thank you

WyldOne
Starting Member

4 Posts

Posted - 2008-04-22 : 19:04:29

Yes, you can.

create proc someProc ()
as
begin
declare @sql nvarchar(4000)
declare @db sysname
select @sb = '[myDb1]'
select @sql = 'exec '+@db+'.dbo.sp_executesql(''create view myV1 as select * from mytable'' ) '
print( @sql )
exec( @sql )

select @sb = '[myDb2]'
select @sql = 'exec '+@db+'.dbo.sp_executesql(''create view myV2 as select * from mytable'' ) '
print( @sql )
exec( @sql )
end
go

As to why I want to do this? Easy, I'm lazy when it some to creating several zillion objects by hand. I create one sp-* to do hold the code and have it create _IDENTICAL_ code in every database I wnat it to be in. I make one change run the sp_ and viola all the procedures are updated. I also use it to do the _endless_ amount of grants etc I need as well.

Works for functions, stored procedures, bulkloads, etc.

Nuff said. Enjoy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 03:24:20

In any case, read this fully
www,sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

glinoff
Starting Member

3 Posts

Posted - 2009-01-25 : 17:37:37

I have found this thread very useful, and especially the reference to http://www.sommarskog.se/dynamic_sql.html.

However, when I try to implement the solutions, I have one problem. I can create views in a procedure using sp_executesql without paramters With parameters, I get a syntax error at the "view" statement.
.
Does anyone know why using parameters would make a difference?

--gordon

heze
Posting Yak Master

192 Posts

Posted - 2009-01-25 : 23:28:21

It shouldn't make a difference, please post your code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:38:51

whats the purpose of creating view within procedure in first place?

glinoff
Starting Member

3 Posts

Posted - 2009-01-26 : 09:42:25

This is a reply to visakh16, because this issue seems to come up repeatedly. Currently, the code that I'm using is rather complicated. I will try to simplify it and then post it here.

I use SQL extensively for querying databases for analytic purposes. In general, I tend to avoid anything that is not reasonably transportable between databases or that interfere with optimization -- for example, stored procedures, and cursors.

However, now I am trying to make some SQL code more generic, so I have to succumb to temptations. I have some code that performs a particular task; this task requires multiple queries and multiple temporary tables. The generic code still needs to interface to data tables. I want this interface to be as localized as possible so I can easily change it (or change it using parameters); hence the desire for views.

For instance, I want to specify one of several columns as a dimension from the original data. A view makes this easy:

CREATE VIEW dimview AS
SELECT column1 as dim
FROM usertable
;

The rest of my code can then use the column "dim1", as it performs merrily along. Then, if I want to change the column being used, I just drop the view and define it again with another column. Same thing if I want to change data tables or databases. Note that the names of the columns themselves depend on the underlying data tables, so I cannot even use a case statement for specifying the dimensions. The only method that I've found for solving this is to create a view within a stored procedure. Then, that procedure and other procedures can access the data in the way I need it to be accessed.

--gordon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 10:06:23

why cant you use derived tables instead?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources