I working with SQL Server a lot and have certain statements that I write and often have to search for templates for. Below is one, well actually two that I have to check for the usage when I use them.
The snippet below creates a cursor that can be used for generating several “UPDATE” statements. The cursor template is simple to use and a good quick reminder. Within that template I also use the CAST to change a date time value to ‘MM/DD/YYYY’.
Below is the statement written in TSQL, and I am placing it here for a way to quickly find it again.
declare my_cursor Cursor FOR select e.guidfield,e.hiredate from Millennium..EInfo e INNER JOIN DbTools..Sheet1 s on (e.ssn=s.ssn) where e.newhireReportDate is null order by e.ssn declare @guid varchar(128), @hiredate datetime open my_cursor fetch next from my_cursor into @guid,@hiredate while(@@FETCH_STATUS<>-1) BEGIN DECLARE @sql varchar(500) set @sql = 'UPDATE EInfo SET newhiredate=''' + convert(varchar(12),@hiredate,101) + ''' WHERE guidfield=''' + @guid + ''' AND newhireReportDate is null' print @sql fetch next from my_cursor into @guid,@hiredate END close my_cursor deallocate my_cursor