Skip to content
2 Comments leave one →
  1. February 12, 2009 12:14 PM

    USE [db]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create proc [dbo].[procUtilsMeta_GetProcMetaData]
    @domain_user varchar(200) ,
    @ret int OUT,
    @msg varchar(200) = null out ,
    @procName varchar(200)

    as
    declare @debugmsg varchar(1000)

    begin –proc

    begin try

    select PARAMETER_NAME as ‘COLUMN_NAME’, DATA_TYPE , CHARACTER_MAXIMUM_LENGTH AS ‘MAX_LENGTH’, IS_RESULT , PARAMETER_MODE from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME=@procName

    set @msg = ‘ meta data for procedure ‘ + @procName + ‘ retrieved successfully’
    set @ret =0

    END TRY

    BEGIN CATCH
    PRINT ‘In CATCH block.
    Error number: ‘ + CAST(ERROR_NUMBER() AS varchar(10)) + ‘
    Error message: ‘ + ERROR_MESSAGE() + ‘
    Error severity: ‘ + CAST(ERROR_SEVERITY() AS varchar(10)) + ‘
    Error state: ‘ + CAST(ERROR_STATE() AS varchar(10)) + ‘
    XACT_STATE: ‘ + CAST(XACT_STATE() AS varchar(10));

    set @msg = ‘ Failed to retrieve meta data for procedure ‘ + @procName
    set @debugmsg = ‘: Error number: ‘ + CAST(ERROR_NUMBER() AS varchar(10)) +
    ‘Error message: ‘ + ERROR_MESSAGE() + ‘Error severity: ‘ +
    CAST(ERROR_SEVERITY() AS varchar(10)) + ‘Error state: ‘ +
    CAST(ERROR_STATE() AS varchar(10)) + ‘XACT_STATE: ‘ +
    CAST(XACT_STATE() AS varchar(10))

    set @ret = 1

    END CATCH
    end –proc

Trackbacks

  1. Automated SQL Server Back: Poor Man’s Edition « Dangling On The Shoulders Of Giants

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives


  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: