Query Help -

• andrew.radmore

  Grasshopper

  Points: 18

  More actions

  December 2, 2017 at 2:48 am

  #326725

  I need some help with a query I am writing, please see below.  
  On the left join I have the table name IndexValues-11, I need to insert the '11' based upon the month retrieved from the GETDATE()-5 variable.
  Hope somebody could help me figure this out, any help appreciated.

  DECLARE @date DATETIME
  SET @date=GETDATE()-5
  SELECT
    YEAR(@date) AS Year,
    MONTH(@date) AS Month,
    DAY(@date) AS Day
  INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
  SELECT Date, Batch, tab2.DocumentID
  FROM [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-11] tab2
     ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
      LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
  where date >= @date

• Thom A

  SSC Guru

  Points: 99126

  More actions

  December 2, 2017 at 5:13 am

  #1970404

  What do you mean by you need to "insert the 11 based upon the month retrieved from the GETDATE()"? Could you try and explain further? You might need to supply some sample data and DDL, and what the result set (you want to insert) would be based on that data.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  December 2, 2017 at 5:58 am

  #1970405

  As Tom mentioned, we need further information and clarification on the question and the data set. Since the question is related to the actual data, please post the DDL (create table) scripts, sample data as an insert statement, what you have tried so far and the expected results.
  😎

• HappyGeek

  SSCoach

  Points: 18726

  More actions

  December 2, 2017 at 6:29 am

  #1970406

  The query also appears to include two databases and a collation conflict. Your DDL may need to include these too.

  ...

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  December 4, 2017 at 9:53 am

  #1970558

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  December 4, 2017 at 10:02 am

  #1970560

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  December 4, 2017 at 10:10 am

  #1970562

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2017 at 11:11 am

  #1970574

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  December 4, 2017 at 11:35 am

  #1970580

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2017 at 11:44 am

  #1970581

  ScottPletcher - Monday, December 4, 2017 11:34 AM

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  Sorry, your right.  You should always do things differently based on what you are doing rather than establishing one method that if repeated in a different situation doesn't cause an issue.

• Ed Wagner

  SSC Guru

  Points: 287024

  More actions

  December 4, 2017 at 12:11 pm

  #1970587

  Lynn Pettis - Monday, December 4, 2017 11:44 AM

  ScottPletcher - Monday, December 4, 2017 11:34 AM

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  Sorry, your right.  You should always do things differently based on what you are doing rather than establishing one method that if repeated in a different situation doesn't cause an issue.

  Naturally.  Then someone can use the same technique on an nvarchar column and I'm sure they won't change your length of 2. Besides, in the new version, the applications will always prevent the use of the single quote in every field on the screen, so that renders it un-injectable as well.  And of course, using dynamic SQL that's not parameterized has no impact on the plan cache at all.

  Seriously???

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  December 4, 2017 at 12:59 pm

  #1970599

  Ed Wagner - Monday, December 4, 2017 12:11 PM

  Lynn Pettis - Monday, December 4, 2017 11:44 AM

  ScottPletcher - Monday, December 4, 2017 11:34 AM

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  Sorry, your right.  You should always do things differently based on what you are doing rather than establishing one method that if repeated in a different situation doesn't cause an issue.

  Naturally.  Then someone can use the same technique on an nvarchar column and I'm sure they won't change your length of 2. Besides, in the new version, the applications will always prevent the use of the single quote in every field on the screen, so that renders it un-injectable as well.  And of course, using dynamic SQL that's not parameterized has no impact on the plan cache at all.

  Seriously???

  Yes. 
  "Length of 2": Hopefully they won't change it, since changing it would cause an error.
  "Applications": For this particular query, it's difficult to see the need for extremely dynamic code.  At any rate, I'm not going to make the current code have more overhead and/or be more complex than it needs to be just to guess about app changes in the future.  Presumably a developer that requires extreme changes to this code will do what is necessary for those changes.
  "Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway.  So it's removed from the cache the moment there's pressure, and is left if not.  So I don't see any real net difference for that.  Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2017 at 1:22 pm

  #1970606

  ScottPletcher - Monday, December 4, 2017 12:59 PM

  Ed Wagner - Monday, December 4, 2017 12:11 PM

  Lynn Pettis - Monday, December 4, 2017 11:44 AM

  ScottPletcher - Monday, December 4, 2017 11:34 AM

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  Sorry, your right.  You should always do things differently based on what you are doing rather than establishing one method that if repeated in a different situation doesn't cause an issue.

  Naturally.  Then someone can use the same technique on an nvarchar column and I'm sure they won't change your length of 2. Besides, in the new version, the applications will always prevent the use of the single quote in every field on the screen, so that renders it un-injectable as well.  And of course, using dynamic SQL that's not parameterized has no impact on the plan cache at all.

  Seriously???

  Yes. 
  "Length of 2": Hopefully they won't change it, since changing it would cause an error.
  "Applications": For this particular query, it's difficult to see the need for extremely dynamic code.  At any rate, I'm not going to make the current code have more overhead and/or be more complex than it needs to be just to guess about app changes in the future.  Presumably a developer that requires extreme changes to this code will do what is necessary for those changes.
  "Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway.  So it's removed from the cache the moment there's pressure, and is left if not.  So I don't see any real net difference for that.  Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.

  Not sure but you seem to be missing the point.  You may not have a problem switch back and forth between using EXEC (@sql) and EXEC sp_executesql @sql, but what about the less experienced people that may follow in your foot steps?  What if THEY don't understand the difference and use EXEC (@sql) where they should have used EXEC sp_executesql @sql?  Where they should have built the dynamic SQL so it was parameterized? What about those that find THIS thread without the EXEC sp_executesql @sql and they follow your suggestion when they shouldn't?

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  December 4, 2017 at 2:19 pm

  #1970614

  Lynn Pettis - Monday, December 4, 2017 1:22 PM

  ScottPletcher - Monday, December 4, 2017 12:59 PM

  Ed Wagner - Monday, December 4, 2017 12:11 PM

  Lynn Pettis - Monday, December 4, 2017 11:44 AM

  ScottPletcher - Monday, December 4, 2017 11:34 AM

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  Sorry, your right.  You should always do things differently based on what you are doing rather than establishing one method that if repeated in a different situation doesn't cause an issue.

  Naturally.  Then someone can use the same technique on an nvarchar column and I'm sure they won't change your length of 2. Besides, in the new version, the applications will always prevent the use of the single quote in every field on the screen, so that renders it un-injectable as well.  And of course, using dynamic SQL that's not parameterized has no impact on the plan cache at all.

  Seriously???

  Yes. 
  "Length of 2": Hopefully they won't change it, since changing it would cause an error.
  "Applications": For this particular query, it's difficult to see the need for extremely dynamic code.  At any rate, I'm not going to make the current code have more overhead and/or be more complex than it needs to be just to guess about app changes in the future.  Presumably a developer that requires extreme changes to this code will do what is necessary for those changes.
  "Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway.  So it's removed from the cache the moment there's pressure, and is left if not.  So I don't see any real net difference for that.  Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.

  Not sure but you seem to be missing the point.  You may not have a problem switch back and forth between using EXEC (@sql) and EXEC sp_executesql @sql, but what about the less experienced people that may follow in your foot steps?  What if THEY don't understand the difference and use EXEC (@sql) where they should have used EXEC sp_executesql @sql?  Where they should have built the dynamic SQL so it was parameterized? What about those that find THIS thread without the EXEC sp_executesql @sql and they follow your suggestion when they shouldn't?

  Interesting thought.  So then EXEC(@sql) could never be used, ever?  
  What if I need to run dynamic code against a remote server and return a value (as, indeed, I do sometimes need to do), i.e. "EXEC(....) AT [linked_server_name]", when there's no equivalent to that for sp_executesql?
  What if developers *still* allow SQL injection (Si) in even though they used sp_executesql, thinking from this discussion that it's a bullet-proof way to avoid it?  After all, the table name *must* be constructed dynamically here, as the table name can't be a parameter even for sp_executesql.  The main problem with Si is not using EXEC(), it's simply not knowing how to prevent Si.  As a final backstop, presumably (?; hopefully?) they would test for Si during the development cycle, regardless of the implementation method.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 4, 2017 at 2:37 pm

  #1970616

  ScottPletcher - Monday, December 4, 2017 2:19 PM

  Lynn Pettis - Monday, December 4, 2017 1:22 PM

  ScottPletcher - Monday, December 4, 2017 12:59 PM

  Ed Wagner - Monday, December 4, 2017 12:11 PM

  Lynn Pettis - Monday, December 4, 2017 11:44 AM

  ScottPletcher - Monday, December 4, 2017 11:34 AM

  Lynn Pettis - Monday, December 4, 2017 11:11 AM

  ScottPletcher - Monday, December 4, 2017 10:10 AM

  Thom A - Monday, December 4, 2017 10:02 AM

  ScottPletcher - Monday, December 4, 2017 9:53 AM

  I think you'll have to use dynamic SQL.  Btw, it'd be better to combine into a single table clustered on date and just specify date as part of the WHERE.

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= @date
'
EXEC(@sql)

  That won't work Scott. you can't reference @date in your "D-SQL" (in your WHERE clause), it would be outside of scope.

  Yep, overlooked that use of @date, will need to concat the actual date value as well, as I did in the table name:

  
DECLARE @date DATETIME
DECLARE @sql varchar(8000)
SET @date=GETDATE()-5
SELECT
YEAR(@date) AS Year,
MONTH(@date) AS Month,
DAY(@date) AS Day
SET @sql = '
INSERT INTO [Check].[dbo].[Reconciliation] (DocDate, Batch, Material)
SELECT Date, Batch, tab2.DocumentID
FROM [Check].[dbo].[Master] tab1
LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(varchar(2), @date, 1) + '] tab2
ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
LEFT JOIN [DB2017].[dbo].[Documents] tab3 ON tab2.DocumentID = tab3.ID
where date >= ''' + CONVERT(varchar(8), @date, 112) + '''
'
EXEC(@sql)

  Nope, use sp_executesql instead:

  DECLARE @date DATETIME;
DECLARE @sql NVARCHAR(MAX),
        @sqlparm NVARCHAR(MAX) = N'@pdate datetime';

  SET @date = GETDATE() - 5;
  SELECT  YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];
  SET @sql = '
  INSERT INTO [Check].[dbo].[Reconciliation] (
    DocDate
    , Batch
    , Material
  )
  SELECT
    Date
    , Batch
    , tab2.DocumentID
  FROM
    [Check].[dbo].[Master] tab1
    LEFT JOIN [DB2017].[dbo].[IndexValues-' + CONVERT(VARCHAR(2), @date, 1) + '] tab2
      ON tab1.Batch = tab2.TextValue COLLATE Latin1_General_CI_AS
    LEFT JOIN [DB2017].[dbo].[Documents] tab3
      ON tab2.DocumentID = tab3.ID
  where date >= @pdate
  ';

  EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;
  

  Not needed here.  The values derived from a date variable can't cause any SQL injection issue.

  Sorry, your right.  You should always do things differently based on what you are doing rather than establishing one method that if repeated in a different situation doesn't cause an issue.

  Naturally.  Then someone can use the same technique on an nvarchar column and I'm sure they won't change your length of 2. Besides, in the new version, the applications will always prevent the use of the single quote in every field on the screen, so that renders it un-injectable as well.  And of course, using dynamic SQL that's not parameterized has no impact on the plan cache at all.

  Seriously???

  Yes. 
  "Length of 2": Hopefully they won't change it, since changing it would cause an error.
  "Applications": For this particular query, it's difficult to see the need for extremely dynamic code.  At any rate, I'm not going to make the current code have more overhead and/or be more complex than it needs to be just to guess about app changes in the future.  Presumably a developer that requires extreme changes to this code will do what is necessary for those changes.
  "Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway.  So it's removed from the cache the moment there's pressure, and is left if not.  So I don't see any real net difference for that.  Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.

  Not sure but you seem to be missing the point.  You may not have a problem switch back and forth between using EXEC (@sql) and EXEC sp_executesql @sql, but what about the less experienced people that may follow in your foot steps?  What if THEY don't understand the difference and use EXEC (@sql) where they should have used EXEC sp_executesql @sql?  Where they should have built the dynamic SQL so it was parameterized? What about those that find THIS thread without the EXEC sp_executesql @sql and they follow your suggestion when they shouldn't?

  Interesting thought.  So then EXEC(@sql) could never be used, ever?  
  What if I need to run dynamic code against a remote server and return a value (as, indeed, I do sometimes need to do), i.e. "EXEC(....) AT [linked_server_name]", when there's no equivalent to that for sp_executesql?
  What if developers *still* allow SQL injection (Si) in even though they used sp_executesql, thinking from this discussion that it's a bullet-proof way to avoid it?  After all, the table name *must* be constructed dynamically here, as the table name can't be a parameter even for sp_executesql.  The main problem with Si is not using EXEC(), it's simply not knowing how to prevent Si.  As a final backstop, presumably (?; hopefully?) they would test for Si during the development cycle, regardless of the implementation method.

  Your right, it isn't bullet proof, there is more that needs to be done.  I would rather see the EXEC (...) AT [linked_server_name] be seen as a specific exception and to be sure that the code is well commented (as all code should be actually).  I have also found that using sp_executesql in my environment helps with plan reuse because of parameterizing the dynamic SQL since the only changes when the dynamic SQL is generated is in the values passed where variables can be used instead of embedding the values while building the dynamic SQL.

  As others may say, "It depends" and "YMMV."