如何在sql存储过程中为列传递多个值？

i have two tables, such as

我有两张桌子,比如

Queue

QueueID
LogParameters

QueueParameters

QueueParametersID
QueueID
LogParametersKey
LogParametersValue

I have to write SP inorder to make an entry in both the tables,

我必须编写SP才能在两个表中创建一个条目,

LogParameterKey and LogParameterValue may contains multiple values but the QueueID for each values should be same and QueueParameterID could be different.

LogParameterKey和LogParameterValue可能包含多个值,但每个值的QueueID应相同,QueueParameterID可能不同。

**QueueID**  **LogParameters**      
   1          AA        



**QueueParametersID**   **QueueID**  **LogParametersKey** **LogParametersValue**
       1                     1             FirstName             Mohammad
       2                     1             LastName               Salman
       3                     1                Age                    17

How do i pass multiple values for LogParameterKey and LogParameterValue?.. Someone suggested me to use Array for this... Is there's any other way?

我如何为LogParameterKey和LogParameterValue传递多个值?..有人建议我使用Array这个......还有其他方法吗?

CREATE Procedure AddQueue @LogParameters NVARCHAR(255) @AuditParameters AS AuditParameter READONLY,-- UserDefinedTable

CREATE过程AddQueue @LogParameters NVARCHAR(255)@AuditParameters AS AuditParameter READONLY, - UserDefinedTable

SET NOCOUNT ON;

BEGIN 

DECLARE @QueueID BIGINT

EXECUTE dbo.procInsertQueue
@LogParameters = @LogParameters,
@QueueID = @QueueID OUTPUT


DECLARE @GetQueueID BIGINT = (SELECT QueuesID FROM Queues WHERE LogParameters= @LogParameters
DECLARE @AuditQueueParametersID BIGINT
DECLARE @TempTable TABLE(
ParameterKey NVARCHAR(255),
ParameterValue NVARCHAR(255),
AuditQueuesID BIGINT)

INSERT INTO @TempTable(ParameterKey,ParameterValue,QueuesID)
SELECT ParameterKey,ParameterValue,@GetQueueID FROM @AuditParameters

DECLARE @LogParameterKey NVARCHAR(255) = (SELECT ParameterKey FROM @TempTable WHERE QueuesID = @GetQueueID)
DECLARE @LogParameterValue NVARCHAR(255) = (SELECT ParameterValue FROM @TempTable WHERE QueuesID = @GetQueueID)

EXECUTE dbo.procAddQueueParameters
@AuditQueueID = @GetQueueID 
@LogParametersKey = @LogParametersKey,
         @LogParametersValue = @LogParametersValue,
@QueueParametersID = @QueueParametersID OUTPUT
END

END

1 个解决方案

#1

1. Use User-Defined Table Types

1.使用用户定义的表类型

https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

2. Generate xml string and parse in SP

2.生成xml字符串并在SP中解析

3.Use temp table

3.使用临时表

CREATE TABLE #Input
(
QueueParametersID ..
QueueID ..
LogParametersKey ..
LogParametersValue ..
)
--INSERT VALUES
EXEC dbo.YouSP

IN SP use table #Input

IN SP使用表#Input

#1