Question

JaguarsJag on Fri, 12 Feb 2016 21:24:47


In a query like below, how to make the variable in the query as dynamic ? Please anyone explain step by step

there are set of @CompanyERpId will be provided like (23. 2454, 876....) randomly.

Declare @CompanyERpId varchar(50)
Set @CompanyERpId = '12345'


Select a.UserId + '|"' + replace(a.UserId,'brightstar\','') + '"|"' + isnull(a.FirstName,'') + ' ' + isnull(a.LastName,'') + '"|"' + case when a.IsLocked = 0 then 'ACTIVE' else 'INACTIVE' end + '"|"' + d.Name  + '"|"' + e.Name  + '"'
from adm.[user] a
inner join adm.userbycompany b on a.UserId = b.Userid
inner join adm.claimsbyuser c on a.userid = c.UserId
inner join adm.Claim e on c.ClaimId = e.id
inner join adm.company d on b.companyId = d.id
where (companyErpId = @CompanyERpId or @CompanyERpId = '0')
order by d.CompanyERPId, isnull(a.FirstName,'') + ' ' + isnull(a.LastName,'')


MBH


Sponsored



Replies

Sary Awwad on Fri, 12 Feb 2016 21:48:23


Hi,

Create an SSIS variable in your package and then modify your declare statement to :

Declare @CompanyERpId varchar(50)
Set @CompanyERpId = ?

if you are using a source component you need to map the variable using parameters in the OLEDB source editor.

Or if you are using an Execute SQL task, go to parameter mapping and map your variable.

Thanks,