filtering a resultset in a stored procedure

[Michael Stettner]

Hello,

i am a newbie in stored procedure programming and i am interested performing a task with a stored procedure which is either impossible or so simple that i cannot find information about it anywhere.
(Thats the reason why i post this)

My aim:

I would like to execute a select statement in a stored procedure
and filter the result set (inside the stored procedure) before i give it back
to the client application (VB client executing the stored procedure)

Background:

In our databases we have a region field (varchar(2)) which conatins the region code (e.g. 'DE', 'US', ...) and as special value there may be a '*'
which means 'all regions'

Buisness rule says: If there is an entry with a concrete region take this one otherwise take the '*'

Plan:

Stored procedure performs the select , orders by region so that * is behind
other region codes
if special region entry is found delete * entry from the result set

Is this possible ?

Happy about any hint

Michael

[Marc Kozlowski]

------------
Michael Stettner at 2/28/01 8:34:18 AM

Hello,

i am a newbie in stored procedure programming and i am interested performing a task with a stored procedure which is either impossible or so simple that i cannot find information about it anywhere.
(Thats the reason why i post this)

My aim:

I would like to execute a select statement in a stored procedure
and filter the result set (inside the stored procedure) before i give it back
to the client application (VB client executing the stored procedure)

Background:

In our databases we have a region field (varchar(2)) which conatins the region code (e.g. 'DE', 'US', ...) and as special value there may be a '*'
which means 'all regions'

Buisness rule says: If there is an entry with a concrete region take this one otherwise take the '*'

Plan:

Stored procedure performs the select , orders by region so that * is behind
other region codes
if special region entry is found delete * entry from the result set

Is this possible ?

Happy about any hint

Michael

Hi Michael

You are probably going to want to use a two-parse stored procedure for this. I could do with seeing the stored procedure as it is now, plus the field details from the table(s) involved, including what is required in the results recordset.

If you would like me to help, please send these to marc@bizwebz.co.uk.

Regards

Marc Kozlowski

Great help from Marc

Here is the result of it. (example code)

Thank you very much
Michael


CREATE PROCEDURE SPFilter(
@p_region VARCHAR(3)
)
AS

DECLARE @PubId int
DECLARE @NodeId int
DECLARE @Region VARCHAR(2)
DECLARE @Language VARCHAR(3)
DECLARE @Value VARCHAR(255)

SET NOCOUNT ON
SET ANSI_WARNINGS ON


-- create the temp table
CREATE TABLE #tblResult (
PubId INT ,
NodeId INT ,
Region VARCHAR(2),
Language VARCHAR(3),
Value VARCHAR(255)
)


--- populate temp table
INSERT INTO #tblResult
SELECT PubId, NodeId, Region, Language, Value
FROM tblTest
WHERE Region = @p_Region or Region = '*'
order by Region


DECLARE list_values CURSOR FOR
SELECT PubId, NodeId, Region, Language, Value FROM #tblResult order by NodeId

OPEN list_values

--- Get the first
FETCH list_values INTO @PubId, @NodeId, @Region, @Language, @Value


--- Process this region then loop through the remaining regions
WHILE @@fetch_status = 0
BEGIN
-- falls konkrete Region da dann weg mit dem *
if @region = @p_Region
begin
PRINT 'Deleting' --+ ' ' + @Nodeid + ' ' + @Region + ' ' + @Language + ' ' + @Value
delete from #tblResult where nodeid = @nodeid and region = '*' and language = @Language
end


FETCH NEXT FROM list_values INTO @PubId, @NodeId, @Region, @Language, @Value
END

CLOSE list_values

OPEN list_values

--- Get the first
FETCH list_values INTO @PubId, @NodeId, @Region, @Language, @Value

WHILE @@fetch_status = 0
BEGIN
PRINT @NodeId
PRINT @Region
PRINT @Language
PRINT @VALUE
PRINT '===========================================&# 39;
FETCH NEXT FROM list_values INTO @PubId, @NodeId, @Region, @Language, @Value
END

CLOSE list_values


DEALLOCATE list_values
DROP TABLE #tblResult












CREATE TABLE [dbo].[tblTest] (
[PubId] [int] NULL ,
[NodeId] [int] NULL ,
[Region] [varchar] (3) NULL ,
[Value] [varchar] (255) NULL ,
[Language] [varchar] (3) NULL
) ON [PRIMARY]
GO



1,1000002,"DE ","spezial: Automatisierungssysteme","deu"
1,1000005,"*","Installationstechnik&#3 4;,"deu"
1,1000007,"*","... und was Sie sonst noch brauchen","deu"
1,1000009,"*","Software-Lizenzbedingungen","deu"
1,1000010,"*","Allgemeine Bedingungen","deu"
1,1000011,"*","Erläuterungen zu den Allgemeinen Bedingungen","deu"
1,1000012,"*","Softwarenutzung",&# 34;deu"
1,1000013,"*","Einfache Lizenz","deu"
1,1000014,"*","Kopierlizenz"," deu"
1,1000015,"*","Sicherungskopie",&# 34;deu"
1,1000016,"*","Netzwerknutzung",&# 34;deu"
1,1000017,"*","Lizenzierung"," deu"
1,1000018,"*","Lizenzvertrag",&#34 ;deu"
1,1000019,"*","Softwareweitergabe" ,"deu"
1,1000020,"*","Upgrade/Service Packs","deu"
1,1000000,"*","Interaktiver Katalog Siemens A&D","deu"
1,1000003,"*","Antriebstechnik",&# 34;deu"
1,1000002,"*","Automatisierungssysteme ","deu"
1,1000002,"*","Automation Systems","eng"
1,1000003,"DE","spezial: Antriebstechnik","deu"