How to create a view dynamically thru a procedure

DECLARE @sql varchar(max), @Database1 varchar(300)

set @Database1 = 'tempdb'

SET @sql='

USE '+@Database1+';

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''test_view'')

BEGIN

DROP VIEW test_view

PRINT ''VIEW EXISTS''

END'

PRINT @sql

--EXEC(@sql)

SET @sql=''

SET @sql='USE '+@Database1+'

;

CREATE VIEW test_view

AS

BEGIN

SELECT TOP 50 * FROM TEST_TABLE

'

PRINT @sql

--EXEC(@SQL)

This code wiil work for you.

Now home work for you, find what i have changed in you code 😛

This is how I do this:

DECLARE @sp_executesql NVARCHAR(150) = QUOTENAME('name of your database goes here') + '.sys.sp_executesql' ;

I put the statement to create the view in @sql of type NVARCHAR(MAX).

To execute this statement on the 'other' database, run

EXEC @sp_executesql @sql ;

Hi,

I tried to using the suggestion we have in this thread. But when executing it i am getting the 2 errors,

1. Msg 156, Level 15, State 1, Line 4 - Incorrect syntax near the keyword 'VIEW'.

2. Msg 102, Level 15, State 1, Line 21 - Incorrect syntax near 'END'.

Below is the query, Please advise.

declare @query nvarchar(max);

SET @query='USE <DatabaseName>;

IF NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''PrimaryKeyDetails'')

BEGIN

CREATE VIEW PrimaryKeyDetails AS

BEGIN

SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)), CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)),

PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)), PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE, 

REFERENCE_TABLE_NAME='', REFERENCE_COL_NAME=''

FROM sys.key_constraints as PKnUKEY

INNER JOIN sys.tables as PKnUTable

ON PKnUTable.object_id = PKnUKEY.parent_object_id

INNER JOIN sys.index_columns as PKnUColIdx

ON PKnUColIdx.object_id = PKnUTable.object_id

AND PKnUColIdx.index_id = PKnUKEY.unique_index_id

INNER JOIN sys.columns as PKnUKEYCol

ON PKnUKEYCol.object_id = PKnUTable.object_id

AND PKnUKEYCol.column_id = PKnUColIdx.column_id

INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl

ON oParentColDtl.TABLE_NAME=PKnUTable.name

AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name

END

';

print @query;

EXECUTE sp_executesql @query;

Cheers,

Immi

You have this great debugging line right there in your code. It looks like this: print @query;

Can you share those results with us?

Hi Sean,

yes you are right, the debug line print @query was the was of the 2nd issue i reported. On removing it, thats no longer an issue. But the 1st issue is still persisting. Please find attached the screenshot of the error for the modified code.

declare @query nvarchar(max)

SET @query='USE <DatabaseName>;

IF NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''PrimaryKeyDetails'')

BEGIN

CREATE VIEW PrimaryKeyDetails AS

BEGIN

SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)), CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)),

PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)), PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE, 

REFERENCE_TABLE_NAME='', REFERENCE_COL_NAME=''

FROM sys.key_constraints as PKnUKEY

INNER JOIN sys.tables as PKnUTable

ON PKnUTable.object_id = PKnUKEY.parent_object_id

INNER JOIN sys.index_columns as PKnUColIdx

ON PKnUColIdx.object_id = PKnUTable.object_id

AND PKnUColIdx.index_id = PKnUKEY.unique_index_id

INNER JOIN sys.columns as PKnUKEYCol

ON PKnUKEYCol.object_id = PKnUTable.object_id

AND PKnUKEYCol.column_id = PKnUColIdx.column_id

INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl

ON oParentColDtl.TABLE_NAME=PKnUTable.name

AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name

END

';

EXECUTE sp_executesql @query;

Cheers,

Immi

Why are you using a BEGIN with your view? Views are a single statement and they can't include a BEGIN...END block.

And by the way, the CREATE VIEW must be the first statement in a query batch. The IF and any other things that you might want to include must be in a separate batch.

As dynamic code doesn't allow GO as a batch separator, you need to either nest your dynamic code or keep it as static SQL.