存储过程示例

CREATE PROCEDURE up_UpdateServiceId @OldServiceId int, @NewServiceId int
AS
DECLARE @SQLContent nvarchar(120)
DECLARE SQLContent_cursor CURSOR FOR(
 SELECT 'UPDATE ' + [name] + ' SET SERVICE_ID = ' + LTRIM(STR(@NewServiceId)) + ' WHERE SERVICE_ID = ' + LTRIM(STR(@OldServiceId))
 FROM sysobjects
 WHERE id IN(
  SELECT id
  FROM syscolumns
  WHERE [name] = 'SERVICE_ID'))

OPEN SQLContent_cursor
FETCH NEXT FROM SQLContent_cursor into @SQLContent
WHILE @@FETCH_STATUS = 0
BEGIN 
 PRINT @SQLContent
 EXECUTE sp_executesql @SQLContent
 FETCH NEXT FROM SQLContent_cursor into @SQLContent
END
CLOSE SQLContent_cursor
DEALLOCATE SQLContent_cursor
GO

从上可以知道如何定义变量,如何使用游标,如何定义存储过程,如何为存储过程传入参数,如何使用函数进行类型之间的转换等知识,Transact_SQL