General Rules

How to use parameters

• Do not use question marks in your SQL statements.

  SELECT * FROM Contacts WHERE Id=?

• Do use variables instead: d

  declare @id as varchar(50) = ?

  SELECT * FROM Contacts WHERE Id=@id

Boomi passes all parameters as String

You can use a questionmark and compare it with number, datetime (or other data types), however SQL with then do an implicit conversion. While this is allowed:

SELECT * FROM Contact WHERE BirthDate = ?

it is really not recommended to do it.

• Do convert all parameters explicitelly to avoid surprises

declare @p1 varchar(50) = ?  -- 
declare @dt datetime = convert( datetime, @p1)
-- select @p1 as StringDate, @dt as SQLDateTime

SELECT * FROM Contact WHERE BirthDate = @dt

More …

Parameter - Best Practices

Pagination and Sorting

Check if record exists

Check using RowCount

Use JSON as a complex parameter