SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

prasanth

Asked: 2016-07-20 09:32:48 +0800 CST2016-07-20 09:32:48 +0800 CST 2016-07-20 09:32:48 +0800 CST

Passando valores separados por vírgula para parâmetros no procedimento armazenado

772

Eu tenho um requisito para recuperar dados de uma tabela com base nos valores separados por vírgula enviados nos parâmetros para o procedimento armazenado. A partir de agora, fiz o código funcionar para um único valor, sem saber como fazê-lo funcionar para vários valores.

Tabela de exemplo:

CREATE TABLE [dbo].FinalStatus
(
    [ID] [int] Primary key IDENTITY(1,1) NOT NULL,
    [Col1] [varchar](15) NULL,
    [Col2] [varchar](15) NULL,
    [Col3] [varchar](100) NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL DEFAULT (getdate())
)

Dados de teste:

Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg2')
Insert into FinalStatus (Col1, Col2, Col3) values ('11','C21','Some Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('12','BC21','Some Msg2')

Procedimento armazenado:

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL, 
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)

    SET @c1 = @col1
    SET @c2 = @col2

    SELECT 
        Col2, Col1, 
        LastUpdatedDate, Col3
    FROM 
        dbo.FinalStatus
    WHERE 
        (Col1 = @c1 OR @c1 IS NULL)
        AND (Col2 = @c2 OR @c2 IS NULL)
    ORDER BY 
        LastUpdatedDate DESC
END

Script de execução para valor único (funciona até aqui):

--To get all data
EXEC [dbo].[FindResult]

--passing first parameter alone
EXEC [dbo].[FindResult] @col1 = '10', @col2 = NULL

--passing second parameter alone
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'c21'

Agora, como fazê-lo retornar o resultado apropriado mesmo quando passamos vários valores para o parâmetro?

Algo assim:

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21, c21'

A tabela subjacente teria no mínimo 100.000 registros em um determinado momento.

Tentei ler "Arrays and Lists in SQL Server 2008" de Erland Sommarskog, mas está muito acima da minha cabeça. Então, procurando alguma ajuda para modificar esse procedimento armazenado.

6 respostas

Voted

Julien Vavasseur · Answer 1 · 2016-07-21T01:26:20+08:00

Existem várias maneiras de fazê-lo. Alterar o modelo de dados também pode ser uma opção melhor se você puder fazê-lo.

1. Parâmetros separados por vírgulas

Se você quiser ficar com a string separada por vírgula, você pode encontrar muitas funções de divisão de string online usando CLR, CTE, XML, ... Eu não vou colocá-los todos aqui e não vou compará-los. Mas você deve saber que todos eles têm seus próprios problemas. Para mais informações, você pode ver este post de Aaron Bertrand : Dividir as cordas da maneira certa - ou da próxima melhor maneira

Consulta de amostra (usando conversão XML):

DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @delimiter nvarchar(1) = N',';
SELECT v1 = LTRIM(RTRIM(vals.node.value('(./text())[1]', 'nvarchar(4000)')))
FROM (
    SELECT x = CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML).query('.')
) v
CROSS APPLY x.nodes('/root/data') vals(node);

Resultado:

Consulta principal:

A ideia é convertê-lo em uma tabela. Ele pode então ser usado em uma subconsulta correlacionada:

DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @c2 nvarchar(100) = N'C21, B21';
DECLARE @delimiter nvarchar(1) = N',';

SELECT * 
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c2, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

Apenas a WHEREcláusula deve ser atualizada em seu procedimento. Os parâmetros permanecem do tipo varchar com valores separados por vírgula.

Resultado:

ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

2. Parâmetro(s) XML

Se você pode alterar os tipos de parâmetro, o tipo de dados XML pode ser usado. Ele pode ser facilmente desserializado pelo procedimento e pela consulta.

Esta consulta é bastante semelhante à anterior. No entanto, oferece um melhor controle sobre valores inválidos e caracteres especiais, pois não há conversão ou pesquisa e substituição.

DECLARE @c xml = N'
    <root>
        <c1>10</c1><c1>15</c1><c1>13</c1><c1>14</c1>
        <c2>C21</c2><c2>B21</c2>
    </root>';
DECLARE @delimiter nvarchar(1) = N',';

SELECT * 
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c1') val1(node)  
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c2') val1(node)  
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

Os tipos de parâmetro devem ser alterados para xml. Eu usei apenas uma variável com c1e c2nós, mas 1 variável para cada nó (c1, c2, ...) e nomes de nós diferentes também podem ser usados:

DECLARE @c1 xml = N'<root><data>10</data><data>15</data><data>13</data><data>14</data></root>';
DECLARE @c2 xml = N'<root><data>C21</data><data>B21</data></root>';
DECLARE @c3 xml = N'...';

O caminho e as variáveis corretos devem ser atualizados na nodesparte do arquivo CROSS APPLY.

Usando .Net, Powershell ou outras linguagens, um array ou outros tipos podem ser facilmente convertidos para xml e usados como parâmetro do procedimento.

...

3. Tipos de Tabela Definidos pelo Usuário

Outra opção seria criar um tipo de valor de tabela que pudesse ser usado pelos parâmetros do procedimento armazenado.

Tipo de tabela

CREATE TYPE [dbo].[TableTypeCols] AS TABLE
(
    [col] varchar(15)
);

Procedimento armazenado com parâmetros de tipo de tabela

Em seguida, você atualiza o procedimento armazenado usando este tipo recém-criado:

CREATE OR ALTER PROCEDURE [dbo].[FindResult]
    @c1 [dbo].[TableTypeCols] READONLY
    , @c2 [dbo].[TableTypeCols] READONLY
AS
    SELECT * -- fs.[...], fs.[...], ...
    FROM [dbo].[FinalStatus] fs
    WHERE 
        EXISTS (
            SELECT 1 FROM @c1 c1 WHERE c1.col = fs.Col1
        )
        OR EXISTS (
            SELECT 1 FROM @c2 c2 WHERE c2.col = fs.Col2
        );
GO;

READONLYé obrigatório na declaração do parâmetro.

Chamada de procedimento armazenado com parâmetros de tipo de tabela

Com o SQL, você pode chamá-lo da mesma maneira usando uma variável de tabela:

DECLARE @tc1 [dbo].[TableTypeCols]; 
DECLARE @tc2 [dbo].[TableTypeCols];

INSERT INTO @tc1(col) VALUES('10'), ('15'), ('13'), ('14');
INSERT INTO @tc2(col) VALUES('C21'), ('B21');

EXEC dbo.FindResult @c1 = @tc1, @c2 = @tc2;

Resultado:

ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

Observe que, se você planeja usar mais de algumas linhas para cada parâmetro, deve executar algum teste de desempenho com dados reais e certificar-se de que funcione corretamente em seu sistema.

Chamada .Net C#

O procedimento pode ser chamado de seu código .Net

// Create & Fill Parameter 1
DataTable dt1 = new DataTable();
dt1.Columns.Add("col", typeof (string));
DataRow row = dt1.NewRow();
row["col"] = ("10");
dt1.Rows.Add(row);
/*
    ... add more row ...
*/

// Create & Fill Parameter 2
DataTable dt2 = new DataTable();
dt2.Columns.Add("col", typeof (string));
DataRow row = dt2.NewRow();
row["col"] = ("C21");
dt2.Rows.Add(row);
/*
    ... add more row ...
*/

// Output dataset
DataSet ds = new DataSet("SQLDatabase");

using (SqlConnection conn = new SqlConnection(...))
{
    // Stored Procedure with table parameters
    SqlCommand sqlComm = new SqlCommand("dbo.FindResult", conn);
    sqlComm.CommandType = CommandType.StoredProcedure;

    // Parameter 1
    SqlParameter param = new SqlParameter("@c1", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt1
    };
    sqlComm.Parameters.Add(param);

    // Parameter 2
    SqlParameter param = new SqlParameter("@c2", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt2
    };
    sqlComm.Parameters.Add(param);

    // Call Stored Procedure
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sqlComm;
    da.Fill(ds);
}

SqlDbType.Structuredé mandatório.

Observe que não sou um especialista em .Net e existem outras maneiras melhores de escrever e usar esse código.

4. Parâmetros de tipo Json (desde o SQL Server 2016)

Com alterações mínimas, você pode declarar um parâmetro NVARCHAR(MAX) com uma string json.

parâmetros json

DECLARE @C1 NVARCHAR(MAX);
SET @jsonC1 =  
N'[
    { "col" : "10"}, { "col" : "15"}, { "col" : "13"}, { "col" : "14"}
]';
DECLARE @C2 NVARCHAR(MAX);
SET @jsonC2 =  
N'[
    { "col" : "C21"}, { "col" : "B21"}
]';

Consulta

SELECT * 
FROM dbo.FinalStatus f
WHERE 
EXISTS (
    SELECT 1 FROM OPENJSON(@C1) WITH(col nvarchar(15)) AS c1 WHERE c1.col = f.Col1
)
OR
EXISTS (
    SELECT 1 FROM OPENJSON(@C2) WITH(col nvarchar(15)) AS c2 WHERE c2.col = f.Col2 
);

Observe que o OPENJSON requer o SQL Server 2016.

R Evans · Answer 2 · 2016-07-20T10:14:34+08:00

R Evans

2016-07-20T10:14:34+08:002016-07-20T10:14:34+08:00

Em algum momento, você tem que dividir essa string. Antes de chamar seu procedimento como sugerido por @MguerraTorres, ou no procedimento. Aqui está um bom exemplo de uma função de divisão de string: http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

Essa função retorna um tipo de dados de tabela. Você pode usar essa tabela para consultar as linhas que estão na tabela dividida.

select what_i_want from my_table where value in (select value from split_table )

O SQL Server 2016 tem uma função STRING_SPLIT integrada.

3

jyao · Answer 3 · 2016-07-20T13:29:02+08:00

Uma solução rápida aqui é alterar sua cláusula where de '=' para 'in' (como um exemplo)

where col1 = @c1 --> where col1 in (@c1)

e altere o @c1 para uma lista de valores com aspas simples, por exemplo

@c1 = '1,2,3' --> '1','2','3'

Então aqui está o script de procedimento modificado

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL, 
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)
    declare @qry varchar(3000);


    SET @c1 = ''''+replace(@col1,',', ''',''')+'''' -- make @col1 to be a string in signle quote
    SET @c2 =''''+replace(@col2,',', ''',''')+''''  -- make @col1 to be a string in signle quote 
    select @c1=iif(@c1 is null, 'col1', @c1), @c2=iif(@c2 is null, 'col2', @c2);

    set @qry='
    SELECT 
        Col2, Col1, 
        LastUpdatedDate, Col3
    FROM 
        dbo.FinalStatus
    WHERE 
        (Col1 in ('+@c1+') OR 1=1)
        AND (Col2 in ('+@c2+') OR 2=2)
    ORDER BY 
        LastUpdatedDate DESC';

   if @c1 <> 'col1' -- i.e. @col1 parameter is not null, remove OR 1=1
     set @qry = replace(@qry, 'OR 1=1', '');
   IF @C2 <> 'col2' -- i.e. @col2 parameter is not null, remove OR 2=2
     set @qry = replace(@qry, 'OR 2=2', '');

    print @qry -- you can comment out this line, it is for debugging
    exec (@qry)
END

Agora você pode usar vários valores em seus parâmetros

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL;
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21,c21'; --note NO space before or after comma.

Solomon Rutzky · Answer 4 · 2016-07-21T08:15:42+08:00

One option that entails minimal change in the calling code is to pass in simplistic XML. This is very similar to Option 2 in @JulienVavasseur's answer, except that it keeps the two input parameters separate. The thought here is that it should be easy enough to convince the developer to change the existing String.Join(',', _SomeIntArray) to instead be something like: '<v>' + String.Join('</v><v>', _SomeIntArray) + '</v>'. Then you could use them in a query similar to the following, which I left as is since it is a runnable example:

DECLARE @Input1 XML,
        @Input2 XML;

SET @Input1 = '<v>231</v><v>175</v>';
SET @Input2 = '<v>256</v><v>4</v>';

-- Both commented out = 800
-- Only @Input2 commented out = 119
-- Only @Input1 commented out = 390
-- Neither commented out = 63

SELECT so.*, in1.col.value('./text()[1]', 'INT')
FROM   master.sys.columns so
LEFT JOIN @Input1.nodes('v') in1(col)
       ON in1.col.value('./text()[1]', 'INT') = so.[system_type_id]
LEFT JOIN @Input2.nodes('v') in2(col)
       ON in2.col.value('./text()[1]', 'INT') = so.[max_length]
WHERE (@Input1 IS NULL OR in1.col.value('./text()[1]', 'INT') IS NOT NULL)
AND   (@Input2 IS NULL OR in2.col.value('./text()[1]', 'INT') IS NOT NULL);

SQLDevDBA · Answer 5 · 2016-07-20T10:04:57+08:00

SQLDevDBA

2016-07-20T10:04:57+08:002016-07-20T10:04:57+08:00

Não é possível executar um procedimento armazenado duas vezes para uma única entrada.

Eu sugiro um CURSOR ou Loop que executará o procedimento armazenado uma vez para cada linha desejada na tabela.

Cada iteração de loop irá: 1) Definir duas variáveis para serem iguais a Col1 e Col2 2) Executar o procedimento com as novas Variáveis como Parâmetros de Entrada.

--Loop Code Here
--For each record returned by your FinalStatus table:
SET @Value1 =Col1;
SET @Value2 =Col2;
--Then execute the stored proc with those values
EXEC [dbo].[FindResult] @Value1,@value2
--End Loop Code

0

Ashmita Jain · Answer 6 · 2017-03-30T21:10:11+08:00

Ashmita Jain

2017-03-30T21:10:11+08:002017-03-30T21:10:11+08:00

The simplest way I found was to use FIND_IN_SET:

FIND_IN_SET(column_name, values)

For example define values=(1,2,3) and execute:

SELECT name WHERE FIND_IN_SET(id, values)

-2

Passando valores separados por vírgula para parâmetros no procedimento armazenado

1. Parâmetros separados por vírgulas

Consulta de amostra (usando conversão XML):

Resultado:

Consulta principal:

Resultado:

2. Parâmetro(s) XML

3. Tipos de Tabela Definidos pelo Usuário

Tipo de tabela

Procedimento armazenado com parâmetros de tipo de tabela

Chamada de procedimento armazenado com parâmetros de tipo de tabela

Resultado:

Chamada .Net C#

4. Parâmetros de tipo Json (desde o SQL Server 2016)

parâmetros json

Consulta

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Passando valores separados por vírgula para parâmetros no procedimento armazenado

6 respostas

1. Parâmetros separados por vírgulas

Consulta de amostra (usando conversão XML):

Resultado:

Consulta principal:

Resultado:

2. Parâmetro(s) XML

3. Tipos de Tabela Definidos pelo Usuário

Tipo de tabela

Procedimento armazenado com parâmetros de tipo de tabela

Chamada de procedimento armazenado com parâmetros de tipo de tabela

Resultado:

Chamada .Net C#

4. Parâmetros de tipo Json (desde o SQL Server 2016)

parâmetros json

Consulta

relate perguntas