SQL Server (T-SQL)

Cursor Template

2008-07-01T13:22:00Z

This is the cursor template that I use... it works better than the default one, and allows BREAK and CONTINUE easily

 DECLARE curName CURSOR KEYSET FOR 
 select *
  from mytablename
DECLARE variables
OPEN curName
WHILE 1=1 
BEGIN
 FETCH NEXT FROM curSopUpdate23 INTO @sopnumbe,@soptype,@dtOnTruck, @dtOnJob
 if @@fetch_status <> 0 begin
  break
 end 


END
CLOSE curName
DEALLOCATE curName

Grant permissions cursor

2008-06-13T12:43:00Z

DECLARE @name varchar(255),
@sql varchar(255)

DECLARE curName CURSOR KEYSET FOR
      select name
            from sys.objects
            where type = 'p'

OPEN curName

WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @name

      if @@fetch_status <> 0 begin
            break
      end

SET @sql = 'grant exec on ' + @name + ' to chemsys'
exec (@sql)
END

CLOSE curName
DEALLOCATE curName
GO

Returning the current time in SQL Server

2008-05-15T12:10:00Z

From a recent email:

I am trying to use SmartList Builder to build a query where a calculation is required showing the number of days an A/R transaction is past due. Obviously, I have the DOCDATE from RM20101 but I need a column that tells me the current date so I can make this comparison…and I am not sure if there is a SQL Server table that I can pull in that shows this information to me. I am pretty certain that there is not a GP table with current date in it just sitting there by default.

Answer:

Select getdate(), convert(varchar(10),getdate(),101)

Getdate() returns a date/time, the second part will strip the time