Stored procedure...need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure...need help

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 10:34:53

hello

I have a stored procedure which performs advance search and which have dynamic sql.but in this stored procedure,I have to implement security .
I have a stored procedure for security of user also,but that is not a dynamic sql.

I just want to simple JOIN these 2 stored procedure.

This one is for advance search.

USE [charlotte]
GO
/****** Object: StoredProcedure [dbo].[sp_AdvanceSearch1] Script Date: 08/27/2008 10:16:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object: StoredProcedure [dbo].[sp_AdvanceSearch1] Script Date: 08/12/2008 09:33:00 ******/

ALTER PROCEDURE [dbo].[sp_AdvanceSearch1]
(
@Text VARCHAR(300),
@Text1 VARCHAR(300),
@Text2 VARCHAR(300),
@tblname VARCHAR(300)
)

AS

SET NOCOUNT ON

-- Show the results
--DECLARE @Params VARCHAR(1000)
Declare @SQL varchar(8000)
Declare @Element varchar(200)
declare @srh as nvarchar(50)
SET @srh = 'FORMSOF(INFLECTIONAL,'+@Text+')'
declare @srh1 as nvarchar(50)
SET @srh1 = 'FORMSOF(INFLECTIONAL,'+@Text1+')'
declare @srh2 as nvarchar(50)
SET @srh2 = 'FORMSOF(INFLECTIONAL,'+@Text2+')'

--Declare @Params varchar(200)

--SET @Params = QUOTENAME(@Text, '''') + 'AND' + QUOTENAME(@Text1, '''') + 'OR' + QUOTENAME(@Text2, '''')

Set @Element = case @tblname
When 'TextElement' then 'ElementText'
When 'VideoElement' then 'VideoMimeType'
When 'MetaTag' then 'MetatagText'
When 'Bookmark' then 'BookmarkText'
END -- Ends the CASE statement

set @SQl = 'Select s.SystemElementID, TOC.DocumentID,DocumentTitleText, SETitleText,'
+ @Element + ' as "SearchElement"'

SET @SQL = @SQL + 'FROM SystemElement s Inner Join ' +
@tblname + ' t On s.SystemElementID=t.SystemElementID Inner Join
TOCMap on s.SystemElementID=TOCMap.SystemElementID Inner Join
TableOfContents TOC on TOCMap.TableOfContentsID = TOC.TableOfContentsID Inner Join
MasterDocument m on TOC.DocumentID=m.DocumentID
WHERE FREETEXT(' + @Element + ',''' + @srh + ''') OR FREETEXT(' + @Element + ',''' + @srh1 + ''') OR FREETEXT(' + @Element + ',''' + @srh2 + ''') '

--Print (@SQL)
EXEC (@SQL)

And this one is for user security

SELECT DISTINCT a.[DocumentID],
a.[DocumentTitleText],
a.[CreatedUserName],
a.[CreatedDate],
a.[privilege],
a.[GroupName] FROM (
SELECT distinct MasterDocument.DocumentID,
MasterDocument.DocumentTitleText,
dbo.sf_getUsernameFromUserID(MasterDocument.CreatedByLID) CreatedUserName,
MasterDocument.CreatedDate,
[UserGroupRole].[Type] [privilege]
,[UserGroup].[GroupName]
FROM MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
WHERE [User].[UserName] = @userName
GROUP BY [MasterDocument].[DocumentID], MasterDocument.DocumentTitleText, MasterDocument.CreatedByLID, MasterDocument.CreatedDate, [UserGroupRole].[Type]
, [UserGroup].[GroupName]
)a

LEFT JOIN (

SELECT distinct MasterDocument.DocumentID,
MasterDocument.DocumentTitleText,
dbo.sf_getUsernameFromUserID(MasterDocument.CreatedByLID) CreatedUserName,
MasterDocument.CreatedDate,
[UserGroupRole].[Type] [privilege]
,[UserGroup].[GroupName]
FROM MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
WHERE [User].[UserName] = @userName
GROUP BY [MasterDocument].[DocumentID], MasterDocument.DocumentTitleText, MasterDocument.CreatedByLID, MasterDocument.CreatedDate, [UserGroupRole].[Type]
, [UserGroup].[GroupName]) b ON a.DocumentID = b.DocumentID AND dbo.sf_convertDocTypeToValue (a.privilege) < dbo.sf_convertDocTypeToValue(b.privilege) WHERE b.DocumentID IS null

) b ON a.documentid = b.documentid

END

Please help me how to join these two.

Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:41:54

what do you mean by join? is your intention to merge both the resultsets?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 10:47:54

well the thing is,my search gives result of all the documents.But now they want me to do search in such a way,that a perticular user will get result after search from public documents,and from his own documents.
I have the somebody else storedprocedure,which is to get user document by entering his username.so my intensions are to merge them two in order to get the result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:50:28

cant you make a UDF with second code part and call it from your dynamic stored procedure by passing username to mke usre you get users documents alone?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 10:58:25

Thats the good way to do,I know, but I dont know how to make UDF.can you help me in that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:09:48

quote:
Originally posted by bluestar

Thats the good way to do,I know, but I dont know how to make UDF.can you help me in that.

Yup. so how will you be using the second query? Can you explain how its resultset will be used in main SP?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 13:18:08

thanks for the reply.
well the thing is, if the user is not logged in that is if it is a guest User then it will get result from the search only from public document.
And if the user is logged in then it will get result from both public and its own document.

Well I have stored procedure which will do the following.
When user name is entered it will display all the documents of that user.

so i thought if I joined that query to my SP then it will work,but now again i have to have a logic to decide whether its a guest user or logged in user.

And I have no clue how to do that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:27:41

ok. Create a temporary table and put the security query result to it. Then use the table in your dynamic sql query to return only those documents.

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 14:02:36

ok but can you please tell me how shall I put this logic,,,,

Like checking 1st whether user is logged in or not.

If its not then search from only public docs

and if not then search from both public and its own private docs.

can you give me small example.

here is the query when username is entered, guest or some name,if will display their docs.

SELECT DISTINCT a.[DocumentID],
a.[DocumentTitleText],
a.[CreatedUserName],
a.[CreatedDate],
a.[privilege],
a.[GroupName] FROM (
SELECT distinct MasterDocument.DocumentID,
MasterDocument.DocumentTitleText,
dbo.sf_getUsernameFromUserID(MasterDocument.CreatedByLID) CreatedUserName,
MasterDocument.CreatedDate,
[UserGroupRole].[Type] [privilege]
,[UserGroup].[GroupName]
FROM MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
WHERE [User].[UserName] = @userName
GROUP BY [MasterDocument].[DocumentID], MasterDocument.DocumentTitleText, MasterDocument.CreatedByLID, MasterDocument.CreatedDate, [UserGroupRole].[Type]
, [UserGroup].[GroupName]
)a

LEFT JOIN (

SELECT distinct MasterDocument.DocumentID,
MasterDocument.DocumentTitleText,
dbo.sf_getUsernameFromUserID(MasterDocument.CreatedByLID) CreatedUserName,
MasterDocument.CreatedDate,
[UserGroupRole].[Type] [privilege]
,[UserGroup].[GroupName]
FROM MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
WHERE [User].[UserName] = @userName
GROUP BY [MasterDocument].[DocumentID], MasterDocument.DocumentTitleText, MasterDocument.CreatedByLID, MasterDocument.CreatedDate, [UserGroupRole].[Type]
, [UserGroup].[GroupName]) b ON a.DocumentID = b.DocumentID AND dbo.sf_convertDocTypeToValue (a.privilege) < dbo.sf_convertDocTypeToValue(b.privilege) WHERE b.DocumentID IS null

)

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 17:10:51

okay I think I need to use session here.
Means if user is already logged in then my stored procedure will take that user name from the session and then give the result.

can anyone please guide me doing this???

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources