SQL Help!

[ddivita]

I need to take a stored procdure and convert it to a single SQL statement. First, here is the DDL for the database:

- EMS MS SQL Manager 1.7.0.1
-- ---------------------------------------
-- Host : ANSMA-QCS-1047
-- Database : UserSecurity


--
-- Structure for table Applications :
--

CREATE TABLE [Applications] (
[appid] int IDENTITY(1, 1) NOT NULL,
[appName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[appInquire] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[appSave] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[appDelete] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table Functions :
--

CREATE TABLE [Functions] (
[funid] int IDENTITY(1, 1) NOT NULL,
[appid] int NOT NULL,
[funName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[funDescription] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS,
[funInquire] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[funSave] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[funDelete] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table GroupMembers :
--

CREATE TABLE [GroupMembers] (
[gmbid] int IDENTITY(1, 1) NOT NULL,
[grpid] int NOT NULL,
[ulgid] int NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table Groups :
--

CREATE TABLE [Groups] (
[grpid] int IDENTITY(1, 1) NOT NULL,
[grpName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[grpDescription] varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS
)
ON [PRIMARY]
GO

--
-- Structure for table Menu :
--

CREATE TABLE [Menu] (
[mnuid] int IDENTITY(1, 1) NOT NULL,
[mnuGroup] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[mnuOrder] int DEFAULT (0) NOT NULL,
[mnuText] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('New Menu Item') NOT NULL,
[mnuTarget] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[mnuSubHead] bit DEFAULT (0) NOT NULL,
[mnuActive] bit DEFAULT (0) NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table SecuritybyGroup :
--

CREATE TABLE [SecuritybyGroup] (
[sgpid] int IDENTITY(1, 1) NOT NULL,
[grpid] int NOT NULL,
[funid] int NOT NULL,
[sgpInquire] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[sgpSave] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[sgpDelete] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table SecuritybyUser :
--

CREATE TABLE [SecuritybyUser] (
[susid] int IDENTITY(1, 1) NOT NULL,
[ulgid] int NOT NULL,
[funid] int NOT NULL,
[susInquire] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[susSave] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[susDelete] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table UserLogin :
--

CREATE TABLE [UserLogin] (
[ulgid] int IDENTITY(1, 1) NOT NULL,
[ulgLogin] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ulgName] varchar(70) COLLATE SQL_Latin1_General_CP1_CI_AS,
[ulgInquire] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[ulgSave] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[ulgDelete] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT (' ') NOT NULL,
[ulgAdmin] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('N') NOT NULL,
[ulgNTAuthorize] bit,
[ulgPassword] char(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
[ulgEmailAddress] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
[ulgPWLastDateChg] datetime,
[ulgRole] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS,
[ulgStatus] bit DEFAULT (1) NOT NULL
)
ON [PRIMARY]
GO

--
-- Structure for table UserRelationship :
--

CREATE TABLE [UserRelationship] (
[urrid] int IDENTITY(1, 1) NOT NULL,
[ulgid] int DEFAULT (0) NOT NULL,
[urrtype] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS,
[urrcode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
)
ON [PRIMARY]
GO


Here Is the SP
CREATE PROCEDURE sp_SecurityFunctionEffective
@AppName VARCHAR(50),
@FunName VARCHAR(50)
AS

Set NoCount On

DECLARE @UserID int, @UserLogin varchar(25), @UserName varchar(70)

Create Table #TempSFE (ulgid int, ulgLogin varchar(25), ulgName varchar(70), SecInquire char(1), SecSave char(1), SecDelete char(1))

DECLARE user_cursor CURSOR FOR
Select Cast(-1 as int) as ulgid, Cast('' as varchar(25)) as ulgLogin, Cast('DEFAULT' as varchar(70)) as ulgName
Union All Select ulgid, ulgLogin, ulgName from UserLogin
OPEN user_cursor

FETCH NEXT FROM user_cursor into @UserID, @UserLogin, @UserName

WHILE @@FETCH_STATUS = 0

BEGIN
--THIS ENTIRE SQL STATEMENT HAS BEEN COPIED FROM sp_SecuritySpecific
--THE ONLY EXCEPTION IS THE INSERT CLAUSE and FIRST 3 SELECT ITEMS, WHICH ARE ADDED HERE
--THEY SHOULD REMAIN IN SYNCH
Insert into #TempSFE
Select @UserID as ulgid, @UserLogin as ulgLogin, @UserName as ulgName,
Case When IsNull(susInquire, ' ') = ' ' and IsNull(sgpInquire, ' ') = ' ' and IsNull(funInquire, ' ') = ' ' then IsNull(appInquire, ' ')
When IsNull(susInquire, ' ') = ' ' and IsNull(sgpInquire, ' ') = ' ' and IsNull(funInquire, ' ') <> ' ' then IsNull(funInquire, ' ')
When IsNull(susInquire, ' ') = ' ' and IsNull(sgpInquire, ' ') <> ' ' then IsNull(sgpInquire, ' ') Else IsNull(susInquire, ' ') End
as SecInquire,
Case When IsNull(susSave, ' ') = ' ' and IsNull(sgpSave, ' ') = ' ' and IsNull(funSave, ' ') = ' ' then IsNull(appSave, ' ')
When IsNull(susSave, ' ') = ' ' and IsNull(sgpSave, ' ') = ' ' and IsNull(funSave, ' ') <> ' ' then IsNull(funSave, ' ')
When IsNull(susSave, ' ') = ' ' and IsNull(sgpSave, ' ') <> ' ' then IsNull(sgpSave, ' ') Else IsNull(susSave, ' ') End
as SecSave,
Case When IsNull(susDelete, ' ') = ' ' and IsNull(sgpDelete, ' ') = ' ' and IsNull(funDelete, ' ') = ' ' then IsNull(appDelete, ' ')
When IsNull(susDelete, ' ') = ' ' and IsNull(sgpDelete, ' ') = ' ' and IsNull(funDelete, ' ') <> ' ' then IsNull(funDelete, ' ')
When IsNull(susDelete, ' ') = ' ' and IsNull(sgpDelete, ' ') <> ' ' then IsNull(sgpDelete, ' ') Else IsNull(susDelete, ' ') End
as SecDelete
from Applications
Left Outer Join Functions on Functions.appid = Applications.appid
Left Outer Join (
Select funid,
Case When Min(Case When sgpInquire = 'N' Then 0 When sgpInquire = 'Y' Then 1 Else 2 End) = 0 Then 'N'
When Min(Case When sgpInquire = 'N' Then 0 When sgpInquire = 'Y' Then 1 Else 2 End) = 1 Then 'Y' Else ' ' End
as sgpInquire,
Case When Min(Case When sgpSave = 'N' Then 0 When sgpSave = 'Y' Then 1 Else 2 End) = 0 Then 'N'
When Min(Case When sgpSave = 'N' Then 0 When sgpSave = 'Y' Then 1 Else 2 End) = 1 Then 'Y' Else ' ' End
as sgpSave,
Case When Min(Case When sgpDelete = 'N' Then 0 When sgpDelete = 'Y' Then 1 Else 2 End) = 0 Then 'N'
When Min(Case When sgpDelete = 'N' Then 0 When sgpDelete = 'Y' Then 1 Else 2 End) = 1 Then 'Y' Else ' ' End
as sgpDelete
from SecuritybyGroup
Left Outer Join GroupMembers on SecuritybyGroup.grpid = GroupMembers.grpid
Left Outer Join UserLogin on GroupMembers.ulgid = UserLogin.ulgid
where ulgLogin = @UserLogin
group by funid)
SecbyGroup on SecbyGroup.funid = Functions.funid
Left Outer Join (
Select SecuritybyUser.*
from SecuritybyUser
Left Outer Join UserLogin on SecuritybyUser.ulgid = UserLogin.ulgid
where ulgLogin = @UserLogin)
SecbyUser on SecbyUser.funid = Functions.funid
Where appName = @AppName and funName = @FunName

FETCH NEXT FROM user_cursor into @UserID, @UserLogin, @UserName
END

CLOSE user_cursor

DEALLOCATE user_cursor

Select * from #TempSFE Order by ulgLogin

Drop Table #TempSFE


I did not write this and cannot make any changes to the structure of the database. Basically what this does is loops through all the Users and spits out what they have access to. It first looks at SecurityByGroup then securityByUser (Explicit rights). I need to write a single SQL statment that does not use a temp table and will perform the same process. I have been working on this all morning and my head is killing me. Any help would be great! Thanks

Daniel

[mikr0s]

Instead of creating a temporary table you could use a table variable

DECLARE @TempSFE TABLE (ulgid int, ulgLogin varchar(25), ulgName varchar(70), SecInquire char(1), SecSave char(1), SecDelete char(1))


HTH