Hej!

Jag försöker skriva en Stored Procedure som skall hämta data från en viss tabell, men där jag inte vet vilka index den skall leta efter, dessa index får jag som en indata-parameter i varchar-format. Den kan t ex se ut så här:

@indata = "123,156,347,568"

Nu undrar jag, kan jag på något sätt skriva en SQL-fråga som hämtar upp data från en specifik tabell där index skall vara lika med något av dessa index?! (Index i den tabellen är av typen int) Dvs:

SELECT * FROM tabell
WHERE index IN (@indata)

Ovanstående exempel fungerar alltså INTE :-) Så är det någon som har förslag på annan lösning?!

Hemskt tacksam
<b>//Qez</b>

Jag fick det inte att funka, men det är något åt det hållet

declare @sql varchar (200)
declare @indata varchar (200)
SET @indata = '123,156,347,568'
SET @sql = 'SELECT * FROM tabell WHERE index IN (' + @indata + ')'
EXEC @SQL

Så kan du göra t.ex.

Tack!

Problemet är att om man gör så så får man inte så stor nytta av att lägga SQL-frågan i en Stored Procedure, SQL-koden kompileras väl inte på ett effektivt sätt när frågan ligger i en textsträng?

En lösning jag försöker arbeta på istället just nu är att konvertera index i tabellen till varchar och därefter jämnföra den med indata-variabeln, men det verkar inte heller fungera på ett bra sätt...

Kollade lite på nätet och det finns ju en del länkar om att "trolla med siffror" i SQL-Server, t ex:

http://www.sql-server-performance.com/vk_fun_numbers_transactsql.asp

Fast exemplena där var inte direkt användbara heller...

Kan väl tillägga att indata till proceduren kommer via en anna SQL-sats som körs tidigare och jag tänkte att jag skulle försöka använda de index som jag redan hade fått istället för att använda mig av en (rätt komplex) subquery för att hämta dem igen inne i denna frågan...

Får fundera vidare...

Mvh <b>//Qez</b>

Jag löste det problemet såhär för ett tag sedan. Det som händer är att sp:n inte kan cacha resultatet eftersom satsen inte kan kollas - men det fungerar i alla fall, om även inte optimerad

create procedure test
@array varchar(500)
as
set nocount on
declare @tmp varchar(500)
set @tmp = 'select * from tabell where poster in(' + @array + ')'

execute (@tmp)

Tack Pelle!

Nej, det är ju sant - lite trist att frågan inte cachas... får nog ta tjuren vid hornen och ändå använda den jobbia sql-satsen som en subquery här för att få fram nödvändiga index... känns som prestandan i alla fall blir sämre om jag tillverkar sql-satsen som en sträng och sedan exekverar den.

Tack i alla fall!! :-)

Mvh
<b>//Qez</b>

Märkte en sak för en tid sedan då jag gjorde så som trash visar, att skriva:

Exec @kommando

Det fungerade inte och jag fick fel. Men när jag däremot satte parentes runt det så gick det utmärkt. Kan vara bra att veta!

Exec (@kommando)

Hälsningar
/Pelle

*ler*

Jo tack, jag upptäckte att det allt gick bättre med parantes! :-)

Tack igen!
// Qez

Precis som ni säger så kan det bli dålig prestanda om man kör en dynamisk SQL-sträng med EXEC (). Det finns dock sätt för att förbättra möjligheterna för SQL Server att återanvända exekveringsplanen genom att köra den med sp_executesql istället för EXEC. Om det kan ni läsa mer i ett av tipsen på min site (www.hedgate.net), som dock just för tillfället är nere (bör komma upp igen senare idag).

Men, dynamiska strängar har flera andra potentiella problem, och man ska helst undvika dem så mycket som möjligt. Som jag tidigare nämnt i andra inlägg ska man alltid söka en set-baserad lösning på problem i SQL, inte lösa dem med ex. cursors eller while-loopar, vilket många använder för just detta problemet att splitta en kommaseparerad sträng. Detta kan vara en rolig tankenöt för den sql-intresserade: skriv en set-baserad lösning på problemet ovan, som ger samma resultat som att köra:

EXEC ('SELECT * FROM tabell WHERE index IN (' + @indata + ')')

Jag skriver en lösning i nästa svar, så om ni själva vill ge det ett försök, läs inte det svaret innan ni testat själv.

Hej igen!!

Min kollega hittade en lösning på nätet som undviker exec. Klipper in hans mail (med hans tillåtelse *ler*) här:

http://www.algonet.se/~sommar/examples_of_lists.html#list_of_integers

Utdrag för en funktion som gör om en kommaseparerad sträng till en lista (tabell eller array) med integer-värden.

Here is a user-defined function for this case:

CREATE FUNCTION intlist_to_tbl (@list varchar(8000))
RETURNS @tbl TABLE (val int) AS
BEGIN
DECLARE @ix int,
@pos int,
@str varchar(8000),
@num int
SET @pos = 1
SET @ix = 1
WHILE @ix > 0
BEGIN
SET @ix = charindex(',', @list, @pos)
IF @ix > 0
SET @str = substring(@list, @pos, @ix - @pos)
ELSE
SET @str = substring(@list, @pos, len(@list))
SET @str = ltrim(rtrim(@str))
IF @str LIKE '%[0-9]%' AND
(@str NOT LIKE '%[^0-9]%' OR
@str LIKE '[-+]%' AND
substring(@str, 2, len(@str)) NOT LIKE '[-+]%[^0-9]%')
BEGIN
SET @num = convert(int, @str)
INSERT @tbl (val) VALUES(@num)
END
SET @pos = @ix + 1
END
RETURN
END

Your main procedure would then look like this:
CREATE PROCEDURE get_in_list @values varchar(4000) AS
SELECT col1, col2, col3
FROM tbl
WHERE keycol IN (SELECT * FROM intlist_to_tbl(@values))

mvh
// Qez

Lösningen bygger på att man har en utilitytabell med en massa nummer i, för att kunna använda denna att joina på för att hitta tecknen att splitta på. Denna kan man skapa hur man vill, man behöver bara en kolumn med nummer från 1 och uppåt, så långt man vill. Egentligen behöver den bara gå så långt som längden på input-vektorn (@indata i exemplet), men jag drar till med en cross join på sysobjects för att slippa skriva en loop som lägger in nummer (kom ihåg, alltid tänka set-baserat). Det ger mig ungefär 15000 rader (och därmed nummer från 1 till 15000) i Northwind-databasen. Denna tabell ska man förstås inte skapa varje gång man kör proceduren eller batchen med @indata, utan denna ska finnas skapad sen tidigare. Jag använder tabellen Orders i Northwind som exempel på vilka id det är man vill hämta.


-- Split array and return all orders with orderid's from array
-- Equal to running EXEC ('SELECT OrderID FROM Orders WHERE OrderID IN (' + @indata + ')')
USE Northwind
GO

-- Create table only once
IF EXISTS ( SELECT * FROM sysobjects WHERE [name] = 'NUMBERS' )
DROP TABLE NUMBERS
GO
CREATE TABLE NUMBERS (n int identity(1,1), foo int)
GO

INSERT INTO NUMBERS (foo)
SELECT a.id
FROM sysobjects a, sysobjects b
GO

CREATE CLUSTERED INDEX ixcNUMBERS ON NUMBERS (n)
GO

-- Here starts the split part
DECLARE @indata varchar(100), @sep char(1)
SET @indata = '10248,10263,10569,11039'
SET @sep = ','

SELECT OrderID
FROM Orders o
INNER JOIN (SELECT LEN(LEFT(@indata,n)) - LEN(REPLACE(LEFT(@indata,n),@sep,'')) + 1 AS i
, CAST(SUBSTRING(@indata,n,CHARINDEX(',',@indata+@sep,n) - n) AS int) AS j
FROM NUMBERS
WHERE n < LEN(@indata)
AND SUBSTRING(@sep+@indata,n,1) = @sep) foo
ON o.OrderID = foo.j

Jo, Erlands lösning löser det utan att köra en dynamisk EXEC med IN (@indata). Men, den baseras på en loop, och den gör fortfarande dynamisk sql för att inserta i returtabellen. Dessutom baserar den sig på SQL Server 2000-specifik funktionalitet, och även om den finns i en SQL 7 version så presterar den inte så bra, framförallt använder den sig av en temptabell. Min lösning kan enkelt skrivas om till en helt standard SQL-lösning (bl a LEFT ingår ej i standard SQL, men det går att byta ut mot substring).

Jag och min kollega tackar hjärtligt för tipsen.

Kikade in (som hastigast) på din webbsida också, fanns en del matnyttigt där må jag säga. Personligen kommer jag mest i kontakt med Informix, men tyvärr brukar det aldrig bli utrymme för att riktigt optimera de olika körningarna...

Tack igen!
mvh Qez

Kanske ingen snygg eller smart lösning. Men den borde väl funka?


Create Procedure test
(
@array varchar(500)
) As

DECLARE @tmp varchar(501)
SET @tmp = ',' + @array + ','

SELECT *
FROM Tabell
WHERE CharIndex(@tmp, ',' + convert(varchar(10) , Tabell.Fält)+ ',')>0

Jodå Andreas, det är en mycket snygg lösning, set-baserad och allt. Den behöver färre sidläsningar än min eller Erlands lösningar, men den tar betydligt mer tid (8 ms jämfört med 0 ms för de andra två) och CPU att exekvera. Ett fel är där dock i den, du har bytt plats på parametrarna i charindex.

För just detta problemet (att göra samma sak som en dynamisk IN (@indata) så är det alltså en alternativ lösning man kan använda (man ska alltid pröva vilken som går fortast i ens egen specifika situation). Om man däremot är ute efter att verkligen splitta vektorn för att på något sätt använda de enskilda värdena så får man använda min eller Erlands lösning, eller någon helt annan. :)

En annan intressant lösning som börjar bli populär att visa upp inom sql-kretsar är att utnyttja den inbyggda xml-funktionaliteten i SQL Server 2000. Denna lösning fungerar alltså enbart i SQL Server 2000 och har förstås ingenting med standard SQL att göra:


DECLARE @indata varchar(100), @sep char(1)
SET @indata = '10248,10263,10569,11039'
SET @sep = ','

set nocount on
set @indata = replace(@indata,@sep,'"/><a id="')
set @indata = '<doc><a id="' + @indata + '"/></doc>'
declare @idoc int
exec sp_xml_preparedocument @idoc OUTPUT, @indata
select orderid from orders o
inner join (SELECT id
FROM OPENXML (@idoc, '/doc/a',1)
WITH (id varchar(255) '@id')) foo
on o.orderid = foo.id
EXEC sp_xml_removedocument @idoc

Man har ju inte möjlighet att använda indexet. så den måste göra en tabelscan. Samt att den måste konvertera varje tal till en sträng. Så jag ryser själv när jag ser den. Men det var inte så mycket kod.