笔记299 获取传入存储过程里的参数包括参数值 --2013-6-18

笔记299 获取传入存储过程里的参数包括参数值 --2013-6-18

--获取传入存储过程里的参数包括参数值 --2013-6-18
http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/016071c4-4d1c-4cf2-8650-58ac6258e5dc

SELECT * FROM sys.[parameters] --获取当前use的数据库里所有存储过程里的传入参数,但是不能获取参数值
USE [pratice]
GO
---------------------------------

DROP FUNCTION [dbo].[f_split]
GO
CREATE FUNCTION f_split
(
  @SourceSql VARCHAR(8000) ,
  @StrSeprate VARCHAR(10)
)
RETURNS @temp TABLE ( ss VARCHAR(100) )
AS
    BEGIN
        DECLARE @i INT
        SET @SourceSql = RTRIM(LTRIM(@SourceSql))
        SET @i = CHARINDEX(@StrSeprate, @SourceSql)
        WHILE @i >= 1
            BEGIN
                INSERT  @temp
                VALUES  ( LEFT(@SourceSql, @i - 1) )
                SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,
                                           LEN(@SourceSql) - @i)
                SET @i = CHARINDEX(@StrSeprate, @SourceSql)
            END
        IF @SourceSql <> '\'
            INSERT  @temp
            VALUES  ( @SourceSql )
        RETURN
    END


------------------------------------

CREATE   proc up_test (@a varchar(100))
AS
DECLARE @s VARCHAR(200)
SET @s=' '
DECLARE @num INT
DECLARE @increase INT
DECLARE @temps VARCHAR(20)
CREATE TABLE temptb(id INT IDENTITY(1,1) NOT NULL,para VARCHAR(20))
INSERT INTO temptb(para) SELECT ss FROM [f_split](@a,',')
SELECT  @num = COUNT(*)
FROM    temptb
SET @increase = 1
WHILE @increase < @num +1
    BEGIN
        SELECT  @temps = para
        FROM    temptb
        WHERE   id = @increase;
        SET @s = @s +' 参数'+CAST(@increase AS VARCHAR(20))+':  '+ @temps+';'
        SET @increase = @increase + 1
    END
SELECT  @s
DROP TABLE temptb

exec up_test '库存,6,2013-6-12'