22,209
社区成员
发帖
与我相关
我的任务
分享
Dim sql As String
Dim sqlWhere As String
'输入非空的才拼条件'
sqlWhere = vbNullString
If Len(TextBox22.Text) <> 0 Then
sqlWhere = sqlWhere & " AND ID= '" + TextBox22.Text + "'"
End If
If Len(TextBox1.Text) <> 0 Then
sqlWhere = sqlWhere & " AND xuhao='" + TextBox1.Text + "'"
End If
...
'第一个 AND 替换成 WHERE'
If Len(sqlWhere) <> 0 Then
sqlWhere = " WHERE " & Mid$(sqlWhere, 5)
End If
sql = "select * from equipment " & sqlWhere
--定义动态SQL
Declare @SqlTxt NVarchar(4000),@SqlCon NVarchar(1000)
Set @SqlTxt = N''
Set @SqlCon = N''
--组织动态SQL字符
Set @SqlTxt = N'Insert Into #TmpStyle' + CHAR(13)
+ N'Select Distinct 0,A.SystemID,A.StyleID,A.StyleNo,A.StyleName,A.StandardStyleNo,0,A.StyleTypeID,N'''',N'''',A.CreatedDate,DATEADD(Day,A.CreatedDate,N''2010-01-01''),0 ' + CHAR(13)
+ N' From MainStyle A With(NoLock) ' + CHAR(13)
+ N' Inner Join StyleEdition B With(NoLock) On B.SystemID = A.SystemID ' + CHAR(13)
/***** 处理条件语句生成 *****/
--款号条件处理
If @StyleNo != N''
Begin
If @SqlCon = N'' Set @SqlCon = N' Where ' Else Set @SqlCon = @SqlCon + N' And '
Set @SqlCon = @SqlCon + N' (A.StyleNo Like ''%' + @StyleNo + N'%'')' + CHAR(13)
End
--款名条件处理
If @StyleName != N''
Begin
If @SqlCon = N'' Set @SqlCon = N' Where ' Else Set @SqlCon = @SqlCon + N' And '
Set @SqlCon = @SqlCon + N' (A.StyleName Like ''%' + @StyleName + N'%'')' + CHAR(13)
End
--主款号条件处理
If @StandardStyleNo != N''
Begin
If @SqlCon = N'' Set @SqlCon = N' Where ' Else Set @SqlCon = @SqlCon + N' And '
Set @SqlCon = @SqlCon + N' (A.StandardStyleNo = ''' + @StandardStyleNo + N''')' + CHAR(13)
End
--款式类别条件处理
If @StyleTypeID > 0
Begin
If @SqlCon = N'' Set @SqlCon = N' Where ' Else Set @SqlCon = @SqlCon + N' And '
Set @SqlCon = @SqlCon + N' (A.StyleTypeId = ' + CONVERT(NVarchar,@StyleTypeID) + N')' + CHAR(13)
End
--修改时间处理条件处理
If @MFDate > 0 Or @MTDate > 0
Begin
If @SqlCon = N'' Set @SqlCon = N' Where ' Else Set @SqlCon = @SqlCon + N' And '
Set @SqlCon = @SqlCon + N' (DATEDIFF(DAY,N''2010-01-01'',B.ModifiedDate) Between ' + CONVERT(NVarchar,@MFDate) + N' And ' + CONVERT(NVarchar,@MTDate) + N')' + CHAR(13)
End
--状态条件处理
If @SqlCon = N'' Set @SqlCon = N' Where ' Else Set @SqlCon = @SqlCon + N' And '
Set @SqlCon = @SqlCon + N' (A.State = ' + CONVERT(NVarchar,@State) + N')' + CHAR(13)
Set @SqlTxt = @SqlTxt + @SqlCon
Print @SqlTxt
Exec(@SqlTxt)