存储过程未按预期执行

I have written following stored procedure

我写了以下存储过程

CREATE procedure [dbo].[findUSerID]
    @Column_name varchar(50),
    @TR_ID int
AS
    DECLARE @sql nvarchar(max) = 'SELECT ' +@Column_name+ ' 
                                  FROM Transfer_TB 
                                  WHERE TID =' + CAST(@TR_ID AS VARCHAR(10))

    EXEC sp_executesql @sql

Table definition :

表定义:

CREATE TABLE [dbo].[Transfer_TB]
(
    [TID] [int] NULL,
    [ABC] [varchar](20) NULL,
    [XYZ] [varchar](50) NULL,
    [LMN] [varchar](50) NULL,
    [PQR] [varchar](50) NULL,
)

But it does not return the proper output.

但它没有返回正确的输出。

Like I have called it from my asp page code for that using n tier architecture.

就像我使用n层架构从我的asp页面代码中调用它一样。

public string check_validID(string branch,int trId)
{
    string user_Br_ID;
    clsBranch_TB objbr = new clsBranch_TB();
    clsUserTB objuser = new clsUserTB();
    objuser.User_Branch = 'XYZ';
    objuser.Extra_Int = 32;

    DataSet ds = clsAdminLogic.findUSerID(objuser);

    if (ds.Tables[0].Rows.Count == 0)
    {
        user_Br_ID = clsAdminLogic.getno_of_Emp(objbr);
    }
    else 
    {
        user_Br_ID = ds.Tables[0].Rows[0][0].ToString();
    }

    return user_Br_ID;
}

 public static DataSet findUSerID(clsUserTB objuser)
 {
        DataSet ds = DataAccessLayer.clsLogs.findUSerID(objuser);
        return ds;
 }

 public static DataSet findUSerID(clsUserTB objuser)
 {
     SqlParameter[] param = {
                              new SqlParameter("@TR_ID",objuser.Extra_Int),
                              new SqlParameter("@Column_name",objuser.User_Branch)
                            };
     DataSet ds = DataAccessLayer.SqlHelper.FillDataNewRJ(
                     DataAccessLayer.clsDataAccessLayer.con.ConnectionString.ToString(),
                     CommandType.StoredProcedure, "findUSerID", (param)
            );
     return ds;
}

As it executes it, there is value present in database, but still it enters into if part of that function.

在执行它时,数据库中存在值,但如果该函数的一部分仍然存在。

Please help me and guide if something wrong in above code

如果上面的代码有问题,请帮助我并指导

3 个解决方案

#1

Your stored procedure is prone to Sql-Injection. do the following to protect yourself against it.

您的存储过程很容易进行Sql-Injection。执行以下操作以保护自己免受攻击。

CREATE procedure [dbo].[findUSerID]
   @Column_name SYSNAME
  ,@TR_ID       INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(max);

  SET @sql = N' SELECT ' + QUOTENAME(@Column_name) 
           + N' FROM Transfer_TB '
           + N' WHERE TID = @TR_ID '

  exec sp_executesql @sql
                    ,N'@TR_ID int'
                    ,@TR_ID 

END

If your Stored procedure is returning a scalar value "UserID" as the name suggest you should be doing something like......

如果你的存储过程返回一个标量值“UserID”,顾名思义你应该做的事......

CREATE procedure [dbo].[findUSerID]
   @Column_name SYSNAME
  ,@TR_ID       INT
  ,@UserID      INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(max);

  SET @sql = N' SELECT @UserID =  ' + QUOTENAME(@Column_name) 
           + N' FROM Transfer_TB '
           + N' WHERE TID = @TR_ID '

exec sp_executesql @sql
                  ,N'@TR_ID int , @UserID INT OUTPUT'
                  ,@TR_ID 
                  ,@UserID OUTPUT

END

#2

there is value present in database, but still it enters into else part of that function

数据库中存在值,但它仍然进入该函数的else部分

I assume this is the part you're talking about (there are 3 functions in that code)

我假设这是你正在讨论的部分(该代码中有3个函数)

if (ds.Tables[0].Rows.Count == 0)
{
    user_Br_ID = clsAdminLogic.getno_of_Emp(objbr);
}
else 
{
    user_Br_ID = ds.Tables[0].Rows[0][0].ToString();
}

Yes. Your if goes into the else if the rows are not 0. It's doing exactly what its being told. That basic logic error is the least of your worries though. You're storing branches as columns?

是。如果行不是0,你的if进入else。它正在完成它被告知的内容。尽管如此,这个基本的逻辑错误是你最不担心的。您将分支存储为列?

#3

i found solution for this problem, actually that particular record contains values instead of selective column that's why query returning the null value. So i just have added one condition my stored procedure to fix it .

我找到了这个问题的解决方案,实际上该特定记录包含值而不是选择列,这就是查询返回空值的原因。所以我只是添加了一个条件我的存储过程来修复它。

ALTER procedure [dbo].[findUSerID]
@Column_name varchar(50),
@TR_ID int

AS

DECLARE @sql nvarchar(max) = 'SELECT ' +@Column_name+ ' 
FROM Transfer_TB where TID =' + CAST(@TR_ID AS VARCHAR(10))+' 
and '+@Column_name+'<> null'

exec sp_executesql @sql

#1