搜索与动态显示字段

搜索功能，并动态显示用户选择的货位，可以通过下面的存储过程去实现。aspx页面，即放一个Gridview即可。

本例程，可以学会如何使用临时表，循环表，和字符串连接，动态SQL语句与执行。

 

  

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_ItemGeneral_Store_Search]
(        
    @CargoSpaceCollection NVARCHAR(100),--货位集合
    @CompareOperator NVARCHAR(100)  --条件
)
AS
BEGIN
    --创建临时表，放存货位，以便做循环记录动作
    IF OBJECT_ID('#TempCargoSpace') IS NOT NULL
        DROP TABLE #TempCargoSpace    
    CREATE TABLE  #TempCargoSpace 
     (   
        ID INT IDENTITY(1,1),
        CargoSpaceId SMALLINT,
        CargoSpaceName NVARCHAR(50)
     )        
END
--宣告变量
--下面有两个变量，宣告之后，马上赋值为'',如果不这样实现，那在下面字符串连接时，NULL与任何字符串连接之后，还是NULL
DECLARE @N INT,@ROWS INT,@CargoSpaceField NVARCHAR(100) = '',@CargoSpaceField1 NVARCHAR(100) = '',@CompareCondition NVARCHAR(500)

SET @N = 1

--如果用户有选择货位
IF (LEN(@CargoSpaceCollection) > 0)    
    --从货位表中搜索出货位主健,名称存入临进表中。
    EXECUTE('INSERT INTO #TempCargoSpace([CargoSpaceId],[CargoSpaceName]) SELECT [CargoSpaceId],[CargoSpaceName] FROM [dbo].[CargoSpace] WHERE [CargoSpaceId] IN (' + @CargoSpaceCollection + ')')
ELSE
    --如果没有选择货位的话，把货位表中所有记录插入临时表中
    INSERT INTO #TempCargoSpace([CargoSpaceId],[CargoSpaceName]) SELECT [CargoSpaceId],[CargoSpaceName] FROM [dbo].[CargoSpace]

--记录发生的记录笔数
SET @ROWS = @@ROWCOUNT

--开始循环临时表
WHILE @N <= @ROWS 
BEGIN
    DECLARE @CargoSpaceId NVARCHAR(30),@CargoSpaceName NVARCHAR(50)     
    --找出每笔记录的货位主键与货位名称
    SELECT @CargoSpaceId = [CargoSpaceId],@CargoSpaceName = [CargoSpaceName] FROM #TempCargoSpace WHERE [ID] = @N        
    --在aspx网页中将会显示这些组合成字段,如：,[1] AS [默认货位],[2] AS [CR1],[3] AS [SRC]....
    SET @CargoSpaceField = @CargoSpaceField + ',[' + @CargoSpaceId + '] AS [' + @CargoSpaceName + ']'
    --下面句，组成条件所需字段,如：,[1],[2],[3]....
    SET @CargoSpaceField1 = @CargoSpaceField1 + ',[' + @CargoSpaceId +']'        
    SET @N = @N + 1    
END --临时表循环结束


IF (LEN(@CargoSpaceField) > 0)
    --截除前面的“，”
    SET @CargoSpaceField = SUBSTRING(@CargoSpaceField,2,LEN(@CargoSpaceField))    

IF (LEN(@CargoSpaceField1) > 0)        
    --截除前面的“，”
    SET @CargoSpaceField1 = SUBSTRING(@CargoSpaceField1,2,LEN(@CargoSpaceField1))

--如果用户有选择条件话，条件如：<> 10
IF (LEN(@CompareOperator) > 0)        
    --每个字段套用条件,结果如：[1] <> 10 OR [2] <> 10 OR [3] <> 10
    SET @Comparecondition =  REPLACE(REPLACE(@CargoSpaceField1,',',' OR '),']',']' + @CompareOperator)  

BEGIN
    DECLARE @sql nvarchar(4000)    = 'SELECT [ItemCode] AS [物料编码],[ItemGeneralName] AS [物料说明],[UnitMeasureName] AS [单位],' + @CargoSpaceField + ' FROM [dbo].[udf_ItemGeneralStock]()'
    IF (LEN(@CompareOperator) = 0)         
        SELECT @sql = @sql + ' ORDER BY [ItemCode]'
    ELSE IF (LEN(@CompareOperator) > 0) 
        SELECT @sql = @sql + ' WHERE ' + @Comparecondition + ' ORDER BY [ItemCode]'    
    EXECUTE sp_executesql @sql    
END