Toolbox: Kommaseparerad lista till tabell

En för mig vanligt förekommande sak när jag byggt webbapplikationer är att användaren är i behov av att kunna välja en eller flera kategorier vid en utsökning av något för att ta fram en rapport. I de flesta fall har man en lista med checkboxar som på serversidan blir en kommaseparerad sträng med kategorivärden vilken skickas till en lagrad procedur och förhoppningsvis så får man ett resultat tillbaka.

Tyvärr är det många som inte riktigt vet hur man ska hantera det hela i den lagrade proceduren då inparametern är en sträng av heltal exempelvis.

Det jag frekvent använt är en egendefinierad funktion vilken tar en sträng som inparameter och returnerar en tabell av heltal. Finessen är att detta kan göras som en underfråga i "WHERE"-satsen.

För att skapa lite klarhet i vad jag menar så tittar vi på lite kod:

CREATE FUNCTION [dbo].[CsvToInt] ( @Array VARCHAR(1000))
   RETURNS @IntTable TABLE (IntValue INT)
AS
BEGIN
   -- Declare variables
   DECLARE @separator CHAR(1)
   DECLARE @separator_position INT
   DECLARE @array_value VARCHAR(1000)

   -- Specify separator token
   SET @separator = ','

   -- Make sure we get the last value too...
   SET @array = @array + ','

   -- Loop and get values
   WHILE(PATINDEX('%,%' , @array) <> 0)
   BEGIN
      SELECT @separator_position = PATINDEX('%,%' , @array)
      SELECT @array_value = left(@array, @separator_position - 1)

      INSERT @IntTable VALUES (CAST(@array_value AS INT))
      SELECT @array = STUFF(@array, 1, @separator_position, '')
   END
   RETURN
END

Denna lilla funktion kan nu anropas på detta vis:

SELECT * FROM Products
WHERE CategoryID IN (SELECT * FROM CsvToInt('1,3,7'))

Går allt som det ska nu så listas det produkter som har kategoriID 1, 3, 7 ur Northwind-databasen.

Själv har jag duplicerat denna funktion och använder den för de fall där den kommaseparerade strängen består av strängar också. Det enda som behöver ändras är typkonvertering från "INT" till "VARCHAR" och så namnet förstås... ;-)

Denna lilla funktion fungerar i SQL7/SQL2000/SQL2005. Gällande upphovsrätten till denna funktion kan jag inte ta åt mig äran, utan någon gav mig koden för många år sedan, känner du dig träffad, hör av dig så hamnar ditt namn här... :-)