CODEWITHSUNDEEP
sqlsql-server
ckv
ckv

Reputation: 10830

SELECT Query selecting values based on a value in another table

I have 2 tables

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.

Upvotes: 0

Views: 1367

Answers (2)

Will N
Will N

Reputation: 1610

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

Upvotes: 1

Metaphor
Metaphor

Reputation: 6405

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?

Upvotes: 1

Related Questions