存储过程如下,只写了一部份,还没写完。IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'FindList'
AND type = 'P')
DROP PROCEDURE FindList
GOCREATE PROCEDURE FindList
@ID Int = Null ,
@SName Varchar(10)=Null,
@TypeName Varchar(10)=Null,
@Operator Varchar(8)=Null,
@StockName Varchar(20)=Null,
@StartDate DateTime = Null,
@EndData DateTime=Null,
@ListIn as Bit=Null,
@List as Bit=Null
AS
Declare @StrSql Varchar(1000)
Declare @StrWhere Varchar(1000) If @ListIn Is Not Null And @ListIn = 1
Begin
If (@List Is Not Null) And (@List = 1)
Begin
Set @StrSql ='SELECT'
Set @StrSql =@StrSql + ' StockIn.ID, Supplies.SName, Supplies.Alias, Supplies.TypeName,Supplies.Color,InList.Qity, StockIn.Operator, StockName.StockName, StockIn.InDate '
Set @StrSql =@StrSql + ' FROM '
Set @StrSql =@StrSql + ' StockIn INNER JOIN InList ON StockIn.ID = InList.ListID INNER JOIN Supplies ON InList.SupID = Supplies.ID INNER JOIN StockName ON StockIn.StockID = StockName.ID'
End
Else
Begin
Set @StrSql ='SELECT'
Set @StrSql =@StrSql + ' DISTINCT StockIn.ID, StockName.StockName, StockIn.Operator, StockIn.InDate '
Set @StrSql =@StrSql + ' FROM '
Set @StrSql =@StrSql + ' StockIn INNER JOIN InList ON StockIn.ID = InList.ListID INNER JOIN Supplies ON InList.SupID = Supplies.ID INNER JOIN StockName ON StockIn.StockID = StockName.ID'
End
If @ID Is Not Null
Begin
Set @StrWhere=' Where StockIn.ID = ' + CAST( @ID as Varchar(10))
End
If @SName Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And Supplies.SName = ' + '''' + @SName + ''''
Else
Set @StrWhere=' Where Supplies.SName = ' + '''' + @SName +'''' End
If @TypeName Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And Supplies.TypeName ' + ' ''' + @TypeName + ''''
Else
Set @StrWhere = ' Where Supplies.TypeName ' + ' ''' + @TypeName +'''' End
If @Operator Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockIn.Operator Like ' + ' ''%' + @Operator + '%'''
Else
Set @StrWhere=' Where StockIn.Operator Like ' + ' ''%' + @Operator +'%''' End
If @StockName Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockName.StockName = ' + ' ''' + @StockName + ''''
Else
Set @StrWhere=' Where StockName.StockName = ' + ' ''' + @StockName +'''' End
If @StartDate Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockIn.InDate >= ' + ' ''' + @StartDate + ''''
Else
Set @StrWhere=' Where StockIn.InDate >= ' + ' ''' + @StartDate +'''' End
If @EndData Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockIn.InDate <= ' + ' ''' + @EndData + ''''
Else
Set @StrWhere=' Where StockIn.InDate <= ' + ' ''' + @EndData +'''' End If @StrWhere Is Not Null
Exec (@StrSql + @StrWhere)
Else
Exec (@StrSql) End
GO我执行存储过程 Exec FindList @ListIn=1,@List=1,@Operator ='aa' 没有问题
执行Exec FindList @ListIn=1,@List=1,@TypeName ='aa'
提示:服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'aa' 附近有语法错误。
这是怎么回事。
FROM sysobjects
WHERE name = 'FindList'
AND type = 'P')
DROP PROCEDURE FindList
GOCREATE PROCEDURE FindList
@ID Int = Null ,
@SName Varchar(10)=Null,
@TypeName Varchar(10)=Null,
@Operator Varchar(8)=Null,
@StockName Varchar(20)=Null,
@StartDate DateTime = Null,
@EndData DateTime=Null,
@ListIn as Bit=Null,
@List as Bit=Null
AS
Declare @StrSql Varchar(1000)
Declare @StrWhere Varchar(1000) If @ListIn Is Not Null And @ListIn = 1
Begin
If (@List Is Not Null) And (@List = 1)
Begin
Set @StrSql ='SELECT'
Set @StrSql =@StrSql + ' StockIn.ID, Supplies.SName, Supplies.Alias, Supplies.TypeName,Supplies.Color,InList.Qity, StockIn.Operator, StockName.StockName, StockIn.InDate '
Set @StrSql =@StrSql + ' FROM '
Set @StrSql =@StrSql + ' StockIn INNER JOIN InList ON StockIn.ID = InList.ListID INNER JOIN Supplies ON InList.SupID = Supplies.ID INNER JOIN StockName ON StockIn.StockID = StockName.ID'
End
Else
Begin
Set @StrSql ='SELECT'
Set @StrSql =@StrSql + ' DISTINCT StockIn.ID, StockName.StockName, StockIn.Operator, StockIn.InDate '
Set @StrSql =@StrSql + ' FROM '
Set @StrSql =@StrSql + ' StockIn INNER JOIN InList ON StockIn.ID = InList.ListID INNER JOIN Supplies ON InList.SupID = Supplies.ID INNER JOIN StockName ON StockIn.StockID = StockName.ID'
End
If @ID Is Not Null
Begin
Set @StrWhere=' Where StockIn.ID = ' + CAST( @ID as Varchar(10))
End
If @SName Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And Supplies.SName = ' + '''' + @SName + ''''
Else
Set @StrWhere=' Where Supplies.SName = ' + '''' + @SName +'''' End
If @TypeName Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And Supplies.TypeName ' + ' ''' + @TypeName + ''''
Else
Set @StrWhere = ' Where Supplies.TypeName ' + ' ''' + @TypeName +'''' End
If @Operator Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockIn.Operator Like ' + ' ''%' + @Operator + '%'''
Else
Set @StrWhere=' Where StockIn.Operator Like ' + ' ''%' + @Operator +'%''' End
If @StockName Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockName.StockName = ' + ' ''' + @StockName + ''''
Else
Set @StrWhere=' Where StockName.StockName = ' + ' ''' + @StockName +'''' End
If @StartDate Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockIn.InDate >= ' + ' ''' + @StartDate + ''''
Else
Set @StrWhere=' Where StockIn.InDate >= ' + ' ''' + @StartDate +'''' End
If @EndData Is Not Null
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And StockIn.InDate <= ' + ' ''' + @EndData + ''''
Else
Set @StrWhere=' Where StockIn.InDate <= ' + ' ''' + @EndData +'''' End If @StrWhere Is Not Null
Exec (@StrSql + @StrWhere)
Else
Exec (@StrSql) End
GO我执行存储过程 Exec FindList @ListIn=1,@List=1,@Operator ='aa' 没有问题
执行Exec FindList @ListIn=1,@List=1,@TypeName ='aa'
提示:服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'aa' 附近有语法错误。
这是怎么回事。
解决方案 »
- 对master.dbo.sysobjects和sys.objects的查询
- 如何清空一个MS SQL 2008 数据库中所有表中的所有记录
- VB+server弹出报警,急~~~~~~
- uniqueidentifier VS Char 做主键
- 如何将字段插入到表中指定的位置?
- 如何汇总
- SQL 2008安装时报错.
- 列转行 请教!!!!!!!!
- 装了sql server 2005 express,开始菜单里只有个服务配置工具的菜单,没有企业管理器的菜单,到具体的装sql server的文件夹里找到sqlserver.
- 里有RapidSql下载?谢谢
- 2表查询问题
- 应该比较难...CLR错误提示如下
Begin
If @StrWhere IS NOT NULL
Set @StrWhere = @StrWhere + ' And Supplies.TypeName = ' + ' ''' + @TypeName + ''''
Else
Set @StrWhere = ' Where Supplies.TypeName =' + ' ''' + @TypeName +'''' End
---
这儿少一个 ' = ' 吧