Passing parameter to Execute sql task

• vipin_jha123

  Hall of Fame

  Points: 3085

  More actions

  August 5, 2015 at 11:03 pm

  #149005

  Hi

  I have one temp table as shone below having data with bigint data type

  In temp table there rae data which start with 1 and 2.

  I want to select only those record which start with 1

  Zone is a parameter to the Execute sql task in ssis package

  I have created sample code to test when I am running my query I am not getting anything

  create table #temp

  ( zoneid bigint

  )

  insert into #temp values(100000000000000000)

  insert into #temp values(100000000000000000)

  insert into #temp values(100000000000000000)

  insert into #temp values(100000000000000000)

  insert into #temp values(200000000000000000)

  insert into #temp values(200000000000000000)

  select * from #temp

  declare @zone bigint

  set @zone='100000000000000000'

  select * FROM #temp

  WHERE zoneid LIKE '@zone%'

  Please suggest what to do with above code

  regards,

  Vipin jha

  Regards,

  Vipin jha

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  August 6, 2015 at 12:09 am

  #1817754

  No need to handle the numbers as a string, just select from the table where it is less than the next value up.

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo..#temp') IS NOT NULL DROP TABLE #temp;

  CREATE TABLE #temp

  (

  zoneid BIGINT

  );

  INSERT INTO #temp VALUES

  (100000000000000000)

  ,(123456789012345678)

  ,(199999999999999999)

  ,(200000000000000000)

  ,(223456789012345678)

  ,(299999999999999999)

  ,(300000000000000000)

  ,(323456789012345678)

  ,(399999999999999999)

  ,(400000000000000000)

  ,(423456789012345678)

  ,(499999999999999999);

  DECLARE @zone BIGINT = 300000000000000000;

  DECLARE @ZONE_INCREMENT BIGINT = 100000000000000000;

  SELECT

  T.zoneid

  FROM #temp T

  WHERE T.zoneid < (@zone + @ZONE_INCREMENT)

  AND T.zoneid >= @zone;

  Results

  zoneid

  --------------------

  300000000000000000

  323456789012345678

  399999999999999999