April 3, 2018 at 10:16 am
All,
I am trying to work on a stored proc that would accept DB as a parameter name and then work its way down creating views and triggers etc. However, the point where I am stuck is I couldn't make it switch to use a different database name inside the stored proc against which the views and triggers needs to be created. Since, I'll be passing the DB name it would be unknown and so I cannot hard code it. I gave it a lot of try but didn't succeed...not sure where am I going wrong.
USE [AdvWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_dbo_create_views_triggers]
(@Database_Name NVARCHAR(MAX) = '')
AS
BEGIN
DECLARE @Db_Name sysname = DB_NAME()
SELECT @Db_Name = @Database_Name
IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = 'abc')
BEGIN
EXEC ('USE [' + @Db_Name + '];')
EXEC ('CREATE SCHEMA abc')
END
IF NOT EXISTS (SELECT name FROM sys.views WHERE name = 'xyz')
BEGIN
EXEC ('USE [' + @Db_Name + '];')
EXEC ('CREATE VIEW [dbo].[xyz]
AS
...;')
PRINT 'dbo.xyz view created'
END
ELSE PRINT 'dbo.xyz view exists'
..
..
..
END
April 3, 2018 at 10:28 am
You need to make the entire set of code dynamic, as having your USE statements separately executed makes them useless. Alternatively, you could instead use sp_MSForEachDb, which is undocumented by Microsoft, but there's plenty of information on the web that searching can easily find. Each EXEC (@SQL) has it's own execution context, so keep that in mind.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2018 at 10:43 am
If you decide to stick with the current methodology, here's the how:--USE [AdvWorks]
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[usp_dbo_create_views_triggers] (
@Database_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL AS nvarchar(max);
SELECT @Database_Name = ISNULL(@Database_Name, DB_NAME());
SELECT @SQL = N'
USE ' + @Database_Name + N';
IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = ''abc'')
BEGIN
CREATE SCHEMA abc;
END;
IF NOT EXISTS (SELECT name FROM sys.views WHERE name = ''xyz'')
BEGIN
EXEC (N''
CREATE VIEW dbo.xyz
AS
...'');
PRINT dbo.xyz view created;
END;
ELSE
BEGIN
PRINT dbo.xyz view exists;
END;';
EXEC (@SQL);
END;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2018 at 11:29 am
sgmunson - Tuesday, April 3, 2018 10:43 AMIf you decide to stick with the current methodology, here's the how:--USE [AdvWorks]
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[usp_dbo_create_views_triggers] (
@Database_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL AS nvarchar(max);SELECT @Database_Name = ISNULL(@Database_Name, DB_NAME());
SELECT @SQL = N'
USE ' + @Database_Name + N';IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = ''abc'')
BEGIN
CREATE SCHEMA abc;
END;IF NOT EXISTS (SELECT name FROM sys.views WHERE name = ''xyz'')
BEGIN
EXEC (N''
CREATE VIEW dbo.xyz
AS
...'');PRINT dbo.xyz view created;
END;
ELSE
BEGIN
PRINT dbo.xyz view exists;
END;';EXEC (@SQL);
END;
GO
I am getting a whole bunch of syntactical errors with your code.
April 3, 2018 at 11:49 am
ffarouqi - Tuesday, April 3, 2018 11:29 AMsgmunson - Tuesday, April 3, 2018 10:43 AMIf you decide to stick with the current methodology, here's the how:--USE [AdvWorks]
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[usp_dbo_create_views_triggers] (
@Database_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL AS nvarchar(max);SELECT @Database_Name = ISNULL(@Database_Name, DB_NAME());
SELECT @SQL = N'
USE ' + @Database_Name + N';IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = ''abc'')
BEGIN
CREATE SCHEMA abc;
END;IF NOT EXISTS (SELECT name FROM sys.views WHERE name = ''xyz'')
BEGIN
EXEC (N''
CREATE VIEW dbo.xyz
AS
...'');PRINT dbo.xyz view created;
END;
ELSE
BEGIN
PRINT dbo.xyz view exists;
END;';EXEC (@SQL);
END;
GOI am getting a whole bunch of syntactical errors with your code.
Would help if you posted the full error message(s) you are getting. Just saying you are getting errors tell us nothing. Also, you may want to show the code you are trying to run.
April 3, 2018 at 12:10 pm
ffarouqi - Tuesday, April 3, 2018 11:29 AMsgmunson - Tuesday, April 3, 2018 10:43 AMIf you decide to stick with the current methodology, here's the how:--USE [AdvWorks]
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[usp_dbo_create_views_triggers] (
@Database_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL AS nvarchar(max);SELECT @Database_Name = ISNULL(@Database_Name, DB_NAME());
SELECT @SQL = N'
USE ' + @Database_Name + N';IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = ''abc'')
BEGIN
CREATE SCHEMA abc;
END;IF NOT EXISTS (SELECT name FROM sys.views WHERE name = ''xyz'')
BEGIN
EXEC (N''
CREATE VIEW dbo.xyz
AS
...'');PRINT dbo.xyz view created;
END;
ELSE
BEGIN
PRINT dbo.xyz view exists;
END;';EXEC (@SQL);
END;
GOI am getting a whole bunch of syntactical errors with your code.
Did you forget to add all the code you would want in the view? And if you did not forget, it may be that some of that code had a syntax error, so please post exactly what you ran, as Lynn suggested....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 4, 2018 at 1:27 pm
Give this version a shot, I edited Steve's a bit, needed quotes around the "print" arguments.
edit: fixed schema section, restored Steve's original tests for the conditionals.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[usp_dbo_create_views_triggers] (
@Database_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql AS nvarchar(max);
SELECT @Database_Name = ISNULL(@Database_Name, DB_NAME());
SELECT @sql = N'
USE ' + @Database_Name + N';
IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = ''abc'')
BEGIN
EXEC (N''
CREATE schema abc'')
PRINT ''schema abc created'';
END;
ELSE
BEGIN
PRINT ''schema abc exists'';
END;
IF NOT EXISTS (SELECT name FROM sys.views WHERE name = ''xyz'')
BEGIN
EXEC (N''
CREATE VIEW dbo.xyz
AS
SELECT 1 AS SAMPLECOLUMN'');
PRINT ''dbo.xyz view created'';
END;
ELSE
BEGIN
PRINT ''dbo.xyz view exists'';
END;';
----uncomment for troubleshooting
--print @sql;
EXEC (@SQL);
END;
GO
April 4, 2018 at 1:38 pm
If you're on an installed version of SQL, rather than SQL Azure, just create the proc in the master db, mark it as special, and then it will run in the context of any db you want, without having to use dynamic SQL at all.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2018 at 1:45 pm
ScottPletcher - Wednesday, April 4, 2018 1:38 PMIf you're on an installed version of SQL, rather than SQL Azure, just create the proc in the master db, mark it as special, and then it will run in the context of any db you want, without having to use dynamic SQL at all.
Looks like you can mark this "special" with t-sql. for a procedure saved in master called "sp_MyTest":
USE Master
GO
EXEC sp_MS_MarkSystemObject sp_MyTest
GO
April 4, 2018 at 8:58 pm
patrickmcginnis59 10839 - Wednesday, April 4, 2018 1:45 PMScottPletcher - Wednesday, April 4, 2018 1:38 PMIf you're on an installed version of SQL, rather than SQL Azure, just create the proc in the master db, mark it as special, and then it will run in the context of any db you want, without having to use dynamic SQL at all.Looks like you can mark this "special" with t-sql. for a procedure saved in master called "sp_MyTest":
USE Master
GO
EXEC sp_MS_MarkSystemObject sp_MyTest
GONice tip Scott I didn't know about making procedures like that!Also this forum software blows chunks.
"run in the context of any db" meand this:
EXEC Any_DB.sp_MyTest
_____________
Code for TallyGenerator
April 5, 2018 at 8:09 am
Sergiy - Wednesday, April 4, 2018 8:58 PMpatrickmcginnis59 10839 - Wednesday, April 4, 2018 1:45 PMScottPletcher - Wednesday, April 4, 2018 1:38 PMIf you're on an installed version of SQL, rather than SQL Azure, just create the proc in the master db, mark it as special, and then it will run in the context of any db you want, without having to use dynamic SQL at all.Looks like you can mark this "special" with t-sql. for a procedure saved in master called "sp_MyTest":
USE Master
GO
EXEC sp_MS_MarkSystemObject sp_MyTest
GONice tip Scott I didn't know about making procedures like that!Also this forum software blows chunks."run in the context of any db" meand this:
EXEC Any_DB.sp_MyTest
Actually it's
EXEC db_name.dbo.sp_YourProcName <parameter(s)>
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply