I make one change run the sp_ and viola all the procedures are updated.
TABLEPLUS SHOW STORED PROCEDURE CODE
I create one sp-* to do hold the code and have it create _IDENTICAL_ code in every database I wnat it to be in. Yes, you can.create proc someProc ()asbegindeclare nvarchar(4000)declare sysnameselect = ''select = 'exec view myV1 as select * from mytable'' ) 'print( )exec( )select = ''select = 'exec view myV2 as select * from mytable'' ) 'print( )exec( )endgoAs to why I want to do this? Easy, I'm lazy when it some to creating several zillion objects by hand. 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 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.
The purpose of a stored proc is to encapsulate reusable code. Sorry, but this kind of maintenance task should not reside in a stored proc. Ssh:thank you theEXEC myDb1.sp_executesql N'CREATE VIEW myView1 AS. 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 myPermanentObjectcreate 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 scriptthank you 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 ?Īnother 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. 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 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? dbo.dimDates')-Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'go'.Msg 111, Level 15, State 1, Line 1'CREATE VIEW' must be the first statement in a query batch.-thank you Spirit, I get the following message when using:exec(' use go create view dimDates_1 as select * from. Ha,spirit,thats what ive been trying to do butwhen 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 viewsthank youĮxec(' use yourDBName go create view. dbo.myv1as.')But why you want to create view inside a stored proc?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" Prefix view with db name.Exec('Create View.
You can use dynamic sql and Exec()_Causing trouble since 1980blog:
TABLEPLUS SHOW STORED PROCEDURE PLUS
Hi, Im am wandering if it is possible to create two views in two different tables from within the same stored proc:excreate proc myProcasuse gocreate view myV1asselect * from mytablegouse gocreate view myV2asselect * from mytablegogo-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 " restriction, is there a way to solve this problem?thank you We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with.
0 kommentar(er)
