Avatar of VitaminD
VitaminD
Flag for United States of America asked on

Dynamic SQL

I want to be able to run this sproc with any Database Name
The example on the bottom works when I run it ad hoc
I am not proficient with dynamic sql syntax
How would I write the dynamic SQL part so it works correctly
Any help will be greatly appreciated
Thanks In Advance


/****** Object:  StoredProcedure [dbo].[usp_enDBAReport]    Script Date: 07/03/2012 11:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




--exec [dbo].[usp_enDBAReport]NULL,NULL,0
CREATE       PROCEDURE [dbo].[usp_GetUserPermissions]
             (      
              @DatabaseName NVARCHAR(100)
          )

AS
BEGIN

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
      SET NOCOUNT ON


--IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects
--WHERE id = OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]'))
--DROP TABLE [tempdb].[dbo].[SQL_DB_REP]  
--GO

CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]
(
[Server] [varchar](100) NOT NULL,
[DB_Name] [varchar](70) NOT NULL,
[User_Name] [nvarchar](90) NULL,
[Group_Name] [varchar](100) NULL,
[Account_Type] [varchar](22) NULL,
[Login_Name] [varchar](80) NULL,
[Def_DB] [varchar](100) NULL
)
ON [PRIMARY]
INSERT INTO [tempdb].[dbo].[SQL_DB_REP]
Exec sp_MSForEachDB 'SELECT CONVERT(varchar(100),
SERVERPROPERTY(''Servername'')) AS Server,
''' + @DatabaseName + ''' as DB_Name,
usu.name u_name
,CASE
WHEN (usg.uid is null) then ''public''
ELSE usg.name
END as Group_Name
,CASE
WHEN usu.isntuser=1 then ''Windows Domain Account''
WHEN usu.isntgroup = 1 then ''Windows Group''
WHEN usu.issqluser = 1 then ''SQL Account''
WHEN usu.issqlrole = 1 then ''SQL Role''
END as Account_Type
,lo.loginname
,lo.dbname as Def_DB
FROM
'''+ [@DatabaseName] + ''' ..sysusers usu LEFT OUTER JOIN
''' + ([@DatabaseName]+ ''' ..sysmembers mem INNER JOIN [db_DIDXDRone]..sysusers usg ON
mem.groupuid = usg.uid) ON usu.uid = mem.memberuid
LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid =
lo.sid
WHERE
(usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess =
1) and
(usg.issqlrole = 1 or usg.uid is null)'
SELECT [Server],
[DB_Name],
[User_Name],
[Group_Name],
[Account_Type],
[Login_Name],
[Def_DB]
FROM [tempdb].[dbo].[SQL_DB_REP]


END



--------------------------------

--
When I run this Manually It works fine
But only for the Database I specify




IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects
WHERE id =
OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]') )
DROP TABLE [tempdb].[dbo].[SQL_DB_REP] ; /*I intentionally left out the space */
GO
CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]
(
[Server] [varchar](100) NOT NULL,
[DB_Name] [varchar](70) NOT NULL,
[User_Name] [nvarchar](90) NULL,
[Group_Name] [varchar](100) NULL,
[Account_Type] [varchar](22) NULL,
[Login_Name] [varchar](80) NULL,
[Def_DB] [varchar](100) NULL
)
ON [PRIMARY]
INSERT INTO [tempdb].[dbo].[SQL_DB_REP]
Exec sp_MSForEachDB 'SELECT CONVERT(varchar(100),
SERVERPROPERTY(''Servername'')) AS Server,
''MyDatabaseName'' as DB_Name,
usu.name u_name
,CASE
WHEN (usg.uid is null) then ''public''
ELSE usg.name
END as Group_Name
,CASE
WHEN usu.isntuser=1 then ''Windows Domain Account''
WHEN usu.isntgroup = 1 then ''Windows Group''
WHEN usu.issqluser = 1 then ''SQL Account''
WHEN usu.issqlrole = 1 then ''SQL Role''
END as Account_Type
,lo.loginname
,lo.dbname as Def_DB
FROM
[MyDatabaseName]..sysusers usu LEFT OUTER JOIN
([MyDatabaseName]..sysmembers mem INNER JOIN [?]..sysusers usg ON
mem.groupuid = usg.uid) ON usu.uid = mem.memberuid
LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid =
lo.sid
WHERE
(usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess =
1) and
(usg.issqlrole = 1 or usg.uid is null)'
SELECT [Server],
[DB_Name],
[User_Name],
[Group_Name],
[Account_Type],
[Login_Name],
[Def_DB]
FROM [tempdb].[dbo].[SQL_DB_REP]
Microsoft SQL Server

Avatar of undefined
Last Comment
Alpesh Patel

8/22/2022 - Mon
brad2575

You have to create a string and then execute the string, simple example (yours is much larger but same concept):

DECLARE @SQL as varchar(MAX)

DECLARE @DatabaseName as varchar(50)

SET @DatabaseName = 'TestDatabaseName'

SET @SQL = 'Select * FROM ' + @DatabaseName + '.TableName WHERE X=1'

EXEC(@SQL)
ASKER CERTIFIED SOLUTION
nishant joshi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Alpesh Patel

If there is no dependencies them create SP on master database and in that SP pass Database name as parameter

the part of SP like bwlow:

@SQL = "Select * From " + @Database + "." + @Schema + ".TableName"
Exec (@SQL)

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23