Wednesday, 24 June 2009

SQL return the insert row id


   1:  alter procedure [dbo].[usp_SomeName] (
   2:  @value varchar(100)
   3:  ) as
   4:  declare @id int
   5:  create table #out(id int, value varchar(100))
   6:  INSERT INTO test2
   7:  (value)
   8:  output inserted.* into #out
   9:  VALUES
  10:  (@value)
  11:  return (select top 1 id from #out)

SQL Error catching


   1:  begin catch
   2:  DECLARE
   3:  @ErrorMessage NVARCHAR(4000),
   4:  @ErrorNumber INT,
   5:  @ErrorSeverity INT,
   6:  @ErrorState INT,
   7:  @ErrorLine INT,
   8:  @ErrorProcedure NVARCHAR(200);
   9:  -- Assign variables to error-handling functions that capture information for RAISERROR.
  10:  SELECT
  11:  @ErrorNumber = ERROR_NUMBER(),
  12:  @ErrorSeverity = ERROR_SEVERITY(),
  13:  @ErrorState = ERROR_STATE(),
  14:  @ErrorLine = ERROR_LINE(),
  15:  @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
  16:  -- Build the message string that will contain original error information.
  17:  SELECT @ErrorMessage = 'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();
  18:  -- Raise an error: msg_str parameter of RAISERROR will contain the original error information.
  19:  RAISERROR
  20:  (
  21:  @ErrorMessage,
  22:  @ErrorSeverity,
  23:  1,
  24:  @ErrorNumber, -- parameter: original error number.
  25:  @ErrorSeverity, -- parameter: original error severity.
  26:  @ErrorState, -- parameter: original error state.
  27:  @ErrorProcedure, -- parameter: original error procedure name.
  28:  @ErrorLine -- parameter: original error line number.
  29:  );
  30:  end catch

Wednesday, 17 June 2009

SQL search for object by name, for one database only


   1:  SELECT name, type_desc, modify_date
   2:  FROM sys.objects
   3:  WHERE OBJECT_DEFINITION(object_id) LIKE '%somestring%'