使用带有where子句的存储过程传递参数? [英] passing parameters using stored procedure with where clause??

问题描述

i have created this function with a parameter offset and i want to access the database using stored procedure but the code and stored procedure i have written below is not working...what is the right way to do it??

# public bool isoffsetexist(int offset)
#     {
#         conn.Open();
#  
#         SqlCommand cmd = new SqlCommand();
#         cmd.Connection = conn;
#         cmd.CommandType = CommandType.StoredProcedure;
#         cmd.CommandText = "sp_offset1";
#         SqlDataReader dr = cmd.ExecuteReader();
#  
#         int i = 0;
#         while (dr.Read())
#         {
#             i++;
#         }
#         if (i == 1)
#         {
#             conn.Close();
#             return true;
#         }
#         else
#         {
#             conn.Close();
#             return false;
#         }
#     }

我的存储过程"sp_offset1"为

my stored procedure "sp_offset1" is

# CREATE PROCEDURE [dbo].[sp_offset1]
# @offset numeric,
# @appname varchar(20),
# @os varchar(20)
#  AS
# select * from tb_offset
#  where @offset=" + offset + "

推荐答案

您的sp应该如下所示

Your sp should be like below

CREATE PROCEDURE [dbo].[sp_offset1]
@offset numeric,
@appname varchar(20),
@os varchar(20)
AS
select * from tb_offset
where offset=@offset

然后您应该在代码中添加参数

Then you should add parameter in your code

SqlCommand cmd = new SqlCommand();
SqlParameter param;
string offset= txtOffset.text;
param = new SqlParameter("@offset", SqlDbType.Numeric);
param.Direction = ParameterDirection.Input;
param.Value = offset;
cmd.Parameters.Add(param);

请根据需要更改代码.

然后您的sp包含3个参数，因此您应该在代码中将值传递给3个参数.(@ appname，@os)

Please change your code based on your need.

Then your sp contains 3 parameters, so you should pass values to 3 parameters in your code.(@appname, @os)

这篇关于使用带有where子句的存储过程传递参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！