1. 带参数的存储过程
set
ANSI_NULLS
ON
set
QUOTED_IDENTIFIER
ON
GO
ALTER
PROCEDURE
[
dbo
]
.
[
sp_select_gua
]
@num
int
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT
ON
;
select
mean
from
tb_gua
where
id
=
@num
END
C#中的调用
conn.Open();
SqlCommand cmd
=
new
SqlCommand();
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
sp_select_gua
"
;
cmd.Connection
=
conn;
cmd.Parameters.Add(
new
SqlParameter(
"
@num
"
, SqlDbType.Int));
cmd.Parameters[
"
@num
"
].Value =
Calculator();
c
= (
string
)cmd.ExecuteScalar();
2. 带rerurn的存储过程
set
ANSI_NULLS
ON
set
QUOTED_IDENTIFIER
ON
GO
ALTER
PROCEDURE
[
dbo
]
.
[
sp_select_gua2
]
(
@num1
int
,
@num2
int
)
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT
ON
;
return
@num1
+
@num2
;
END
C#中的调用
conn.Open();
SqlCommand cmd
=
new
SqlCommand();
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
sp_select_gua2
"
;
cmd.Connection
=
conn;
cmd.Parameters.Add(
new
SqlParameter(
"
@num1
"
, SqlDbType.Int));
cmd.Parameters[
"
@num1
"
].Value =
Calculator();
cmd.Parameters.Add(
new
SqlParameter(
"
@num2
"
, SqlDbType.Int));
cmd.Parameters[
"
@num2
"
].Value =
Calculator();
cmd.Parameters.Add(
new
SqlParameter(
"
@return
"
, SqlDbType.Int));
cmd.Parameters[
"
@return
"
].Direction =
ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
c
= cmd.Parameters[
"
@return
"
].Value.ToString();
３． 带output参数的调用
set
ANSI_NULLS
ON
set
QUOTED_IDENTIFIER
ON
GO
ALTER
PROCEDURE
[
dbo
]
.
[
sp_select_gua1
]
(
@num
int
,
@guamean
nvarchar
(
1000
)
=
''
output,
@guaname
nvarchar
(
10
)
=
''
output)
AS
BEGIN
--
SET NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET
NOCOUNT
ON
;
select
@guaname
=
name,
@guamean
=
mean
from
tb_gua
where
id
=
@num
END
C#中的调用
conn.Open();
SqlCommand cmd
=
new
SqlCommand();
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
sp_select_gua1
"
;
cmd.Connection
=
conn;
cmd.Parameters.Add(
new
SqlParameter(
"
@num
"
, SqlDbType.Int));
cmd.Parameters[
"
@num
"
].Value =
Calculator();
cmd.Parameters.Add(
new
SqlParameter(
"
@guamean
"
, SqlDbType.NVarChar,
1000
));
cmd.Parameters[
"
@guamean
"
].Direction =
ParameterDirection.Output;
cmd.Parameters.Add(
new
SqlParameter(
"
@guaname
"
, SqlDbType.NVarChar,
10
));
cmd.Parameters[
"
@guaname
"
].Direction =
ParameterDirection.Output;
cmd.ExecuteNonQuery();
c
= cmd.Parameters[
"
@guamean
"
].Value.ToString();
d
= cmd.Parameters[
"
@guaname
"
].Value.ToString();
