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?
 
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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.