SELECT Query selecting values based on a value in another table

Question

I have 2 tables

Account(AccountId, Encoding)
DeviceAccountMap(AccountId, DeviceId)

Now I need to fetch the devices from the DeviceAccountMap. I pass a list of AccountId to a stored procedure and while fetching the DeviceId from the DeviceAccountMap table I need to compare the Encoding value for each account with a particular value.

Which is the easy way to do this? I am totally lost.

The select clause in the stored procedure will look something like this:

DECLARE @Accounts [usp].[Array]

and [usp].[Array] is defined as below

CREATE TYPE [usp].[Array] AS TABLE
(
    Value VARCHAR(36)   NULL
)

SELECT 
   DeviceId,
   AccountEncoding = A.Encoding
FROM 
   usp.DeviceControllerAccountMap DCAM
INNER JOIN 
   usp.Account A ON (DCAM.AccountId = A.AccountId)
WHERE 
   DCAM.AccountId IN (SELECT Value From @AccountIds) 
   AND DCAM.IsShared = 1 
   AND AccountEncoding LIKE A.Encoding + '.%'

In other words I need to fetch the encoding value for each account and use that in this where clause.

I am thinking the last line is the issue. try "AND A.Encoding LIKE 'whatever you are comparing to'" — SteveB, Apr 04 '14 at 15:01
first of all your UDT `[usp].[Array]` with column `Value` allow NULLS to be inserted and you are using `IN` operator to check values in that column, It will produce unexpected results if there are any NULL values in that column. Make it not nullable OR use EXISTS operator to check values in that column. — M.Ali, Apr 04 '14 at 15:04

score 1 · Answer 1 · answered Apr 04 '14 at 16:15

1

The following line is completely unnecessary. The JOIN to Account does this filter for you.

   DCAM.AccountId IN (SELECT Value From @AccountIds)

Or am I missing something?

answered Apr 04 '14 at 16:15

Metaphor

6,157
10
54
77

score 1 · Accepted Answer · edited May 23 '17 at 12:05

So you can look up information on Table-Valued Parameters (TVPs) in T-SQL.

Here is an article by Erland Sommarskog.

You can refer to this StackOverflow answer to see an example of C# code calling a stored procedure that uses a TVP. I believe TVPs require SQL Server 2008 or higher.

TVPs, as far as I understand, provide a way to make your own data type in sql server that gets treated as if it was a table. You're doing this when you declare your Array type and then when you use the @AccountIds in your stored procedure's select statement.

CREATE TYPE [usp].[Array] AS TABLE    -- maybe choose a more descriptive name than 'Array'
(
    Value VARCHAR(36) NULL            -- choose a more descriptive name than 'Value'
)

CREATE PROCEDURE [usp].[your_procedure_name]
    @AccountIds [usp].[Array] READONLY       -- use TVP as a parameter
AS
SELECT  …

It is not clear form your question details whether you also mean to have a parameter in the stored procedure for the Encoding. It seems like you're looking for accounts whose Encodings start with a period '.'.

So first, create your type, like you're doing. Then create your stored procedure. Then test your stored procedure, something like this:

DECLARE @mylist Array                           -- make TVP sample data 
INSERT @mylist(Value) VALUES(1),(11),(27),(123) -- insert some values
exec your_procedure_name @mylist                -- run stored procedure

SELECT Query selecting values based on a value in another table

2 Answers2