SQL Server 存储过程管理

1. 打印长文本 SQL

USE master;
GO

IF OBJECT_ID('master.dbo.PrintLongMsgProc') IS NULL
  EXEC ('CREATE PROCEDURE dbo.PrintLongMsgProc AS SET NOCOUNT ON;');
GO
-- =============================================
-- Author:      FUWM
-- Create date: 2019-07-30
-- Description: 打印长文本 SQL
-- =============================================
ALTER PROCEDURE dbo.PrintLongMsgProc
  @msg NVARCHAR(MAX)
AS
BEGIN
  DECLARE @totalLength INT = LEN(@msg);
  DECLARE @start INT = 1, @length INT = CASE WHEN @totalLength >= 4000 THEN 4000 ELSE @totalLength END;
  DECLARE @reversedMsg NVARCHAR(MAX), @charLF NCHAR(1), @lastIdxOfLF INT;

  WHILE @length > 0
  BEGIN
    IF @start + @length - 1 >= @totalLength
    BEGIN
      PRINT SUBSTRING(@msg, @start, @length);
      SET @length = 0;
    END;
    ELSE
    BEGIN
      IF @reversedMsg IS NULL
      BEGIN
        SET @reversedMsg = REVERSE(@msg);
        SET @charLF = NCHAR(10);
      END;

      SET @lastIdxOfLF = @totalLength - CHARINDEX(@charLF, @reversedMsg, @totalLength - @start - @length + 2) + 1;

      IF @lastIdxOfLF = @totalLength + 1 OR @lastIdxOfLF < @start
      BEGIN
        RAISERROR(N'ERROR：SQL 语句太长无法截断', 16, 1);
        RETURN;
      END;

      SET @length = @lastIdxOfLF - @start + 1;

      PRINT SUBSTRING(@msg, @start, @length);

      SET @start = @start + @length;
      SET @length = CASE WHEN @totalLength >= @start THEN CASE WHEN @totalLength - @start >= 3999 THEN 4000 ELSE @totalLength - @start + 1 END
                         ELSE 0
                    END;
    END;
  END;
END;
GO

2. 打印 SQL 对象脚本

USE master;
GO

IF OBJECT_ID('master.dbo.PrintObjectScriptProc') IS NULL
  EXEC ('CREATE PROCEDURE dbo.PrintObjectScriptProc AS SET NOCOUNT ON;');
GO
-- =============================================
-- Author:      FUWM
-- Create date: 2019-07-30
-- Description: 在控制台输出存储过程、函数、触发器等的脚本
-- =============================================
ALTER PROCEDURE dbo.PrintObjectScriptProc
  @dbname    NVARCHAR(MAX) -- 数据库名称
, @name      NVARCHAR(MAX) -- 存储过程、函数、触发器等名称（不包含 schema 名称）
, @isAllowTR TINYINT = 0   -- 是否允许输出触发器
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @fullName NVARCHAR(MAX) = @dbname + N'.dbo.' + @name;
  IF ISNULL(@fullName, N'') = N''
  BEGIN
    RAISERROR(N'缺少 @dbname 或 @name 参数', 16, 1);
    RETURN;
  END;

  DECLARE @sqlToExec NVARCHAR(MAX);

  SET @sqlToExec = N'
  DECLARE @tarObjId INT, @tarObjType VARCHAR(2);

  SELECT @tarObjId = so.id, @tarObjType = so.xtype
  FROM   ' + @dbname + N'.sys.sysobjects so
  WHERE  so.name = ''' + @name + N''';

  IF ISNULL(@tarObjId, 0) = 0
  BEGIN
    RAISERROR(N''>> error: 未能找到“' + @fullName + N'”'', 16, 1);
    RETURN;
  END;' + CASE WHEN ISNULL(@isAllowTR, 0) <= 0 THEN N'
  ELSE IF @tarObjType = ''TR''
  BEGIN
    RAISERROR(N''>> error: “' + @fullName + N'”是触发器'', 16, 1);
    RETURN;
  END'
               ELSE N''
          END + N'
  ELSE IF ISNULL(@tarObjType, '''') NOT IN (''P'', ''FN'', ''TF'', ''IF'', ''TR'', ''V'')
  BEGIN
    RAISERROR(N''>> error: “' + @fullName
                   + N'”不是存储过程、标量函数、表函数、内嵌表函数、触发器或视图'', 16, 1);
    RETURN;
  END;

  DECLARE @text NVARCHAR(MAX), @proc NVARCHAR(MAX);

  SET @text = N''USE ' + @dbname + N';
GO

IF OBJECT_ID(''''' + @fullName
                   + N''''') IS NULL
  EXEC(''''CREATE '' + CASE @tarObjType
                WHEN N''P'' THEN N''PROCEDURE''
                WHEN N''FN'' THEN N''FUNCTION''
                WHEN N''TF'' THEN N''FUNCTION''
                WHEN N''IF'' THEN N''FUNCTION''
                WHEN N''TR'' THEN N''TRIGGER''
                WHEN N''V'' THEN N''VIEW''
           END + N'' dbo.' + @name
                   + N' '' + CASE @tarObjType
                                  WHEN N''P'' THEN N''AS SET NOCOUNT ON''
                                  WHEN N''FN'' THEN N''() RETURNS INT AS BEGIN RETURN 0 END''
                                  WHEN N''TF'' THEN N''() RETURNS @result TABLE (col INT) AS BEGIN RETURN END''
                                  WHEN N''IF'' THEN N''() RETURNS TABLE AS RETURN (SELECT 0 val)''
                                  WHEN N''TR'' THEN N''ON dbo.'' + OBJECT_NAME((SELECT tri.parent_id
                                                                                FROM   ' + @dbname
                   + N'.sys.triggers tri
                                                                                WHERE  tri.object_id = @tarObjId),
                                                                               DB_ID(''' + @dbname
                   + N''')) + N'' AFTER INSERT, DELETE, UPDATE AS BEGIN SET NOCOUNT ON END''
                                  WHEN N''V'' THEN N''AS SELECT 1 val''
                             END + N'';'''')
GO
''
SET @proc = (SELECT   sc.text + ''''
             FROM     ' + @dbname
                   + N'.sys.syscomments sc
             WHERE    sc.id = @tarObjId
             ORDER BY sc.colid
            FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'');

DECLARE @i INT = CHARINDEX(''CREATE'', @proc), @j INT
FIND_CREATE:
IF @i > 0
   AND @i + LEN(''CREATE'') - 1 < LEN(@proc)
   AND SUBSTRING(@proc, @i + LEN(''CREATE''), 1) IN (CHAR(32), CHAR(9))
BEGIN
  SET @j = @i - 1
  WHILE @j > 0 AND SUBSTRING(@proc, @j, 1) IN (CHAR(32), CHAR(9))
  BEGIN
    SET @j = @j - 1
  END
  IF @j <> 0
     AND SUBSTRING(@proc, @j, 1) <> CHAR(10)
  BEGIN
    SET @i = CHARINDEX(''CREATE'', @proc, @i + 1)
    GOTO FIND_CREATE
  END
END
ELSE
  RAISERROR(N''>> error: find key word "create" failed'', 16, 1);

SET @proc = STUFF(@proc, @i, LEN(''CREATE''), ''ALTER'')

SET @text = @text + @proc + CASE WHEN RIGHT(@proc, 1) = CHAR(10) THEN N'''' ELSE N''
'' END         + N''GO
'' ;

  IF OBJECT_ID(''master.dbo.PrintLongMsgProc'', ''P'') IS NOT NULL
    EXEC master.dbo.PrintLongMsgProc @msg = @text;';

  EXEC (@sqlToExec);
END;
GO

3. 批量打印 SQL 对象脚本

USE master;
GO

IF OBJECT_ID('master.dbo.PrintMultiObjectScriptProc') IS NULL
  EXEC ('CREATE PROCEDURE dbo.PrintMultiObjectScriptProc AS SET NOCOUNT ON;');
GO
-- =============================================
-- Author:      FUWM
-- Create date: 2019-07-31
-- Description: 在控制台输出多个存储过程、函数、触发器等的脚本
-- =============================================
ALTER PROCEDURE PrintMultiObjectScriptProc
  @targets   NVARCHAR(MAX) -- 要导出脚本的对象，格式：DATABASE_NAME1.[SCHEMA_NAME1].OBJECT_NAME1,DATABASE_NAME2.[SCHEMA_NAME2].OBJECT_NAME2 ...
, @isAllowTR TINYINT = 0   -- 是否允许输出触发器
AS
BEGIN
  SET NOCOUNT ON;

  IF ISNULL(@targets, N'') = N''
  BEGIN
    RAISERROR(N'缺少 @targets 参数', 16, 1);
    RETURN;
  END;

  DECLARE @tempXML XML, @errMsg NVARCHAR(MAX);

  SET @tempXML = N'<v>' + REPLACE(@targets, N',', N'</v><v>') + N'</v>';

  CREATE TABLE #targets (val NVARCHAR(200) NOT NULL);

  INSERT #targets (val)
  SELECT t.val
  FROM   (SELECT t.c.value(N'.', N'NVARCHAR(200)') val FROM @tempXML.nodes(N'/v') t(c) ) t
  WHERE  t.val <> N'';

  DECLARE @fullname NVARCHAR(200), @dbname NVARCHAR(200), @name NVARCHAR(200), @l INT, @i1 INT, @i2 INT;

  DECLARE myCursor CURSOR FOR(SELECT t.val FROM #targets t);

  OPEN myCursor;

  FETCH NEXT FROM myCursor
  INTO @fullname;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @i1 = CHARINDEX(N'.', @fullname);
    IF @i1 > 1
    BEGIN
      SET @l = LEN(@fullname);
      SET @i2 = @l - CHARINDEX(N'.', REVERSE(@fullname)) + 1;
      IF @i2 < @l AND @i1 <> @i2
      BEGIN
        SET @dbname = SUBSTRING(@fullname, 1, @i1 - 1);
        SET @name = SUBSTRING(@fullname, @i2 + 1, @l - @i2);
        EXEC master.dbo.PrintObjectScriptProc @dbname = @dbname, @name = @name, @isAllowTR = @isAllowTR;
      END;
      ELSE
      BEGIN
        SET @errMsg = N'异常对象“' + @fullname + N'”';
        RAISERROR(@errMsg, 16, 1);
      END;
    END;
    ELSE
    BEGIN
      SET @errMsg = N'异常对象“' + @fullname + N'”';
      RAISERROR(@errMsg, 16, 1);
    END;
    FETCH NEXT FROM myCursor
    INTO @fullname;
  END;

  CLOSE myCursor;
  DEALLOCATE myCursor;
END;
GO

4. 查找 SQL 对象

4.1. 宏 EverythingMacroFunc

USE master;
GO

IF OBJECT_ID('master.dbo.EverythingMacroFunc') IS NULL
  EXEC ('CREATE FUNCTION dbo.EverythingMacroFunc () RETURNS INT AS BEGIN RETURN 0 END;');
GO
-- =============================================
-- Author:      FUWM
-- Create date: 2024-08-20
-- Description: ww:whole-word c:content n:name t:xtype dm:datetime-of-modification
-- =============================================
ALTER FUNCTION dbo.EverythingMacroFunc (@literal NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
  IF CHARINDEX(':', @literal) = 0
    RETURN N't.text LIKE ''%' + @literal + N'%''';

  DECLARE @reversed NVARCHAR(MAX) = REVERSE(@literal), @LEN INT = LEN(@literal);
  DECLARE @rr INT = CHARINDEX(':', @reversed); -- reversed right bound
  DECLARE @rl INT = CHARINDEX(':', @reversed, @rr + 1); -- reversed left bound

  IF @rl = 0
    SET @rl = @LEN + 1;

  DECLARE @KEY NVARCHAR(MAX) = RIGHT(@literal, @rr - 1);
  DECLARE @rightOperand NVARCHAR(MAX), @leftOperand NVARCHAR(MAX), @operator NVARCHAR(MAX);

  WHILE 1 = 1
  BEGIN
    DECLARE @macro NVARCHAR(20) = SUBSTRING(@literal, @LEN - @rl + 2, @rl - @rr - 1);

    IF @macro = N'ww'
    BEGIN
      SET @operator = N'LIKE';
      SET @rightOperand = N'''%[^a-zA-Z0-9_]' + @KEY + N'[^a-zA-Z0-9_]%''';
    END;
    ELSE IF @macro = N'c'
    BEGIN
      SET @leftOperand = N't.text';
      SET @operator = N'LIKE';
    END;
    ELSE IF @macro = N'n'
    BEGIN
      SET @leftOperand = N't.obj';
      SET @operator = N'LIKE';
    END;
    ELSE IF @macro = N't'
    BEGIN
      SET @leftOperand = N't.xtype';
      SET @operator = N'IN';
      SET @rightOperand = N'(''' + REPLACE(@KEY, N',', N''',''') + N''')';
    END;
    ELSE IF @macro = N'dm'
    BEGIN
      SET @leftOperand = N't.modifyDate';
      IF @KEY LIKE '>=%'
      BEGIN
        SET @operator = N'>=';
        SET @rightOperand = N'''' + RIGHT(@KEY, LEN(@KEY) - 2) + N'''';
      END;
      ELSE IF @KEY LIKE '>%'
      BEGIN
        SET @operator = N'>';
        SET @rightOperand = N'''' + RIGHT(@KEY, LEN(@KEY) - 1) + N'''';
      END;
      ELSE IF @KEY LIKE '=%'
      BEGIN
        SET @operator = N'=';
        SET @rightOperand = N'''' + RIGHT(@KEY, LEN(@KEY) - 1) + N'''';
      END;
      ELSE IF @KEY LIKE '<=%'
      BEGIN
        SET @operator = N'<=';
        SET @rightOperand = N'''' + RIGHT(@KEY, LEN(@KEY) - 2) + N'''';
      END;
      ELSE IF @KEY LIKE '<%'
      BEGIN
        SET @operator = N'<';
        SET @rightOperand = N'''' + RIGHT(@KEY, LEN(@KEY) - 1) + N'''';
      END;
      ELSE
      BEGIN
        SET @operator = N'=';
        SET @rightOperand = N'''' + @KEY + N'''';
      END;
    END;
    ELSE
      RETURN N'invalid macro: ' + ISNULL(@macro, N'');

    IF @rl > @LEN
      BREAK;

    SET @rr = @rl;
    SET @rl = CHARINDEX(':', @reversed, @rr + 1);

    IF @rl = 0
      SET @rl = @LEN + 1;
  END;

  RETURN ISNULL(@leftOperand, N't.text') + N' ' + ISNULL(@operator, N'LIKE') + N' ' + ISNULL(@rightOperand, '''%' + @KEY + '%''');
END;
GO

4.2. 主 EverythingProc

USE master;
GO

IF OBJECT_ID('master.dbo.EverythingProc') IS NULL
  EXEC ('CREATE PROCEDURE dbo.EverythingProc AS SET NOCOUNT ON;');
GO
-- =============================================
-- Author:      FUWM
-- Create date: 2019-05-16
-- Description: 在数据库中查找包含指定关键字的存储过程或函数
-- =============================================
ALTER PROCEDURE dbo.EverythingProc
  @srh     NVARCHAR(MAX) -- 要查找的内容，搜索语法请参考 dbo.Tokenize 函数，所支持的宏请参考 dbo.EverythingMacroFunc 函数
, @dbs     NVARCHAR(MAX) -- 在哪些数据库中查找，用空格分隔多个数据库名称
, @orders  NVARCHAR(MAX) -- 查询结果排序
, @options INT = 0x01    -- 选项，位标识；0x01：执行、0x40：DEBUG 标志位、0x80：输出 SQL 脚本
AS
BEGIN
  SET NOCOUNT ON;

  IF ISNULL(@srh, N'') = N''
  BEGIN
    RAISERROR(N'请提供 @srh 参数', 16, 1);
    RETURN;
  END;

  DECLARE @dbTbl TABLE (dbname NVARCHAR(MAX));

  BEGIN
    IF LEN(@dbs) > 0 AND @dbs <> N'*'
    BEGIN
      DECLARE @tempXML XML = N'<v>' + REPLACE(@dbs, N' ', N'</v><v>') + N'</v>';

      INSERT @dbTbl (dbname)
      SELECT t.val
      FROM   (SELECT t.c.value(N'.', N'NVARCHAR(MAX)') val FROM @tempXML.nodes(N'/v') t(c) ) t
      WHERE  t.val <> N'';
    END;

    IF NOT EXISTS (SELECT 1 FROM @dbTbl)
    BEGIN
      INSERT @dbTbl (dbname) SELECT name FROM sys.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
    END;
  END;

  DECLARE @condition NVARCHAR(MAX);

  BEGIN
    DECLARE @tokens TABLE (idx INT, lvl INT, typ VARCHAR(20), body NVARCHAR(MAX));
    INSERT @tokens (idx, lvl, typ, body) SELECT t.idx, t.lvl, t.typ, t.body FROM dbo.Tokenize(@srh) t;

    DECLARE @err NVARCHAR(MAX) = (
              SELECT   TOP 1
                       CASE WHEN LEN(t.body) > 0 THEN t.body ELSE N'unknown' END
              FROM     @tokens t
              WHERE    t.typ = N'Err'
              ORDER BY t.idx DESC
            );

    IF LEN(@err) > 0
    BEGIN
      SET @err = N'tokenize @srh: ' + @err;
      RAISERROR(@err, 16, 1);
      RETURN;
    END;

    SET @condition = (
                       SELECT    CASE WHEN cur.lvl <> ISNULL(prv.lvl, 0) THEN
                         (
                           SELECT CASE WHEN cur.lvl > ISNULL(prv.lvl, 0) THEN N'(' ELSE N')' END
                           FROM   dbo.spt_values spt WITH (NOLOCK)
                           WHERE  spt.type = 'P'
                                  AND spt.number < CASE WHEN cur.lvl > ISNULL(prv.lvl, 0) THEN cur.lvl - ISNULL(prv.lvl, 0)ELSE prv.lvl - cur.lvl END
                           FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)')
                                      ELSE N''
                                 END + CASE cur.typ
                                            WHEN N'Literal' THEN dbo.EverythingMacroFunc(cur.body)
                                            WHEN N'And' THEN N' AND '
                                            WHEN N'Or' THEN N' OR '
                                            WHEN N'Not' THEN N'NOT '
                                            WHEN N'End' THEN N''
                                       END
                       FROM      @tokens cur
                       LEFT JOIN @tokens prv ON prv.idx = cur.idx - 1
                       FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)');

    IF @options & 0x40 = 0x40
      PRINT '>> debug: @condition: ' + @condition;

    IF ISNULL(@condition, N'') = N''
    BEGIN
      RAISERROR(N'请输入需要查找的内容', 16, 1);
      RETURN;
    END;
  END;

  DECLARE @sql NVARCHAR(MAX);
  SET @sql = STUFF(
               (
                 SELECT N'
UNION ALL
SELECT   ''' +         t.dbname
                        + ''' db
       , t.sch
       , t.obj
       , t.xtype
       , CASE t.xtype
              WHEN ''AF'' THEN N''聚合函数 (CLR)''
              WHEN ''C'' THEN N''CHECK 约束''
              WHEN ''D'' THEN N''默认值或 DEFAULT 约束''
              WHEN ''F'' THEN N''FOREIGN KEY 约束''
              WHEN ''L'' THEN N''日志''
              WHEN ''FN'' THEN N''标量函数''
              WHEN ''FS'' THEN N''程序集 (CLR) 标量函数''
              WHEN ''FT'' THEN N''程序集 (CLR) 表值函数''
              WHEN ''IF'' THEN N''内联表函数''
              WHEN ''IT'' THEN N''内部表''
              WHEN ''P'' THEN N''存储过程''
              WHEN ''PC'' THEN N''程序集 (CLR) 存储过程''
              WHEN ''PK'' THEN N''PRIMARY KEY 约束（类型为 K）''
              WHEN ''RF'' THEN N''复制筛选存储过程''
              WHEN ''S'' THEN N''系统表''
              WHEN ''SN'' THEN N''同义词''
              WHEN ''SQ'' THEN N''服务队列''
              WHEN ''TA'' THEN N''程序集 (CLR) DML 触发器''
              WHEN ''TF'' THEN N''表函数''
              WHEN ''TR'' THEN N''SQL DML 触发器''
              WHEN ''TT'' THEN N''表类型''
              WHEN ''U'' THEN N''用户表''
              WHEN ''UQ'' THEN N''UNIQUE 约束（类型为 K）''
              WHEN ''V'' THEN N''视图''
              WHEN ''X'' THEN N''扩展存储过程''
              ELSE t.xtype
         END xtypeDesc
       , t.modifyDate
FROM     (
  SELECT    sch.name COLLATE DATABASE_DEFAULT sch
          , o.name COLLATE DATABASE_DEFAULT obj
          , o.type COLLATE DATABASE_DEFAULT xtype
          , (SELECT sc.text FROM ' + t.dbname
                        + '.sys.syscomments sc WHERE sc.id = o.object_id FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'') text
          , o.modify_date modifyDate
  FROM      ' +        t.dbname + '.sys.objects o
  LEFT JOIN ' +        t.dbname + '.sys.schemas sch ON sch.schema_id = o.schema_id
) t
WHERE    ' +           @condition
                 FROM   @dbTbl t
                 FOR XML PATH(N''), TYPE
               ).value('.', 'NVARCHAR(MAX)')
             , 1
             , LEN(N'
UNION ALL')
             , ''
             ) + N' ORDER BY ' + CASE WHEN @orders <> N'' THEN @orders ELSE N'modifyDate DESC' END;

  IF @options & 0x80 = 0x80
    EXEC dbo.PrintLongMsgProc @msg = @sql;

  IF @options & 0x01 = 0x01
    EXEC (@sql);
END;
GO

5. 获取存储过程的参数

USE $DBName$;
GO

SET NOCOUNT ON;

DECLARE @model TINYINT = $1$; -- 模式；0-简略、1-详细
DECLARE @objId INT = OBJECT_ID('$DBName$.$dbo$.$ProcName$');

CREATE TABLE #map (k sysname NOT NULL, v VARCHAR(200) NOT NULL, hasLen TINYINT NOT NULL, hasPre TINYINT NOT NULL, hasSca TINYINT NOT NULL);

INSERT #map (k, v, hasLen, hasPre, hasSca)
VALUES ('bigint', 'BigInt', 0, 0, 0)
     , ('binary', 'Binary', 1, 0, 0)
     , ('bit', 'Bit', 0, 0, 0)
     , ('char', 'Char', 1, 0, 0)
     , ('date', 'Date', 0, 0, 0)
     , ('datetime', 'DateTime', 0, 0, 0)
     , ('datetime2', 'DateTime2', 0, 0, 1)
     , ('datetimeoffset', 'DateTimeOffset', 0, 0, 1)
     , ('decimal', 'Decimal', 0, 1, 1)
     , ('float', 'Float', 0, 0, 0)
     , ('image', 'Image', 0, 0, 0)
     , ('int', 'Int', 0, 0, 0)
     , ('money', 'Money', 0, 0, 0)
     , ('nchar', 'NChar', 2, 0, 0)
     , ('ntext', 'NText', 0, 0, 0)
     , ('numeric', 'Decimal', 0, 1, 1)
     , ('nvarchar', 'NVarChar', 2, 0, 0)
     , ('real', 'Real', 0, 0, 0)
     , ('smalldatetime', 'SmallDateTime', 0, 0, 0)
     , ('smallint', 'SmallInt', 0, 0, 0)
     , ('smallmoney', 'SmallMoney', 0, 0, 0)
     , ('text', 'Text', 0, 0, 0)
     , ('time', 'Time', 0, 0, 1)
     , ('timestamp', 'Timestamp', 0, 0, 0)
     , ('tinyint', 'TinyInt', 0, 0, 0)
     , ('uniqueidentifier', 'UniqueIdentifier', 0, 0, 0)
     , ('varbinary', 'VarBinary', 1, 0, 0)
     , ('varchar', 'VarChar', 1, 0, 0)
     , ('xml', 'Xml', 0, 0, 0);

SELECT     sParams.parameter_id paramId
         , sParams.name paramName
         , sTypes.name typeName
         , sParams.max_length len
         , sParams.precision prec
         , sParams.scale scale
         , sParams.is_output isOut
         , CASE WHEN sParams.parameter_id = 0 THEN 1 ELSE 0 END isReturn
         , CASE WHEN sParams.parameter_id = 0 THEN
                  'returnSqlParam = new SqlParameter() { SqlDbType = SqlDbType.' + ISNULL(map.v, '') + ', '
                  + CASE WHEN map.hasLen <> 0 THEN
                           'Size = ' + CASE WHEN sParams.max_length = -1 THEN '-1' ELSE CONVERT(VARCHAR(20), sParams.max_length / map.hasLen)END + ', '
                         ELSE ''
                    END + CASE WHEN map.hasPre <> 0 THEN 'Precision = ' + CONVERT(VARCHAR(20), sParams.precision) + ', ' ELSE '' END
                  + CASE WHEN map.hasSca <> 0 THEN 'Scale = ' + CONVERT(VARCHAR(20), sParams.scale) + ', ' ELSE '' END
                  + 'Direction = ParameterDirection.ReturnValue }'
                WHEN sParams.is_output = 1 THEN
                  RIGHT(sParams.name, LEN(sParams.name) - 1) + 'SqlParam = new SqlParameter("' + sParams.name + '", SqlDbType.' + ISNULL(map.v, '') + ') { '
                  + CASE WHEN map.hasLen <> 0 THEN
                           'Size = ' + CASE WHEN sParams.max_length = -1 THEN '-1' ELSE CONVERT(VARCHAR(20), sParams.max_length / map.hasLen)END + ', '
                         ELSE ''
                    END + CASE WHEN map.hasPre <> 0 THEN 'Precision = ' + CONVERT(VARCHAR(20), sParams.precision) + ', ' ELSE '' END
                  + CASE WHEN map.hasSca <> 0 THEN 'Scale = ' + CONVERT(VARCHAR(20), sParams.scale) + ', ' ELSE '' END + 'Direction = ParameterDirection.Output }'
                ELSE
                  'new SqlParameter("' + sParams.name + '", ' + RIGHT(sParams.name, LEN(sParams.name) - 1) + ')'
                  + CASE WHEN @model = 1 AND (map.v IS NOT NULL OR map.hasLen <> 0 OR map.hasPre <> 0 OR map.hasSca <> 0) THEN
                           ' { '
                           + CASE WHEN map.v IS NOT NULL THEN
                                    'SqlDbType = SqlDbType.' + map.v + CASE WHEN map.hasLen <> 0 OR map.hasPre <> 0 OR map.hasSca <> 0 THEN ', ' ELSE '' END
                                  ELSE ''
                             END
                           + CASE WHEN map.hasLen <> 0 THEN
                                    'Size = ' + CASE WHEN sParams.max_length = -1 THEN '-1' ELSE CONVERT(VARCHAR(20), sParams.max_length / map.hasLen)END
                                    + CASE WHEN map.hasPre <> 0 OR map.hasSca <> 0 THEN ', ' ELSE '' END
                                  ELSE ''
                             END
                           + CASE WHEN map.hasPre <> 0 THEN
                                    'Precision = ' + CONVERT(VARCHAR(20), sParams.precision) + CASE WHEN map.hasSca <> 0 THEN ', ' ELSE '' END
                                  ELSE ''
                             END + CASE WHEN map.hasSca <> 0 THEN 'Scale = ' + CONVERT(VARCHAR(20), sParams.scale)ELSE '' END + ' }'
                         ELSE ''
                    END
           END csharp
INTO       #dtl
FROM       sys.parameters sParams
INNER JOIN sys.types sTypes ON sTypes.user_type_id = sParams.user_type_id
LEFT JOIN  #map map ON map.k = sTypes.name
WHERE      sParams.object_id = @objId;

SELECT   t.paramId, t.paramName, t.typeName, t.len, t.prec, t.scale, t.isOut, t.isReturn, t.csharp
FROM     #dtl t
ORDER BY t.isReturn DESC, t.isOut DESC, t.paramId ASC;

IF EXISTS (SELECT 1 FROM #dtl t WHERE t.isReturn = 1)
BEGIN
  DECLARE @returnSqlParams VARCHAR(MAX);
  PRINT 'ReturnSqlParams:';
  SET @returnSqlParams = (SELECT 'SqlParameter ' + t.csharp + ';' FROM #dtl t WHERE t.isReturn = 1);
  IF OBJECT_ID('master..PrintLongMsgProc', 'P') IS NOT NULL
    EXEC master..PrintLongMsgProc @msg = @returnSqlParams;
END;

IF EXISTS (SELECT 1 FROM #dtl t WHERE t.isOut = 1 AND t.isReturn = 0)
BEGIN
  DECLARE @outSqlParams VARCHAR(MAX);
  PRINT 'OutSqlParams:';
  SET @outSqlParams = 'SqlParameter ' + (SELECT t.csharp + ',\r\n' FROM #dtl t WHERE t.isOut = 1 AND t.isReturn = 0 ORDER BY t.paramId ASC FOR XML PATH(''));
  SET @outSqlParams = REPLACE(LEFT(@outSqlParams, LEN(@outSqlParams) - LEN(',\r\n')) + ';', '\r\n', '
');
  IF OBJECT_ID('master..PrintLongMsgProc', 'P') IS NOT NULL
    EXEC master..PrintLongMsgProc @msg = @outSqlParams;
END;

IF EXISTS (SELECT 1 FROM #dtl t WHERE t.isOut = 0 AND t.isReturn = 0)
BEGIN
  DECLARE @sqlParams VARCHAR(MAX);
  PRINT 'SqlParams:';
  SET @sqlParams = (SELECT t.csharp + ',\r\n' FROM #dtl t WHERE t.isOut = 0 AND t.isReturn = 0 ORDER BY t.paramId ASC FOR XML PATH(''));
  SET @sqlParams = REPLACE(LEFT(@sqlParams, LEN(@sqlParams) - LEN(',\r\n')), '\r\n', '
');
  IF OBJECT_ID('master..PrintLongMsgProc', 'P') IS NOT NULL
    EXEC master..PrintLongMsgProc @msg = @sqlParams;
END;

DROP TABLE #dtl;
DROP TABLE #map;

SET NOCOUNT OFF;
GO