T-SQL Procedure to create objects dynamically

Hi,
I am using a procedure  below to create a dyncamic view. The tablenames will change over time  and need to be dynamic. I'm receiving an error below.

Msg 102, Level 15, State 1, Procedure create_view, Line 1
Incorrect syntax near ')'.

create proc create_view()
as
begin
declare @sql nvarchar(4000)
select @sql = 'exec dbname1'+ '.dbo.sp_executesql(''create view xyz as SELECT * from table1 Union SELECT * from table2'' )'
print( @sql )
exec( @sql )
end
crazywolf2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

waltersnowslinarnoldCommented:
You cant create a VIEW inside a Stored procedure.. Few DDL are not possible in Stored Procedure..

Please refer below URL for more details:
http://msdn.microsoft.com/en-us/library/ms187926.aspx
0
crazywolf2010Author Commented:
Hi,
Can you please suggest me another way to do this?
0
waltersnowslinarnoldCommented:
The way the Stored procedure samples is illustrated above seems, there is no need for a stored procedure rather. You can directly go ahead create a VIEW by yourself using the Tables you like.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

crazywolf2010Author Commented:
Hi,
Code below worked for me. So one can create a view inside procedure. I am now trying to work out passing tablenames as parameter.

alter proc test_proc as
begin
   exec('create view test_view as SELECT * from table1 Union ALL SELECT * from table2')
end
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
waltersnowslinarnoldCommented:
i still don't understand, why do you go for a Stored procedure for creating a VIEW, which is not a dynamic query or View rather.

Though the above View was created using Stored Procedure.You cant execute the same Stored procedure again, which eventually throws an error.
0
crazywolf2010Author Commented:
Hi,
I did say the tablenames will be dynamic  in view defn earlier. I need to put that in place.

I ran this procedure around 10 times and it all works fine on SQL 2008R2.
0
Scott PletcherSenior DBACommented:
Not sure what walter is talking about.

The first error is because of this:

create proc create_view()

You can't have the ( ) unless you also declare parameters.  So do this instead:


create proc create_view
as
...
0
Scott PletcherSenior DBACommented:
You can certainly use *dynamic* SQL (as in your example) to create a view from within a proc.

Yes, if the view already exists you will get an error.

If that is a possibility, add an EXISTS() check in a separate statement before the one that creates the view and condition execution of the view based on the results returned by the EXISTS() check. [If you need guidance on setting that up, just let me know.]
0
crazywolf2010Author Commented:
Thanks ScottPletcher. I just did that.

Do you have a snippet to accept tablenames (n tables) as a input variable and replace them in create view stmt?

Thanks
0
Scott PletcherSenior DBACommented:
Not 100% sure what you mean.

You mean to be the source table(s) for the view?

I usually use a template-style approach, then substitute in values from the params.

For example:

DECLARE @sql varchar(8000)
SET @sql_template = 'exec dbname1'+ '.dbo.sp_executesql(''create view xyz as SELECT * from $table1$ Union SELECT * from $table2$'')'


SET @sql = REPLACE(REPLACE(@sql,
    '$table1$', COALESCE(@param_table1, 'table1')),
    '$table2$', COALESCE(@param_table2, 'table2'))

EXEC(@sql)
0
crazywolf2010Author Commented:
I tried above code and compiled ok.
Unfortunately when I tried executing it, nothing is happening.
How can I catch and print errors as well as variable values?
Is there an easy way to debug the procedure under SSMS?

Thanks
0
Scott PletcherSenior DBACommented:
First easy thing to do is print the statement before it runs.

Then you can run it stand-alone to see the error.

You can add TRY ... CATCH if needed to capture and display the actual error.


DECLARE @sql varchar(8000)
SET @sql_template = 'exec dbname1'+ '.dbo.sp_executesql(''create view xyz as SELECT * from $table1$ Union SELECT * from $table2$'')'


SET @sql = REPLACE(REPLACE(@sql,
    '$table1$', COALESCE(@param_table1, 'table1')),
    '$table2$', COALESCE(@param_table2, 'table2'))

PRINT @sql

EXEC(@sql)
0
crazywolf2010Author Commented:
Answered Correctly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.