SQL Server Performance Forum – Threads Archive

explicit output from sp

Hello everyone i have a problem with my stored procedure on a MS SQL Server.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure proc_FTP_AccessRules2
(@id INT) as SET NOCOUNT ON DECLARE
@group VARCHAR(50),
@pointer INT declare
@folder nvarchar(800),
@accessRule nvarchar(200) if @id is not null
begin /*
===========================================================================
all accessRules for this User
===========================================================================
*/
PRINT ‘create temporary table [#tmpAccessRule]’
CREATE TABLE [#tmpAccessRule] (
[accessRule] [nvarchar] (200) COLLATE Latin1_General_CI_AS NOT NULL
) PRINT ‘fill [#tmpAccessRule] with accessRule from [ftp_users]’
INSERT INTO [#tmpAccessRule]
SELECT
[accessRule]
FROM
[iboxxDevelop].[dbo].[ftp_users]
WHERE
[id] = @id PRINT ‘fill [#tmpAccessRule] with accessRules from [ftp_userAccess]’
INSERT INTO [#tmpAccessRule]
SELECT
ua.[accessRule]
FROM
[iboxxDevelop].[dbo].[ftp_userAccess] ua
INNER JOIN
[iboxxDevelop].[dbo].[ftp_users] u
ON
ua.[ftpUserName]=u.[ftpUserName]
WHERE
u.[id] = @id
ORDER BY
ua.[indexNo]
SET @pointer = 0
SELECT @group = (SELECT [groups] from [iboxxDevelop].[dbo].[ftp_users] WHERE [email protected]) while @pointer < LEN(@group)-(CHARINDEX(‘|’,REVERSE(@group),1)-1)
BEGIN
BEGIN
PRINT ‘fill [#tmpAccessRule] with accessRule from [ftp_groups]’
INSERT INTO [#tmpAccessRule]
SELECT
[accessRule]
FROM
[iboxxDevelop].[dbo].[ftp_groups]
WHERE [ftpUserName]=” + SUBSTRING(@group,@pointer,CHARINDEX(‘|’,@group,@pointer)) + ”
END BEGIN /*
==========================================================================
all folders that match a certain access rule
==========================================================================
*/
PRINT ‘create temporary table [#tmpSelected_folders]’
CREATE TABLE [#tmpSelected_folders] (
[folder] [nvarchar] (800) COLLATE Latin1_General_CI_AS NOT NULL
, [accessRule] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL
) PRINT ‘fill [#tmpSelected_folders] with folders with exitsting accessRule’
INSERT INTO [#tmpSelected_folders]([folder])
SELECT
c.folder
from
[#tmpAccessRule] a
inner join
[ftp_dirStructure] c
ON LOWER(c.folder) LIKE LOWER(SUBSTRING(a.[accessRule],1,CHARINDEX(‘|’,a.[accessRule],1)-1))
or LOWER(c.folder) LIKE LOWER(SUBSTRING(a.[accessRule],1,CHARINDEX(‘|’,a.[accessRule],1)-1)) + ‘%’
GROUP BY c.folder /*
===========================================================================
folders with matched accessRule
===========================================================================
*/
Print ‘declare variables’ Print ‘curAccessRule’
DECLARE curAccessRule CURSOR FOR
SELECT * FROM [#tmpAccessRule]
FOR READ ONLY Print ‘curFolder’
DECLARE curFolders CURSOR FOR
SELECT folder FROM [#tmpSelected_folders]
FOR UPDATE OF accessRule OPEN curFolders FETCH NEXT FROM curFolders
INTO @folder
WHILE @@FETCH_STATUS = 0
BEGIN
OPEN curAccessRule FETCH NEXT FROM curAccessRule
INTO @accessRule WHILE @@FETCH_STATUS = 0
BEGIN
IF (LOWER(@folder) like LOWER(SUBSTRING(@accessRule,1,CHARINDEX(‘|’,@accessRule,1)-1)))
OR (LOWER(@folder) like (LOWER(SUBSTRING(@accessRule,1,CHARINDEX(‘|’,@accessRule,1)-1)) + ‘%’))
BEGIN
UPDATE [#tmpSelected_folders] SET [accessRule] = @accessRule WHERE CURRENT OF curFolders
BREAK
END FETCH NEXT FROM curAccessRule
INTO @accessRule
END CLOSE curAccessRule FETCH NEXT FROM curFolders
INTO @folder
END CLOSE curFolders Print ‘Deallocate Cursors’
DEALLOCATE curFolders
DEALLOCATE curAccessRule
/*
===========================================================================
show result
===========================================================================
*/
PRINT ‘show [#tmpSelected_folders]’
INSERT INTO ##tmpMasterTable
SELECT
d.[folder]
, f.[accessRule]
FROM
[#tmpSelected_folders] f
RIGHT OUTER JOIN
[ftp_dirStructure] d
ON f.[folder] = d.[folder] /*
===========================================================================
destroy all items
===========================================================================
*/
Print ‘drop [#tmpSelected_folders]’
DROP TABLE [#tmpSelected_folders] Print ‘drop [#tmpAccessRule]’
DROP TABLE [#tmpAccessRule] end
else
select ‘nix’ as nix
SET NOCOUNT OFF GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
as you can see there are 2 temporary tables.
for each item in the first table the second table is scanned from top to bottom
if theres is an entry that fits the where clause, the first table is updated
an the next item from the first table is processed and so on.
when the first table is completely updated, i make a select to access
this output from an asp site.
asp site works fine if used with an procedure with just an select statement, so there should be no error.
if i use this sp i get an error: "… not possible for closed.object".
uncommenting the cursor curAccessRule and all FETCH statments for cursor curFolders
will work, but give me the wrong result.
I suppose the cursor curFolders gives me the first recordset which the asp site processes
since the cursor is at the end this rs is empty an closes.
my question is if i can suppress the ouput from the cursor to access only the select statement at the end of the sp please help me
elmi

Not sure that it is the source for the problem, but there is no CLOSE curAccessRule before the DEALLOCATE. You should also remove all PRINT commands when you start calling the procedure by a client app other than QA. (Do not use — or /* */ but remove completely.)

there is an CLOSE curAccessRule before deallocation As far as i know the PRINT commands shouldnÂ´t bother the result,
because of the SET NOCOUNT OFF but thanks for trying to help

Okay, the flow of the procedure is less than ideal. You open the curFolders, and start looping through it.
For each row that you fetch from curFolders, you open curAccessRules.
Then when you’ve handled the first row of curFolders you close curAccessRule, fetch the next row from curFolders and open curAccessRule again. So you’re not deallocating curAccessRule before re-opening it …

creating a new cursor for every loop of the cursor curFolders
is, if you think of performance reasons, far less then ideal.
so i wonÂ´t change that.
besides that theres no problem with ne loop
it works fine and quick. if i run my code like that /*
===========================================================================
folders with matched accessRule
===========================================================================
*/
Print ‘declare variables’ Print ‘curAccessRule’
DECLARE curAccessRule CURSOR FOR
SELECT * FROM [#tmpAccessRule]
FOR READ ONLY Print ‘curFolder’
DECLARE curFolders CURSOR FOR
SELECT folder FROM [#tmpSelected_folders]
FOR UPDATE OF accessRule OPEN curFolders CLOSE curFolders Print ‘Deallocate Cursors’
DEALLOCATE curFolders
DEALLOCATE curAccessRule
it works fine. /*
===========================================================================
folders with matched accessRule
===========================================================================
*/
Print ‘declare variables’ Print ‘curAccessRule’
DECLARE curAccessRule CURSOR FOR
SELECT * FROM [#tmpAccessRule]
FOR READ ONLY Print ‘curFolder’
DECLARE curFolders CURSOR FOR
SELECT folder FROM [#tmpSelected_folders]
FOR UPDATE OF accessRule OPEN curFolders FETCH NEXT FROM curFolders
INTO @folder
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curFolders
INTO @folder
END CLOSE curFolders Print ‘Deallocate Cursors’
DEALLOCATE curFolders
DEALLOCATE curAccessRule
and this version wonÂ´t work. so you see thereÂ´s a problem with the resultset
the cursor delivers

i donÂ´t mean to be cocky iÂ´m just trying to solve my problem <img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ /> hereÂ´s a quick explanation why i need the nested cursor for every folder in the first table the 2. cursors checks a list of rules (the order of that list is very important) if any rule fits the searched expression, the accessrule is entered in the first table, and the check of the rulelist is aborted. then the first cursor moves on to the next record and begins the check of the rules list and so on i hope you understand, couse itÂ´s kind a hard to explain this in short

If I got right what the process is, I believe you can do it using temp tables, maybe creating an index or two on temp tables and then use set operations. Something like:
Update t1
set t1.column = rule dependant expression
from #t1 t1
join #t2 t2 on …
order by t1.key, t2.rule

i think the "order by" wonÂ´t work on a MS SQL Server cuz i got an error message. since it didnÂ´t work, i couldnÂ´t really try it, but i think your query dosenÂ´t do exaclty what iÂ´m up to. for each folder thereÂ´s only one access rule itÂ´s a 1:1 relationship, BUT there could be many different rules in the rules list that match the folder where only the first one matching is the one to choose. <pre id="code"> folders rules ====================== =================== D:foldersubfolder D:folder C:systemfolder D:foldersubfolder C<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />ystemfolder </pre id="code"> After running the query the new table should look like this <pre id="code"> D:foldersubfolder | D:folder C:systemfolder | C:systemfolder </pre id="code"> if the rule list would be like that: <pre id="code"> rules =================== D:foldersubfolder D:folder C<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />ystemfolder </pre id="code"> the result would be: <pre id="code"> D:foldersubfolder | D:foldersubfolder C:systemfolder | C:systemfolder </pre id="code">

1. You are right it doesn’t work on updates.
2. I’m not quite sure I understand your intention but let me try another theory:
insert into #t3(key, rule)
select t1.key, min(t2.rule) as rule
from #t1 t1
join #t2 t2 on …
—
upadate t1
set t1.rule = t3.rule
from #t1 t1
join #t3 t3 on t1.key = t3.key It may also be slow but I would give it a try.

]]>