Примеры с SQL

CREATE FUNCTION dbo.Check_OKPO (@OKPO VARCHAR(11))
RETURNS INT
AS
BEGIN
  DECLARE @I INT, @S INT, @SUMMA INT
  SET @OKPO = LTRIM(RTRIM(@OKPO))
  IF (@OKPO IS NULL OR @OKPO = '') RETURN(1)
  IF LEN(@OKPO) < 8 
    SET @OKPO = RIGHT('00000000' + @OKPO, 8)
  IF LEN(@OKPO) > 8 
    SET @OKPO = RIGHT('00' + @OKPO, 10)
  SET @I = 1
  SET @SUMMA = 0
  WHILE @I < LEN(@OKPO)
  BEGIN
    SET @SUMMA = @SUMMA + CAST(SUBSTRING(@OKPO, @I, 1) AS INT) * @I
    SET @I = @I + 1
  END
  SET @S = @SUMMA % 11
  IF @S = 10 SET @S = 0
  IF @S <> CAST(RIGHT(@OKPO, 1) AS INT) RETURN(1)
  RETURN(0)
END

SELECT CAST(FLOOR(CAST(GetDate() as float)) as Datetime)

-- Округлить дату
declare @date datetime
set @date=CONVERT(varchar(8), GETDATE(), 112)
-- Изъять время из даты
declare @time datetime
set @time=CONVERT(varchar(8), GETDATE(), 108)

-- Trim всех полей в БД
DECLARE @tName varchar(1000), @cName varchar(1000)
DECLARE cur CURSOR FOR
SELECT c.TABLE_NAME,
       c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
     JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_CATALOG = t.TABLE_CATALOG AND
     c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' AND -- исключаем View
      DATA_TYPE = 'varchar' AND       -- выбираем только текстовые поля
      NOT c.TABLE_NAME LIKE '%_OLD'   -- если надо ограничение на обрабатываемые таблицы
order by c.TABLE_NAME,
         c.COLUMN_NAME
OPEN cur

FETCH NEXT FROM cur INTO @tName, @cName
     
WHILE @@FETCH_STATUS = 0
BEGIN
  exec ('UPDATE ['+@tName+'] SET "'+@cName+'"=LTRIM(RTRIM("'+@cName+'"))')
  FETCH NEXT FROM cur INTO @tName, @cName
END

CLOSE cur
DEALLOCATE cur

-- Количество строк во ВСЕХ таблицах
create table #svv_tab_row_Count (ID int IDENTITY not null, Name varchar(100), col int)
declare @i int, @j int
declare @t varchar(100)
insert into #svv_tab_row_Count (Name)
select Table_Name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' and TABLE_TYPE='BASE TABLE'
SET @i = 1
SET @j = 1
WHILE (@j < 2)
BEGIN
 IF NOT EXISTS(select * from #svv_tab_row_Count where ID=@i) SET @j = 2
 ELSE
 BEGIN
	select @t = Name from #svv_tab_row_Count where ID=@i
  exec ('update #svv_tab_row_Count set col=(select Count(*) from ['+@t+']) where id='+@i+'')
 end
 SET @i = @i + 1
END
select * from #svv_tab_row_Count
drop table #svv_tab_row_Count

SELECT * FROM sysdatabases WHERE status&100000=0 AND sid<>1 ORDER BY name

DECLARE @i INT
SET @i = 1

WHILE (@i < 500)
BEGIN
// здесь пишется выполняемый в цикле код
SET @i = @i + 1
END

 ADOQuery1.Close;
 ADOQuery1.SQL.Text:='SELECT * FROM TAB1 WHERE DATE>='+
                     QuotedStr(FormatDateTime('yyyy-mm-dd 00:00:00', dtpFrom.Date))+
                     ' AND DATE<='+
                     QuotedStr(FormatDateTime('yyyy-mm-dd 23:59:59', dtpTo.Date));
 ADOQuery1.Open;