0

So I made a procedure that makes a dynamic view using dynamic SQL, but I used two sql executions: One for the if clause and one for the else, it should be possible to put it all under one SQL string or am i wrong? Because I tried it and get an error over and over again. Im not the best in writing dynamic sql, so it is probably my mistake or cant it be done and im losing my time on trying to do this?

create procedure test_view
  (@table_name varchar(30))
as
BEGIN
declare@ sqlQuery varchar(100)

if exists(select 1 from sp_iqview('v_anon_' + @table_name) where view_name = 'v_anon_' + @table_name)
begin
set@ sqlQuery = ('drop view ' + 'v_anon_' + @table_name)
EXECUTE(@sqlQuery)
end

else
  begin
set@ sqlQuery = ('CREATE VIEW ' + 'v_anon_' + @table_name + ' AS SeLECT * FROM ' + @table_name)
EXECUTE(@sqlQuery)
select@ sqlQuery
end
END
frlan
  • 6,950
  • 3
  • 31
  • 72
theweeknd
  • 277
  • 1
  • 12
  • Maybe to add something about the procedure, she makes a view of any table that you have in your database if executed. – theweeknd Oct 13 '14 at 11:39

2 Answers2

1

try this query.... Here else statement is not required.... if the object exists, it will drop in the first step itself. If not, it create new one... 

create procedure test_view
  (@table_name varchar(30))
as
BEGIN
declare @DropQuery varchar(100)
declare @CreateQuery varchar(100)

IF EXISTS(select 1 from sp_iqview('v_anon_' + @table_name) where view_name = 'v_anon_'  + @table_name)
BEGIN
SET @DropQuery= 'drop view v_anon_' + @table_name
EXEC sp_executesql @DropQuery
END



SET @CreateQuery = 'CREATE VIEW  v_anon_' + @table_name + ' AS SeLECT * FROM ' + @table_name
EXEC sp_executesql @CreateQuery 
SELECT @CreateQuery 

END
pyborg
  • 166
  • 10
  • Yep, that also does exactly what i need, thx m8.... it's funny how you cant see the simplest things and they make such a big difference :/ – theweeknd Oct 14 '14 at 07:51
0

You would need a go between the two statements, but dynamic SQL doesn't support the GO keyword as it's not valid T-SQL. You would need to execution them separately...

However you could add go and then go with a solution proposed here I suppose...

Execute Dynamic Query with go in sql

Community
  • 1
  • 1
dandcg
  • 442
  • 4
  • 16
  • thx this is also usefull to know, but you dont need to separate if exists and else with GO, Venkat G has given the solution to what i needet, because i just wanted that the sql executes if exist, drop, create one after another, everytime, and i thought i needet to put all that in one string (the if exist, drop, create), but it wasnt necessary. – theweeknd Oct 14 '14 at 09:26
  • If exists no, but drop and create yes... The question specified 'it should be possible to put it all under one SQL string'... So not sure how the first answer resolves your original question. But glad you got it working... – dandcg Oct 14 '14 at 09:44