SLAM_query_by_id

SLAM_query_by_id

Overview

This specialized query operation filters Salesforce data based on IDs provided in a filter table. It automatically handles SOQL length limitations by batching IDs and uses efficient IN clause filtering.

The most common use-cases will apply to Salesforce objects that require filters when querying, e.g. ContentDocumentLink

You may also use this procedure to filter by other text fields, such as external ids. You must ensure any data is properly escaped as required by Salesforce

Performance tip: This procedure is designed for querying specific records when you have a list of IDs. It automatically batches IDs to stay within SOQL length limits and can handle large sets of IDs.

Parameters

Name	Type	Default	Description
@query	NVARCHAR	Required	sObject name or SOQL query string. When just an sObject name is passed, queries all records & fields
@table	VARCHAR	Required	Target SQL table name for results
@filter_table	VARCHAR	Required	Source table containing IDs to filter by (must have an ID column)
@queryall	BIT	0	When 1, includes deleted/archived records
@exists_action	VARCHAR(20)	SLAM_Settings.default_exists_action	Controls the behavior when the destination table name, specified by @table is already used. If NULL, uses the value from SLAM_Settings.default_exists_action (defaulted to 'drop' during initial setup). Valid options: 'drop' - Drop the existing object and create a new one (default) 'rename' - Rename the existing object by appending a timestamp suffix (format: _YYYYMMDDTHHMMSS_MMM) 'fail' - Throws a SQL exception if the destination object already exists

Usage Examples

Query ContentDocumentLink records by ContentDocumentId using a filter table:

-- prepare filter table from ContentDocument records
EXEC dbo.SLAM_query_soap
    @query = 'SELECT Id FROM ContentDocument LIMIT 600',
    @table = 'ContentDocument_query'

CREATE TABLE ContentDocumentLink_filter(ContentDocumentId VARCHAR(18))
INSERT INTO ContentDocumentLink_filter(ContentDocumentId)
SELECT Id FROM ContentDocument_query

EXEC dbo.SLAM_query_by_id
    @query = 'ContentDocumentLink',
    @table = 'ContentDocumentLink_filtered',
    @filter_table = 'ContentDocumentLink_filter'

Query with deleted records included:

-- prepare filter table
CREATE TABLE Lead_filter(Id VARCHAR(18))
INSERT INTO Lead_filter(Id)
    <OUTPUT STATEMENT>

-- execute query
EXEC dbo.SLAM_query_by_id
    @query = 'SELECT Id, Name, Email, Status, IsDeleted FROM Lead',
    @table = 'Lead_filtered',
    @filter_table = 'Lead_filter',
    @queryall = 1

Query specific Contact fields using external ID filter:

-- prepare filter table
CREATE TABLE Contact_External_IDs(External_Id__c VARCHAR(255))
INSERT INTO Contact_External_IDs(External_Id__c)
    VALUES ('ExtId123'), ('ExtId456')

-- execute query
EXEC dbo.SLAM_query_by_id
    @query = 'SELECT Id, Name, Email, Account.Name FROM Contact',
    @table = 'Contact_filtered',
    @filter_table = 'Contact_External_IDs'