저장 프로 시저에 매개 변수 배열 전달
"id"배열을 저장 프로 시저에 전달하여 배열의 id와 일치하는 행을 제외하고 테이블에서 모든 행을 삭제해야합니다.
가장 간단한 방법으로 어떻게 할 수 있습니까?
저장 프로 시저를 사용하십시오.
편집 : 목록 직렬화 (또는 기타)에 대한 보완 :
List<string> testList = new List<int>();
testList.Add(1);
testList.Add(2);
testList.Add(3);
XmlSerializer xs = new XmlSerializer(typeof(List<int>));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);
string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());
결과 (XML 매개 변수와 함께 사용 가능) :
<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>1</int>
<int>2</int>
<int>3</int>
</ArrayOfInt>
원래 게시물 :
XML을 매개 변수로 전달 :
<ids>
<id>1</id>
<id>2</id>
</ids>
CREATE PROCEDURE [dbo].[DeleteAllData]
(
@XMLDoc XML
)
AS
BEGIN
DECLARE @handle INT
EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc
DELETE FROM
YOURTABLE
WHERE
YOUR_ID_COLUMN NOT IN (
SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.')
)
EXEC sp_xml_removedocument @handle
Sql Server 2008 이상을 사용하는 경우 저장 프로 시저로 전달할 때마다 목록 데이터를 직렬화 및 역 직렬화하는 대신 TVP (테이블 반환 매개 변수)라는 것을 사용할 수 있습니다.
플레이 그라운드 역할을 할 간단한 스키마를 생성하는 것으로 시작하겠습니다.
CREATE DATABASE [TestbedDb]
GO
USE [TestbedDb]
GO
/* First, setup the sample program's account & credentials*/
CREATE LOGIN [testbedUser] WITH PASSWORD=N'µ×?
?S[°¿Q¥½q?_Ĭ¼Ð)3õļ%dv', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
CREATE USER [testbedUser] FOR LOGIN [testbedUser] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', N'testbedUser'
GO
/* Now setup the schema */
CREATE TABLE dbo.Table1 ( t1Id INT NOT NULL PRIMARY KEY );
GO
INSERT INTO dbo.Table1 (t1Id)
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);
GO
스키마와 샘플 데이터가 준비되었으므로 이제 TVP 저장 프로 시저를 만들 준비가되었습니다.
CREATE TYPE T1Ids AS Table (
t1Id INT
);
GO
CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY )
AS
BEGIN
SET NOCOUNT ON;
SELECT Table1.t1Id FROM dbo.Table1 AS Table1
JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id;
END
GO
스키마와 API를 모두 사용하면 다음과 같이 프로그램에서 TVP 저장 프로 시저를 호출 할 수 있습니다.
// Curry the TVP data
DataTable t1Ids = new DataTable( );
t1Ids.Columns.Add( "t1Id",
typeof( int ) );
int[] listOfIdsToFind = new[] {1, 5, 9};
foreach ( int id in listOfIdsToFind )
{
t1Ids.Rows.Add( id );
}
// Prepare the connection details
SqlConnection testbedConnection =
new SqlConnection(
@"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5" );
try
{
testbedConnection.Open( );
// Prepare a call to the stored procedure
SqlCommand findMatchingRowsInTable1 = new SqlCommand( "dbo.FindMatchingRowsInTable1",
testbedConnection );
findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure;
// Curry up the TVP parameter
SqlParameter sqlParameter = new SqlParameter( "Table1Ids",
t1Ids );
findMatchingRowsInTable1.Parameters.Add( sqlParameter );
// Execute the stored procedure
SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader( );
while ( sqlDataReader.Read( ) )
{
Console.WriteLine( "Matching t1ID: {0}",
sqlDataReader[ "t1Id" ] );
}
}
catch ( Exception e )
{
Console.WriteLine( e.ToString( ) );
}
/* Output:
* Matching t1ID: 1
* Matching t1ID: 5
* Matching t1ID: 9
*/
Entity Framework와 같은 더 추상적 인 API를 사용하여이 작업을 수행하는 덜 고통스러운 방법이있을 것입니다. 하지만 지금은 스스로 볼 시간이 없습니다.
이것이 최고의 소스입니다.
http://www.sommarskog.se/arrays-in-sql.html
링크를 사용하여 분할 함수를 만들고 다음과 같이 사용하십시오.
DELETE YourTable
FROM YourTable d
LEFT OUTER JOIN dbo.splitFunction(@Parameter) s ON d.ID=s.Value
WHERE s.Value IS NULL
이것은 당신을 위해 그것을 해야하는 위의 링크를 기반으로 한 코드입니다 ...
내 함수를 사용하기 전에 "도우미"테이블을 설정해야합니다.이 작업은 데이터베이스 당 한 번만 수행하면됩니다.
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
이 함수를 사용하여 문자열을 분할하십시오. 이는 반복되지 않고 매우 빠릅니다.
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.
Returns a table, one row per item in the list, with a column name "ListValue"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
RETURN
END --Function FN_ListToTable
이 함수를 조인에서 테이블로 사용할 수 있습니다.
SELECT
Col1, COl2, Col3...
FROM YourTable
INNER JOIN dbo.FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue
다음은 삭제입니다.
DELETE YourTable
FROM YourTable d
LEFT OUTER JOIN dbo.FN_ListToTable(',',@Parameter) s ON d.ID=s.ListValue
WHERE s.ListValue IS NULL
이것을 시도해 볼 수 있습니다.
DECLARE @List VARCHAR(MAX)
SELECT @List = '1,2,3,4,5,6,7,8'
EXEC(
'DELETE
FROM TABLE
WHERE ID NOT IN (' + @List + ')'
)
You could use a temp table which the stored procedure expects to exist. This will work on older versions of SQL Server, which do not support XML etc.
CREATE TABLE #temp
(INT myid)
GO
CREATE PROC myproc
AS
BEGIN
DELETE YourTable
FROM YourTable
LEFT OUTER JOIN #temp T ON T.myid=s.id
WHERE s.id IS NULL
END
declare @ids nvarchar(1000)
set @ids = '100,2,3,4,5' --Parameter passed
set @ids = ',' + @ids + ','
select *
from TableName
where charindex(',' + CAST(Id as nvarchar(50)) + ',', @ids) > 0
I'd consider passing your IDs as an XML string, and then you could shred the XML into a temp table to join against, or you could also query against the XML directly using SP_XML_PREPAREDOCUMENT and OPENXML.
What about using the XML data type instead of passing an array. I find that a better solution and works well in SQL 2005
I like this one, because it is suited to be passed as an XElement, which is suitable for SqlCommand
(Sorry it is VB.NET but you get the idea)
<Extension()>
Public Function ToXml(Of T)(array As IEnumerable(Of T)) As XElement
Return XElement.Parse(
String.Format("<doc>{0}</doc>", String.Join("", array.Select(Function(s) String.Concat("<d>", s.ToString(), "</d>")))), LoadOptions.None)
End Function
This is the sql Stored proc, shortened, not complete!
CREATE PROCEDURE [dbo].[myproc] (@blah xml)
AS ... WHERE SomeID IN (SELECT doc.t.value('.','int') from @netwerkids.nodes(N'/doc/d') as doc(t))
In SQL Server 2016 you can wrap array with [ ] and pass it as JSON see http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures-as-json.aspx
You could use the STRING_SPLIT function in SQL Server. You can check the documentation here.
DECLARE @YourListOfIds VARCHAR(1000) -- Or VARCHAR(MAX) depending on what you need
SET @YourListOfIds = '1,2,3,4,5,6,7,8'
SELECT * FROM YourTable
WHERE Id IN(SELECT CAST(Value AS INT) FROM STRING_SPLIT(@YourListOfIds, ','))
참고URL : https://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure
'program tip' 카테고리의 다른 글
| 목록이 아닌 F #에서 시퀀스를 사용하는 경우 (0) | 2020.10.24 |
|---|---|
| double equals vs is in python (0) | 2020.10.24 |
| 조각간에 데이터를 전달하는 방법 (0) | 2020.10.24 |
| 파이썬 : 줄이 빈 줄인지 확인하는 방법 (0) | 2020.10.24 |
| JBoss AS 7 : tmp를 정리하는 방법? (0) | 2020.10.24 |