SQL Server stored procedure dynamic parameter calling implementation code-mysql tutorial

SQL Server stored procedure dynamic parameter call implementation code. For more information, see.

SQL Server stored procedure dynamic parameter call implementation code. For more information, see.

Just take notes. nothing !!
The code is as follows:
-- Create a test table
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 ('')
)
-- For example, a query stored procedure
Create PROC GetStudentByType
@ Type int = 0, -- 1 Query by id, 2 query by gender
@ Args XML -- write all parameters here
AS
BEGIN
DECLARE @ id INT, @ sex BIT
SET @ id = @ args. value ('(args/id) [1]', 'int') -- The parameters can all be written here. if it is not passed, it cannot be a null value. it cannot be used anyway, it doesn't matter.
SET @ sex = @ args. value ('(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

It is much better to write parameters in xml than to use strings. in this way, the parameters are difficult to organize during the call, so here we need a help class XmlArgs.
The code is as follows:
Public class XmlArgs
{
Private string _ strArgs = string. Empty;
Private bool _ isCreate = false;
Private Dictionary _ Args;
Public string Args
{
Get
{
If (! _ IsCreate)
{
_ StrArgs = _ CreateArgs ();
_ IsCreate = true;
}
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 ();
}
}

Call:
The code is as follows:
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;
}