How to create a view dynamically thru a procedure

Question

Post reply

How to create a view dynamically thru a procedure

pvsrpk

SSChasing Mays

Points: 602
More actions
February 11, 2010 at 12:37 pm

#145133

Hi Friends,
I need to create a view in another database dynamically from a procedure in "MAIN_DATABASE".
The following is my piece of code:
DECLARE @sql varchar(max)
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+'
GO
CREATE VIEW test_view
SELECT TOP 50 * FROM TEST_TABLE
'
PRINT @sql
EXEC(@SQL)
I am getting error "Incorrect Syntax near GO"
Any help appreciated.
Thanks in advance
pvsrpk
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

pvsrpk SSChasing Mays Points: 602 More actions · Answer 1

Hi this is the correct piece of code. I missed AS BEGIN in view definition.

DECLARE @sql varchar(max)

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+'

GO

CREATE VIEW test_view

AS

BEGIN

SELECT TOP 50 * FROM TEST_TABLE

'

PRINT @sql

EXEC(@SQL)

I am getting error "Incorrect Syntax near GO"

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

You can't use GO in a dynamic query like this.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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 😛

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

wschampheleer SSCertifiable Points: 5504 More actions · Answer 4

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 ;

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

immanuel2112 SSC Enthusiast Points: 128 More actions · Answer 5

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

immanuel2112 (10/7/2015)
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?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

immanuel2112 SSC Enthusiast Points: 128 More actions · Answer 7

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2