T-SQL Stored Procedure

Question

I am trying to understand this code for my Uni project.

As I am new to using stored procedures, could you please help me out?

SET @SQL = @SQL + CHAR(13)   +  'DELETE fROM  table_name WHERE [Date]  ='''+ CONVERT(varchar, @DATE, 120) + ''';'

I am not clear about usage of single quotes here.

Thanks

Answer 1

Just as a tip, conversions from character strings may vary depending on regional settings. When using strings for dates, if you use the format YYYYMMDD with no dashes, it seems to convert correctly regardless of regional settings. I always use that format. Makes life simpler. To include time you'd use for example:

'20150409 09:44:00.000'

This cuts the risk of mixing up month and day etc :)

Answer 2

'' within a string is an escape sequence for a single quote. So what is being added to the @SQL string will end up looking like this:

DELETE FROM table_name WHERE [Date] = 'SomeDateHere';

I'd have to look up what date format 120 is. Dates in certain formats can be implicitly converted in SQL server. For instance this is valid:

DELETE FROM table_name WHERE [Date] = '2015-04-09';