mssql 存储过程动态参数实现方法

-创建测试表

CREATE TABLE [dbo].[Student](
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](20) NOT NULL   DEFAULT (''),
    [Age] [int] NOT NULL DEFAULT (0),
    [Sex] [bit] NOT NULL DEFAULT (0),
    [Address] [nvarchar](200)  NOT NULL DEFAULT ('')
)

--比如是一个查询存储过程

Create PROC GetStudentByType
    @type int =0, -- 1根据id查询,  2根据性别查询
    @args XML     -- 参数都写到这里吧
AS
BEGIN
    DECLARE @id INT,@sex BIT
    SET @[email protected]('(args/id)[1]','int')   --参数都可以写在这里，如果没有传过来，大不了是null值了，反正也用不到，没关系的
    SET @sex [email protected]('(args/sex)[1]','bit')
   
    IF(@type=1)
    BEGIN
        SELECT * FROM dbo.Student WHERE ID=@id
    END
    IF(@type=2)
    BEGIN
        SELECT * FROM dbo.Student WHERE Sex=@sex
    END
END   
 

参数写xml里感觉比用字符串要好很多，这样调用时参数就不好组织了，所以这里要有个帮助类XmlArgs

public class XmlArgs
    {
        private string _strArgs = string.Empty;
        private bool _isCreate = false;
        private Dictionary _args;
        public string Args
        {
            get
            {
                if (!_isCreate)
                {
                    _strArgs = _CreateArgs();
                }
                return _strArgs;
            }
        }

        public XmlArgs()
        {
            _args = new Dictionary();
        }

        public void Add(string key, object value)
        {
            _args.Add(key, value.ToString());
            _isCreate = false;
        }

        public void Remove(string key)
        {
            _args.Remove(key);
            _isCreate = false;
        }

        public void Clear()
        {
            _args.Clear();
            _isCreate = false;
        }

        private string _CreateArgs()
        {
            if (_args.Count == 0)
            {
                return string.Empty;
            }
            StringBuilder sb = new StringBuilder();
            foreach (string key in _args.Keys)
            {
                sb.AppendFormat("<{0}>{1}", key, _args[key]);
            }
            return sb.ToString();
        }
    }
 

调用：

private void BindData()
{
    XmlArgs args = new XmlArgs();
    args.Add("id", 1);
    System.Data.DataTable dt = GetStudentByType(1, args);
    GridView1.DataShow(dt);
}
private System.Data.DataTable GetStudentByType(int type, XmlArgs args)
{
    SqlHelper helper = new SqlHelper();
    helper.Params.Add("type", type);
    helper.Params.Add("args", args.Args);
    System.Data.DataTable dt = helper.RunDataTable("GetStudentByType");
    return dt;
}