sql-server - with - t sql stored procedure return table




Gespeicherte T-SQL-Prozedur, die mehrere ID-Werte akzeptiert (4)

Eine Methode, die Sie vielleicht in Erwägung ziehen, wenn Sie mit den Werten arbeiten, ist, sie zuerst in eine temporäre Tabelle zu schreiben. Dann kommst du einfach wie normal dazu.

Auf diese Weise analysieren Sie nur einmal.

Am einfachsten ist es, eines der Split-UDFs zu verwenden, aber so viele Leute haben Beispiele dafür gepostet. Ich dachte, ich würde einen anderen Weg gehen;)

In diesem Beispiel wird eine temporäre Tabelle erstellt, an der Sie teilnehmen können (#tmpDept) und die mit der Abteilungs-ID, die Sie übergeben haben, gefüllt wird. Ich nehme an, Sie trennen sie mit Kommas, aber Sie können natürlich ändern es zu was auch immer du willst.

IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL
BEGIN
    DROP TABLE #tmpDept
END

SET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','')

CREATE TABLE #tmpDept (DeptID INT)
DECLARE @DeptID INT
IF IsNumeric(@DepartmentIDs)=1
BEGIN
    SET @[email protected]
    INSERT INTO #tmpDept (DeptID) SELECT @DeptID
END
ELSE
BEGIN
        WHILE CHARINDEX(',',@DepartmentIDs)>0
        BEGIN
            SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1)
            SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs))
            INSERT INTO #tmpDept (DeptID) SELECT @DeptID
        END
END

Auf diese Weise können Sie eine Abteilungs-ID, mehrere IDs mit Kommata dazwischen oder mehrere IDs mit Kommas und Leerzeichen dazwischen übergeben.

Also, wenn du etwas getan hast wie:

SELECT Dept.Name 
FROM Departments 
JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID
ORDER BY Dept.Name

Sie sehen die Namen aller Abteilungs-IDs, die Sie übergeben haben.

Dies kann wiederum vereinfacht werden, indem eine Funktion verwendet wird, um den temporären Tisch zu füllen ... Ich habe es hauptsächlich ohne jemanden getan, um etwas Langeweile zu töten :-P

- Kevin Fairchild

Gibt es eine elegante Möglichkeit, eine Liste von IDs als Parameter an eine gespeicherte Prozedur zu übergeben?

Zum Beispiel möchte ich, dass die Abteilungen 1, 2, 5, 7, 20 von meiner gespeicherten Prozedur zurückgegeben werden. In der Vergangenheit habe ich eine Komma-getrennte Liste von IDs übergeben, wie den unten stehenden Code, fühle mich aber sehr schmutzig dabei.

SQL Server 2005 ist meine einzige gültige Einschränkung, denke ich.

create procedure getDepartments
  @DepartmentIds varchar(max)
as
  declare @Sql varchar(max)     
  select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
  exec(@Sql)

Eine superschnelle XML-Methode, wenn Sie eine gespeicherte Prozedur verwenden und die kommagetrennte Liste der Abteilungs-IDs übergeben möchten:

Declare @XMLList xml
SET @XMLList=cast('<i>'+replace(@DepartmentIDs,',','</i><i>')+'</i>' as xml)
SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i))

Der ganze Lob geht an Guru Brad Schulz's Blog


Ja, Ihre aktuelle Lösung ist anfällig für SQL-Injection-Angriffe.

Die beste Lösung, die ich gefunden habe, ist, eine Funktion zu verwenden, die Text in Wörter aufteilt (es gibt einige, die hier gepostet werden, oder Sie können diese aus meinem Blog verwenden ) und verbinden Sie diese dann mit Ihrem Tisch. Etwas wie:

SELECT d.[Name]
FROM Department d
    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId

Probier diese:

@list_of_params varchar(20) -- value 1, 2, 5, 7, 20 

SELECT d.[Name]
FROM Department d
where @list_of_params like ('%'+ CONVERT(VARCHAR(10),d.Id)  +'%')

sehr einfach.







stored-procedures