Link to home
Start Free TrialLog in
Avatar of VitaminD
VitaminDFlag 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]
Avatar of brad2575
brad2575
Flag of United States of America image

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
Avatar of nishant joshi
nishant joshi
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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