please help !! Dynamic SQL

Question

please help !! Dynamic SQL

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Lynn Pettis - Wednesday, August 8, 2018 8:23 AM
Try this, uncomment the EXEC statement.
DECLARE @SQLTemplate NVARCHAR(MAX)         , @SQLCmd NVARCHAR(MAX)         , @SQLParm NVARCHAR(MAX) = N'@iViewDef NVARCHAR(MAX)'         , @ViewName NVARCHAR(256)         , @ViewDef NVARCHAR(MAX)         , @DBName NVARCHAR(256);
SET @DBName = QUOTENAME(N'Test');
SET @SQLTemplate = N'
USE !DBName!;
IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
exec [sys].[executesql] @iViewDef
';
DECLARE [ViewDef] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([v].[object_id])) + '.' + QUOTENAME([v].[name]) AS [Viewname]
, [sm].[definition]                   AS [ViewDefinition]
FROM
[sys].[views]                  AS [v]
INNER JOIN [sys].[sql_modules] AS [sm]
    ON [sm].[object_id] = [v].[object_id]
ORDER BY
[v].[name];
OPEN [ViewDef];
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [ViewDef]
INTO @ViewName, @ViewDef;
IF @@FETCH_STATUS <> 0 BREAK; -- Exit loop on error, should enhance and use try catch
SELECT @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLTemplate,'!ViewName!',@ViewName),'!ViewDef!',@ViewDef),'!DBName!',@DBName)
PRINT @SQLCmd;
--EXEC [sys].[sp_executesql] @SQLCmd, @SQLParm, @iViewDef = @ViewDef;
END
CLOSE [ViewDef];
DEALLOCATE [ViewDef];
GO

Sorry to nitpick, but -

You need "exec [sys].[sp_executesql] @iViewDef" instead of "exec [sys].[executesql] @iViewDef".

Other than that it's great! Hope the OP likes it...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

laurie-789651 - Wednesday, August 8, 2018 9:03 AM
Lynn Pettis - Wednesday, August 8, 2018 8:23 AM
Try this, uncomment the EXEC statement.
DECLARE @SQLTemplate NVARCHAR(MAX)         , @SQLCmd NVARCHAR(MAX)         , @SQLParm NVARCHAR(MAX) = N'@iViewDef NVARCHAR(MAX)'         , @ViewName NVARCHAR(256)         , @ViewDef NVARCHAR(MAX)         , @DBName NVARCHAR(256);
SET @DBName = QUOTENAME(N'Test');
SET @SQLTemplate = N'
USE !DBName!;
IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
exec [sys].[executesql] @iViewDef
';
DECLARE [ViewDef] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([v].[object_id])) + '.' + QUOTENAME([v].[name]) AS [Viewname]
, [sm].[definition]                   AS [ViewDefinition]
FROM
[sys].[views]                  AS [v]
INNER JOIN [sys].[sql_modules] AS [sm]
    ON [sm].[object_id] = [v].[object_id]
ORDER BY
[v].[name];
OPEN [ViewDef];
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [ViewDef]
INTO @ViewName, @ViewDef;
IF @@FETCH_STATUS <> 0 BREAK; -- Exit loop on error, should enhance and use try catch
SELECT @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLTemplate,'!ViewName!',@ViewName),'!ViewDef!',@ViewDef),'!DBName!',@DBName)
PRINT @SQLCmd;
--EXEC [sys].[sp_executesql] @SQLCmd, @SQLParm, @iViewDef = @ViewDef;
END
CLOSE [ViewDef];
DEALLOCATE [ViewDef];
GO
Sorry to nitpick, but -
You need "exec [sys].[sp_executesql] @iViewDef" instead of "exec [sys].[executesql] @iViewDef".
Other than that it's great! Hope the OP likes it...

Already fixed that.

Joe Torre SSChampion Points: 10248 More actions · Answer 3

Lidou123 - Tuesday, August 7, 2018 9:47 AM
laurie-789651 - Tuesday, August 7, 2018 8:45 AM
Are you still trying to do this?
What is the purpose? Do you want to move views from one database to another?
Jeff Moden - Tuesday, August 7, 2018 9:07 AM
Lidou123 - Tuesday, August 7, 2018 8:55 AM
laurie-789651 - Tuesday, August 7, 2018 8:45 AM
Are you still trying to do this?
What is the purpose? Do you want to move views from one database to another?
Hello.
Yes. Still doing it.
Do u have something to help me ?
If you're trying to move the whole database, why not just do a restore of the existing database?
Hello
Because I don't want to do the tasks manually. I just want to duplicate the views and the procedures.

Why not script it out in SSMS?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Joe Torre - Thursday, August 9, 2018 6:09 PM
Lidou123 - Tuesday, August 7, 2018 9:47 AM
laurie-789651 - Tuesday, August 7, 2018 8:45 AM
Are you still trying to do this?
What is the purpose? Do you want to move views from one database to another?
Jeff Moden - Tuesday, August 7, 2018 9:07 AM
Lidou123 - Tuesday, August 7, 2018 8:55 AM
laurie-789651 - Tuesday, August 7, 2018 8:45 AM
Are you still trying to do this?
What is the purpose? Do you want to move views from one database to another?
Hello.
Yes. Still doing it.
Do u have something to help me ?
If you're trying to move the whole database, why not just do a restore of the existing database?
Hello
Because I don't want to do the tasks manually. I just want to duplicate the views and the procedures.
Why not script it out in SSMS?

Because that would be a manual process? Just guessing.