使用存储过程将两列并置为一列 [英] Concatenation two column in one column using store procedure

问题描述

大家好，

我有将两个列串联在一列中并得到结果的存储过程.
我在表1)FirstName 2)LastName中有2个字段.
在存储过程中要生成一列并在gridview中绑定.
这是存储过程.

  ALTER  过程 [dbo].[GetOrderList]


- 在此处添加存储过程的参数
 @ OrderID   INT ，
 @ OrderStatus   VARCHAR ( 100 )，
         @ CustomerName   VARCHAR ( 100 )
 AS 
开始

选择
OM.ID
OM.OrderID，
OM.OrderDate，
OM.OrderStatus，
                OM.FirstName + ' ' + OM.LastName  As 客户名称，
ZM.ZoneName，
SM.StateName，
CM.CityName，
S.StoreName
 FROM 
dbo.OrderMaster OM
 INNER   JOIN  dbo.OrderProduct OP  ON  OM .OrderID = OP.OrderID
 INNER   JOIN  dbo.Zone ZM  ON  ZM .ID = OM.ZoneID
 INNER   JOIN  dbo.StateMaster SM  ON  SM .ID = OM.StateID
 INNER   JOIN  dbo.City CM  ON  CM .ID = OM.CityID
 INNER   JOIN  dbo.StoreMaster S  ON  S .ID = OM.StoreID

位置
OM.IsDeleted = 0
 AND  OM.OrderID = ISNULL( NULLIF ( @ OrderID ， 0 )，OM.OrderID)
 AND  OM.FirstName + ' ' + OM.LastName 喜欢  @ CustomerName 
 AND  OM.OrderStatus  like  ' ％' + ISNULL( @ OrderStatus ，' ')+ ' ％'
 END  

但gridview中没有绑定:
这是GirdView BindDate:-

 私有  void  BindData(字符串 SortExpression，字符串 SortDirection， int  OrderID， string  OrderStatus，字符串 CustomerName)
    {
        DatabaseHelper db = 新 DatabaseHelper();
        数据集ds = db.GetOrderList(OrderID，OrderStatus，CustomerName);

        如果(SortDirection！= 空&& SortExpression！= 空)
        {
            DataView DView = 新 DataView(ds.Tables [ 0 ]);
            DView.Sort = SortExpression + "  + SortDirection;
            GrdOrder.DataSource = DView;
        }
        其他
        {
            GrdOrder.DataSource = ds.Tables [ 0 ];
            GrdOrder.DataBind();
        }
    } 

  public 数据集GetOrderList( int  OrderID，字符串OrderStatus，字符串CustomerName)
    {
        数据库 db =新的SqlDatabase(this.ConnectionString);
        使用(DbCommand objcmd = db.GetStoredProcCommand(" ))
        {
            如果(订单ID == 空)
                db.AddInParameter(objcmd，" ，DbType.Int32，DBNull.Value)；
            其他
                db.AddInParameter(objcmd，" ，DbType.Int32，OrderID)；

            如果(CustomerName == 空)
                db.AddInParameter(objcmd，" ，DbType.String，DBNull.Value)；
            其他
                db.AddInParameter(objcmd，" ，DbType.String，CustomerName)；

            如果(OrderStatus == 空)
                db.AddInParameter(objcmd，" ，DbType.String，DBNull.Value)；
            其他
                db.AddInParameter(objcmd，" ，DbType.String，OrderStatus)；

            数据集ds = db.ExecuteDataSet(objcmd);
            返回 ds；
        }
    } 

我在哪里错了.. !!

谢谢大家

解决方案

可能您的一栏是NULL，请在存储过程中尝试以下操作:

 ...
TRIM(ISNULL(OM.FirstName，' ')+  ' ' + ISNULL(OM.LastName，' 如 CustomerName，
... 

尝试一下

  SELECT  OrderMaster.FirstName + '  AS 全名ALTER PROCEDURE [dbo].[GetOrderList]


	-- Add the parameters for the stored procedure here
	@OrderID INT,
	@OrderStatus VARCHAR(100),
        @CustomerName VARCHAR(100)
AS
BEGIN
	
	SELECT
		OM.ID,
		OM.OrderID,
		OM.OrderDate,
		OM.OrderStatus,
                OM.FirstName + ' ' + OM.LastName As CustomerName,
		ZM.ZoneName,
		SM.StateName,
		CM.CityName,
		S.StoreName
	FROM
		dbo.OrderMaster OM
	INNER JOIN dbo.OrderProduct OP ON OM.OrderID=OP.OrderID
	INNER JOIN dbo.Zone ZM ON ZM.ID=OM.ZoneID
	INNER JOIN dbo.StateMaster SM ON SM.ID=OM.StateID
	INNER JOIN dbo.City CM ON CM.ID=OM.CityID
	INNER JOIN dbo.StoreMaster S ON S.ID=OM.StoreID
	
	WHERE
		OM.IsDeleted=0
		AND OM.OrderID = ISNULL(NULLIF(@OrderID,0),OM.OrderID)
		AND OM.FirstName + ' ' + OM.LastName like @CustomerName
		AND OM.OrderStatus like '%' + ISNULL(@OrderStatus,'') + '%'
END

but there is no bind in gridview :
here is GirdView BindDate :-

private void BindData(string SortExpression, string SortDirection, int OrderID,string OrderStatus,string CustomerName)
    {
        DatabaseHelper db = new DatabaseHelper();
        DataSet ds = db.GetOrderList(OrderID, OrderStatus,CustomerName);

        if (SortDirection != null && SortExpression != null)
        {
            DataView DView = new DataView(ds.Tables[0]);
            DView.Sort = SortExpression + " " + SortDirection;
            GrdOrder.DataSource = DView;
        }
        else
        {
            GrdOrder.DataSource = ds.Tables[0];
            GrdOrder.DataBind();
        }
    }

public DataSet GetOrderList(int OrderID,string OrderStatus,string CustomerName)
    {
        Database db = new SqlDatabase(this.ConnectionString);
        using (DbCommand objcmd = db.GetStoredProcCommand("dbo.GetOrderList"))
        {
            if (OrderID == null)
                db.AddInParameter(objcmd, "@OrderID", DbType.Int32, DBNull.Value);
            else
                db.AddInParameter(objcmd, "@OrderID", DbType.Int32, OrderID);

            if (CustomerName== null)
                db.AddInParameter(objcmd, "@CustomerName", DbType.String, DBNull.Value);
            else
                db.AddInParameter(objcmd, "@CustomerName", DbType.String, CustomerName);

            if (OrderStatus == null)
                db.AddInParameter(objcmd, "@OrderStatus", DbType.String, DBNull.Value);
            else
                db.AddInParameter(objcmd, "@OrderStatus", DbType.String, OrderStatus);

            DataSet ds = db.ExecuteDataSet(objcmd);
            return ds;
        }
    }

Where I am Wrong in this..!!

Thank You All

解决方案

Probably one of your columns is NULL, try the following in your stored procedure:

...
TRIM(ISNULL(OM.FirstName,'') + ' ' + ISNULL(OM.LastName,'')) As CustomerName,
...

Try this

SELECT OrderMaster.FirstName + ' ' + OrderMaster.LastName AS Full_Name FROM OrderMaster

Suspect the following line
AND OM.OrderID = ISNULL(NULLIF(@OrderID,0),OM.OrderID)
can be written as
AND OM.OrderID = COALESCE(@OrderID,0)


BUT the question here is do you have records with OrderID as zero in your table ?

这篇关于使用存储过程将两列并置为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！