Multiple data in where variables in SQL

Question

Multiple data in where variables in SQL

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Well, I'll tell you a little bit. I have a query in SQL like the following >

SELECT * FROM TABLA
WHERE 
Priority <> 'P1'
AND Region = ISNULL(@Region,Region) 
AND PM_Name = ISNULL(@PM,PM_Name)
AND Priority = ISNULL(@Priority, Priority)
AND Status = ISNULL (@Status, Status)

Each condition with @ contains only 1 value, which from the interface are selected from one, the problem starts when they ask me to change the select to select-multiple. So now I can have more than one value in each select.

So far the way I get the data of the select is in a chain for example

cadena = "UNO, DOS, TRES"

At the moment of doing the Select in SQL how could I use these three values to make the filtering of the information. I do not know if I gave myself to explain well, but anything we see.

Parameters to SQL sent them as follows in JS

 params.PM_Name = $("#cb_ProductM option:selected").text(); //nombre del action result
 params.Priority = $("#cb_Priority option:selected").text();
 params.Status = $("#cb_Status option:selected").text();

and received them in the table

<table id="datatable-GeneralTable" data-toggle="table"
                                                   data-url="@Url.Action("LoadGeneral", "Home")"
                                                   data-method="GET"
                                                   data-pagination="true"                                                                                      
data-query-params="queryParams"
                                                       data-buttons-class="primary">

and the action result:

var list = ex.Proc_CargarTabla(Region, PM_Name, Priority, Status).ToList();

sql sql-server select

asked by Richard 21.11.2018 в 16:16

source

2 answers

1

SQL Server allows you to create data types, using the example of the regions you could create this type of data:

 CREATE TYPE [dbo].[REGIONS] AS TABLE(
[REGION] [varchar](100) 
)

The idea is to be able to send a table as a parameter, the SP that you use in your query receives the parameter like this:

 ALTER PROCEDURE [dbo].[NOMBRE_DEL_SP]
     @REGION REGIONS READONLY --IMPORTANTE DEJAR EL READONLY
 AS
 BEGIN
       SELECT * FROM TABLA
       WHERE 
       Priority <> 'P1'
       AND Region IN (SELECT REGION FROM @REGION)
 END

To summarize the data type 'REGIONS' behaves like a table that you can fill from your code and send it.

I have used these types of data from C # several times and I send a dataTable to the SP, in the case of JS I prefer not to give ideas because it is not my strong point, but you can find how to do it.

Luck

answered by 21.11.2018 в 16:50

Multiply Python arrays Are the data of the variables or objects initialized in the constructor method stored in RAM directly?

score 1 · Accepted Answer

Maybe, you could create a tabular function to return the data and use the following logic:

CREATE FUNCTION fnCommaStringToTable
(   
    @Texto VARCHAR(MAX) 
)
RETURNS 
@TablaRetorno TABLE 
(
    [Dato] VARCHAR(MAX) NULL
) 
AS
BEGIN

    DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(50))
    INSERT INTO @t(data) SELECT @Texto  

    INSERT INTO @TablaRetorno
    SELECT  
     O.splitdata 
    FROM
     (
     SELECT *,
     cast('<X>'+replace(F.data,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F
     )F1
     CROSS APPLY
     ( 
     SELECT fdata.D.value('.','varchar(50)') as splitdata 
     FROM f1.xmlfilter.nodes('X') as fdata(D)) O

     RETURN
END
GO


select * from fnCommaStringToTable('UNO, DOS, TRES')

select * from fnCommaStringToTable('UNO')

The above basically takes a string separated by commas and converts it into a line of a table, then you could use it later in your main query (the one you have in the example you put) wheres for the conditions you can use CROSS APPLY to nest the query or also use IN for example:

SELECT * FROM TABLA
WHERE 
Priority <> 'P1'
AND CAST(Region as VARCHAR(MAX)) IN(select dato from fnCommaStringToTable(ISNULL(@Region,Region))) 
...