November 19, 2014 at 9:48 pm
Hello All,
I am working on creating multiple views dynamically and since the batch separate 'GO' does not work, I enter all of the create statements with GO in between the create statements and I replace all 'GO' with EXEC. There is only one problem, it also replaces lowercase 'go' in a table named Categories. Below is the code to replace the all of the GO words in the SQL string. I am hoping to figure out a way to replace only Upper Case 'GO' and not the lower case 'go' in a string.
DECLARE
@TableSuffix VARCHAR(10)= 'New'
,@TableIdVARCHAR(10)= '2'
,@SQLNVARCHAR(MAX)= ''
,@DebugBIT= 1
SET @SQL ='
DROP VIEW schema.TableName_' + @TableSuffix + ';
GO
CREATE VIEW schema.TableName_' + @TableSuffix + '
AS
SELECT
*
FROM
schema.TableName
WHERE
TableId = ' + @TableId + ';
GO
GRANT SELECT ON schema.TableName_' + @TableSuffix + ' TO ' + @TableSuffix + '
GO
DROP VIEW schema.TableName2_' + @TableSuffix + ';
GO
CREATE VIEW schema.TableNamego_' + @TableSuffix + '
AS
SELECT
*
FROM
schema.TableNamego
WHERE
TableId = ' + @TableId + ';
GO
GRANT SELECT ON schema.TableNamego_' + @TableSuffix + ' TO ' + @TableSuffix + ';
GO '
SET @SQL = 'EXEC (''' + REPLACE(REPLACE(@SQL, '''', ''''''), 'GO', ''');' + + CHAR(13) + ' EXEC(''') + ''');'
IF @Debug = 1
BEGIN
PRINT SUBSTRING(@SQL,1,4000)
PRINT SUBSTRING(@SQL,4001,4000)
END
ELSE
EXECUTE sp_executesql @stmt = @SQL;+ ''');'
Cheers!
Tim
The pain of Discipline is far better than the pain of Regret!
November 20, 2014 at 4:02 am
The best thing to do is to write the code with exec instead of GO, but you can also modify your replace statement so it will replace ENTER + 'GO' instead of replacing just GO. Instead of this line:
SET @SQL = 'EXEC (''' + REPLACE(REPLACE(@SQL, '''', ''''''), 'GO', ''');' + + CHAR(13) + ' EXEC(''') + ''');'
Use this line:
SET @SQL = 'EXEC (''' + REPLACE(REPLACE(@SQL, '''', ''''''), '
GO', ''');' + + CHAR(13) + ' EXEC(''') + ''');'
Of course if you did start a line with the letter go, it won't work.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 20, 2014 at 9:30 am
Hello Adi,
This worked great! This does wok for my solution. But now curiosity has the best of me and I truly wonder how to use the replace function and replace only UPPER CASE letters.
Thanks for you help.
Tim
The pain of Discipline is far better than the pain of Regret!
November 20, 2014 at 9:49 am
You can try a case sensitive collation.
SET @SQL = 'EXEC (''' + REPLACE(REPLACE(@SQL COLLATE Latin1_General_BIN, '''', ''''''), 'GO', ''');' + + CHAR(13) + ' EXEC(''') + ''');'
November 20, 2014 at 10:16 am
Luis,
Thanks! I was trying to figure out how to use COLLATE Latin1_General_BIN in the function, but I was unable. Thank again and it tests out fine!
EXEC ('
DROP VIEW schema.TableName_New;
');
EXEC('
CREATE VIEW schema.TableName_New
AS
SELECT
*
FROM
schema.TableName
WHERE
TableId = 2;
');
EXEC('
GRANT SELECT ON schema.TableName_New TO New
');
EXEC('
DROP VIEW schema.TableName2_New;
');
EXEC('
CREATE VIEW schema.TableNamego_New
AS
SELECT
*
FROM
schema.TableNamego
WHERE
TableId = 2;
');
EXEC('
GRANT SELECT ON schema.TableNamego_New TO New;
');
EXEC(' ');
Tim
The pain of Discipline is far better than the pain of Regret!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply