Picking out several lines from a table SQL server management

Question

Im very new to SQL Server. I'm trying to create a stored procedure that gets me some lines that im asking for. This is datatable i have:

So let's say i would like to show all the vehicles that has TMS1 and Identifier 88 and 89. What should i write? For the moment i have the code

ALTER Procedure [db_ddladmin].[spGetVehicles]@ECU nvarchar(20),


@Identifier nvarchar(20)
  as
  Begin 
  Select * FROM db_ddladmin.View_VehicleReadouts where ECU = @ECU and Identifier = @Identifier
  End

  exec [db_ddladmin].[spGetVehicles] 'EBS7', '91'

This gives me all the vehicles with ECU EBS7 and identifier 91. Let's sat i would identifier 90 also with ECU EBS7. What should i write? I could use an OR statement but that would give me duplicate vehicles which i don't want.

The problem with `or` is just parentheses: `where ECU = @ECU and (Identifier = 90 or Identifier = 91)` Another option is to say `Identifier in (90, 91)` which helps avoid the precedence issue. — shawnt00, May 04 '16 at 18:26

score 0 · Answer 1 · answered May 04 '16 at 10:08

you can use User Defined Table Types:

create  type dbo.YourNames as table (name varchar(10))
go
declare @names dbo.YourNames
insert into @names values ('ECU'),('EBS7')
select * from @names
select * 
from db_ddladmin.View_VehicleReadouts v join @names on v.ECU = @names.name

Edmond Quinton · Answer 2 · 2016-05-04T18:13:03.010

If the number of potential identifiers is unknown then one option is to pass the identifiers as a comma delimited string. Unfortunately you will not be able to directly compare the comma delimited string to your source table identifiers, you will first have to split the string of identifiers out into a separate table. This can be done via a table valued function. I found the following split string code example right here on stack overflow that should do the job.

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[SplitString]
            (@pString NVARCHAR(4000), @pDelimiter NCHAR(1))
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
      WITH E1(N) AS (
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                    ),                          --10E+1 or 10 rows
           E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
           E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
     cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                         -- for both a performance gain and prevention of accidental "overruns"
                     SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                    ),
    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                     SELECT 1 UNION ALL
                     SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                    ),
    cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                     SELECT s.N1,
                            ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                       FROM cteStart s
                    )
    --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
     SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
            Item       = SUBSTRING(@pString, l.N1, l.L1)
       FROM cteLen l
       WHERE SUBSTRING(@pString, l.N1, l.L1) <> ''
    ;
    GO

Once you have added the function to your database you can update your procedure as follow:

    ALTER Procedure [db_ddladmin].[spGetVehicles]
    (
        @ECU nvarchar(20),
        @Identifiers nvarchar(20)
    )
    AS
    Begin 

      SELECT    * 
      FROM  db_ddladmin.View_VehicleReadouts where ECU = @ECU 
                and 
            Identifier IN (SELECT Item  FROM [dbo].[SplitString]( @Identifiers, ',' ) )

    END

You can now execute the procedure as follow:

    EXEC [db_ddladmin].[spGetVehicles] @ECU = 'EBS7', @Identifiers = '88,89'

Wow thank you, will try this and come back and see if it works. Let's say i would like to do the same for the ECUs, let say i have different ECUs also like i have different identifiers, would it work? — Adnan Hossain, May 04 '16 at 10:52
I just noticed an error in the example I gave. The SplitString function requires a second parameter that defines the delimiter for the string in this case a comma. The IN clause should read as follow: Identifier IN (SELECT Item FROM [dbo].[SplitString]( @Identifiers, ',' ) ) I have updated the example to reflect the correction. — Edmond Quinton, May 04 '16 at 18:13
Thank you very much @Edmond. I still have a small problem. When i execute the stored procedure i get a list of all vehicles containing identifier 88,89 in ecu ebs7. But the thing is that it displays doubles. As example vehicle Aura has both identifers 88 and 89 in ECu EBS7 so in the list it is listed twice. once with id 88 and once with 89, is there someway to only display it once?? — Adnan Hossain, May 09 '16 at 08:51
I noticed one thing to, if i create a IN list for ecus, let's say i want all the vehicles with ecu EBS7 and ALM1, when i execute the stored procedure i get all the vehicles that wither has ebs7 or alm1 or both of them. I don't want to show vehicles that has ebs7 but not alm1. I would only like to list all the vehicles that has both alm1 and ebs7 — Adnan Hossain, May 09 '16 at 09:05
If I understand your first question correctly, you want to get distinct records of the vehicles within the ECu that matches your identifier. If that is the case, you can simply use the 'DISTINCT' clause in but you would have to limit your columns to only return the Vehicle and ECu. If you want to include the other columns such as Identifier column that has potentially multiple entries per ECu and vehicle, then you first have to decide how you want to treat these columns. Example if both 88 and 89 is a valid identifier for Aura which of the identifiers should be returned in your record? — Edmond Quinton, May 09 '16 at 13:26
On your second question I will need a bit more information please send me an example result set of what you are getting right now and what you would like to get. — Edmond Quinton, May 09 '16 at 13:27
I wrote my code and the result from executing it in an answer here, please can you look at it? — Adnan Hossain, May 10 '16 at 09:34

Adnan Hossain · Answer 3 · 2016-05-10T09:32:24.947

Edmond, this is how my code lookes like now.

Alter Procedure [db_ddladmin].[spGetVehicles]
(
    @ECU nvarchar(20),
    @Identifiers nvarchar(20),
    @Value nvarchar(20)



)
AS
Begin 

  SELECT    * 
  FROM  db_ddladmin.View_VehicleReadouts where ECU IN (Select Item FROM [dbo].[SplitString](@ECU,',') )
            and 
        Identifier IN (SELECT Item  FROM [dbo].[SplitString]( @Identifiers, ',' ) )
        and
        Value in (Select Item From [dbo].[SplitString]( @Value, ',' ) );


        with items(item) as (
  select Item 
  from dbo.SplitString(@Value, ',') )
 select Name
 from db_ddladmin.View_VehicleReadouts
 where Value in (select items.item from items)
 group by Name
 having count(distinct value) = (select count(*) from items);


END

And i Execute using

EXEC [db_ddladmin].[spGetVehicles] @ECU = 'EBS7, DIS2', @Identifiers = '88,94', @Value = '2459579, 8.0.0'

If i use only one ECU. it works. example here, if i have EBS7 this is the result:

As you can se, it works when i have only one ECU written, but when i try with two ECUs it doesn't work, any suggestions?

For your information all the ecu are linked with some identifiers.And all the identifiers have some specific values which are unique for that identifier for that ECU

score -1 · Answer 4 · edited May 23 '17 at 10:28

-1

You could use a list as parameter delimited by comma. Then on your where clause, you can do:

Identifier IN @IdentifierList

In this way, you are selecting multiple records from a list. Is this something that you want?

i.e.

So let's say i would like to show all the vehicles that has TMS1 and Identifier 88 and 89

EXEC [db_ddladmin].[spGetVehicles] 'TMS1', '88,89'

Reference for IN Clause

Reference for using a list as parameter in IN Clause

edited May 23 '17 at 10:28

Community

1
1

answered May 04 '16 at 09:55

gellowmellow

33
4

Yes this is something i would like. Could you please explain it a bit more? – Adnan Hossain May 04 '16 at 10:05
could you please try it and post the script? – Liya Tansky May 04 '16 at 10:09
Hi sorry, doing something, but have already tried this personally. Check this reference: http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable do the IN clause for the identifier clause similar to the answer to that thread. ;) – gellowmellow May 04 '16 at 10:30
So you actually meant to say dinamic sql – Liya Tansky May 04 '16 at 10:35

Picking out several lines from a table SQL server management

4 Answers4