SQLTeam.com | Weblogs | Forums

T-sql 2012 make the where clause work

#1

I have the following t-sql 2012 and I would like to create a backup table by using the where clause. I
want the where clause since I want to limit the number of rows that are in the backup table.

Thus would you show me how to modify the sql listed below so that it will run correctly in sql server management studio?

declare @qry nvarchar(max);
set @qry='SELECT * into ContactLog_'
+ cast(year(getdate()) as varchar(4))
+ cast(month(getdate()) as varchar(2))
+ cast(day(getdate()) as varchar(2))
+ 'FROM ContactLog
+ 'where [module]=''StudentServices''

  • and [contactMode] = ''Parent/Guardian''
    • and [contactType] = 2
  • and text like ''%ss1.org%''
  • and text like ''%/letter.aspx%''
  • and datetimestamp >= ''2008-08-01 00:00:00.00''
    exec (@qry)
#2

You do have the WHERE clause in your query, and except for minor syntax errors it should work. See the following

DECLARE @qry NVARCHAR(MAX);
SET @qry
    = N'SELECT * into ContactLog_' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + CAST(MONTH(GETDATE()) AS VARCHAR(2))
      + CAST(DAY(GETDATE()) AS VARCHAR(2)) + N' FROM ContactLog '
      + N' WHERE [module]=''StudentServices''
	AND [contactMode] = ''Parent/Guardian''
	AND [contactType] = 2
	AND text LIKE ''%ss1.org%''
	AND text LIKE ''%/letter.aspx%''
	AND datetimestamp >= ''2008-08-01 00:00:00.00''';

EXEC (@qry);

There are security risks associated with dynamic SQL. If you are not aware of those, please do look it up.

#3

This would evaluate into strange looking "dates"; ex. today would be 2019416, january 1st next year would be 202011. I'm assuming you're going to use this table later on, and if you try to access ex. ContactLog_20190416 it would fail. And if your program doesn't handle this properly, you might stumble into some security issues (like @JamesK mentioned). There are several ways to get yyyymmdd - this is one way:

convert(char(8),getdate(),112)