我根据大家的意见已经把stored procedure之前错的地方改了下,可是还是不能编译成功,请大家帮我看看还有哪里有错,谢谢了,代码如下:CREATE PROCEDURE sp_displaychosencustomers@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)AS
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = "ID = " + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + " AND ( NAME = \'" + @name + "\' )"
else if ( (@name is not null) AND @id = 0 )
SET @str = ("NAME = \'" + @name + "\'" )
if ( ( @birth <> DateTime.Today ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + " AND ( Birth = \'" + @birth + "\' )"
else if ( @birth <> DateTime.Today AND @id = 0 AND @name is null )
SET @str = ( "Birth= \'" + @birth + "\'" )
if ( @address is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today) )
SET @str = @str + " AND ( ADDRESS = \'" + @address + "\' )"
else if ( @address is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today )
SET @str = ( "Address = \'" + @address + "\'" )
if ( @phone is not null AND ( @id = 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null ))
SET @str = @str + " AND ( PHONE = \'" + @phone + "\' )"
else if ( @phone is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null )
SET @str = ( "PHONE= \'" + @phone + "\'" )
if ( @state is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null
OR @phone is not null ))
SET @str = @str + " AND ( STATE = \'" + @state + "\' )"
else if ( @state is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null
AND @phone is null )
SET @str = ( "STATE = \'" + @state + "\'" ) if (@str is null)
select * from customer
else if (@str is not null )
select * from customer
where @str
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)AS
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = "ID = " + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + " AND ( NAME = \'" + @name + "\' )"
else if ( (@name is not null) AND @id = 0 )
SET @str = ("NAME = \'" + @name + "\'" )
if ( ( @birth <> DateTime.Today ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + " AND ( Birth = \'" + @birth + "\' )"
else if ( @birth <> DateTime.Today AND @id = 0 AND @name is null )
SET @str = ( "Birth= \'" + @birth + "\'" )
if ( @address is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today) )
SET @str = @str + " AND ( ADDRESS = \'" + @address + "\' )"
else if ( @address is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today )
SET @str = ( "Address = \'" + @address + "\'" )
if ( @phone is not null AND ( @id = 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null ))
SET @str = @str + " AND ( PHONE = \'" + @phone + "\' )"
else if ( @phone is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null )
SET @str = ( "PHONE= \'" + @phone + "\'" )
if ( @state is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null
OR @phone is not null ))
SET @str = @str + " AND ( STATE = \'" + @state + "\' )"
else if ( @state is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null
AND @phone is null )
SET @str = ( "STATE = \'" + @state + "\'" ) if (@str is null)
select * from customer
else if (@str is not null )
select * from customer
where @str
解决方案 »
- 求教高手一个XML转table的问题
- sql server2000使用select count(某字段名) from table提示"在这一子查询或聚合表达式中,text、ntext 和 image 数据类型无效。"
- 关于update更新表问题
- 请教三个安装方面的问题。
- 关于导入数据库表时结构发生变化的问题!在线等待!100分
- 请问那位高手可以帮我解决这个存储过程问题????
- SQL 语句的优化问题,想了n天没结果,有兴趣的请进
- 列舉問題
- 一个关于两表中内容联动更改的问题。请大家帮助,分不够再给!!!
- 关于数据库结构的问题
- 动态tsql: 构造动态命令字符时,如何在串中包含单引号'?
- 一个关于计算出表达式值SQL语句的问题
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5) AS
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = "ID = " + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + " AND ( NAME = \'" + @name + "\' )"
else if ( (@name is not null) AND @id = 0 )
SET @str = ("NAME = \'" + @name + "\'" )
if ( ( @birth <> DateTime.Today ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + " AND ( Birth = \'" + @birth + "\' )"
else if ( @birth <> DateTime.Today AND @id = 0 AND @name is null )
SET @str = ( "Birth= \'" + @birth + "\'" )
if ( @address is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today) )
SET @str = @str + " AND ( ADDRESS = \'" + @address + "\' )"
else if ( @address is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today )
SET @str = ( "Address = \'" + @address + "\'" )
if ( @phone is not null AND ( @id = 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null ))
SET @str = @str + " AND ( PHONE = \'" + @phone + "\' )"
else if ( @phone is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null )
SET @str = ( "PHONE= \'" + @phone + "\'" )
if ( @state is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null
OR @phone is not null ))
SET @str = @str + " AND ( STATE = \'" + @state + "\' )"
else if ( @state is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null
AND @phone is null )
SET @str = ( "STATE = \'" + @state + "\'" ) if (@str is null)
select * from customer
else if (@str is not null )
exec('
select * from customer
where ' + @str + '
')
CREATE PROCEDURE sp_displaychosencustomers
@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)
AS
begin
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = 'ID = ' + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + ' AND NAME = ''' + @name + ''''
else if ( (@name is not null) AND @id = 0 )
SET @str ='NAME = ''' + @name + ''''
if ( ( @birth <> DateTime.Today ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + ' AND Birth = ''' + @birth + ''''
else if ( @birth <> DateTime.Today AND @id = 0 AND @name is null )
SET @str = ( 'Birth= '' + @birth + ''' )
if ( @address is not null AND ( @id <> 0 OR @name is not null OR @birth <> DateTime.Today) )
SET @str = @str + ' AND ADDRESS = ''' + @address + ''''
else if ( @address is not null AND @id = 0 AND @name is null AND @birth = DateTime.Today )
SET @str = 'Address = ''' + @address + ''''
if ( @phone is not null AND ( @id = 0 OR @name is not null OR @birth <> DateTime.Today OR @address is not null ))
SET @str = @str + ' AND PHONE = ''' + @phone + ''''
else if ( @phone is not null AND @id = 0 AND @name is null AND @birth = DateTime.Today AND @address is null )
SET @str = 'PHONE= ''' + @phone + ''''
if ( @state is not null AND ( @id <> 0 OR @name is not null OR @birth <> DateTime.Today OR @address is not null OR @phone is not null ))
SET @str = @str + ' AND STATE = ''' + @state + ''''
else if ( @state is not null AND @id = 0 AND @name is null AND @birth = DateTime.Today AND @address is null AND @phone is null )
SET @str = 'STATE = ' + @state + '''' if (@str is null)
select * from customer
else if (@str is not null )
exec('select * from customer where '+@str)
end
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 17
列名 'ID = ' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 19
列名 ' AND ( NAME = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 19
列名 '\' )' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 21
列名 'NAME = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 21
列名 '\'' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 22
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 23
列名 ' AND ( Birth = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 23
列名 '\' )' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 24
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 25
列名 'Birth= \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 25
列名 '\'' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 26
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 28
列名 ' AND ( ADDRESS = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 28
列名 '\' )' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 29
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 31
列名 'Address = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 31
列名 '\'' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 32
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 34
列名 ' AND ( PHONE = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 34
列名 '\' )' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 35
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 37
列名 'PHONE= \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 37
列名 '\'' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 38
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 41
列名 ' AND ( STATE = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 41
列名 '\' )' 无效。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 42
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 45
列名 'STATE = \'' 无效。
Msg 207, Level 16, State 1, Procedure sp_displaychosencustomers, Line 45
列名 '\'' 无效。
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 20
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 22
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 24
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 26
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 28
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 30
无法绑定由多个部分组成的标识符 "DateTime.Today"。
Msg 4104, Level 16, State 1, Procedure sp_displaychosencustomers, Line 32
无法绑定由多个部分组成的标识符 "DateTime.Today"。
CREATE PROCEDURE sp_displaychosencustomers
@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)
AS
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = "ID = " + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + " AND ( NAME = \'" + @name + "\' )"
else if ( (@name is not null) AND @id = 0 )
SET @str = ("NAME = \'" + @name + "\'" )
if ( ( @birth <> DateTime.Today ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + " AND ( Birth = \'" + @birth + "\' )"
else if ( @birth <> DateTime.Today AND @id = 0 AND @name is null )
SET @str = ( "Birth= \'" + @birth + "\'" )
if ( @address is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today) )
SET @str = @str + " AND ( ADDRESS = \'" + @address + "\' )"
else if ( @address is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today )
SET @str = ( "Address = \'" + @address + "\'" )
if ( @phone is not null AND ( @id = 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null ))
SET @str = @str + " AND ( PHONE = \'" + @phone + "\' )"
else if ( @phone is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null )
SET @str = ( "PHONE= \'" + @phone + "\'" )
if ( @state is not null AND ( @id <> 0 OR @name is not null
OR @birth <> DateTime.Today OR @address is not null
OR @phone is not null ))
SET @str = @str + " AND ( STATE = \'" + @state + "\' )"
else if ( @state is not null AND @id = 0 AND @name is null
AND @birth = DateTime.Today AND @address is null
AND @phone is null )
SET @str = ( "STATE = \'" + @state + "\'" ) if (@str is null)
select * from customer
else if (@str is not null )
select * from customer
where exists( select @str)
@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)
AS
begin
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = 'ID = ' + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + ' AND NAME = ''' + @name + ''''
else if ( (@name is not null) AND @id = 0 )
SET @str ='NAME = ''' + @name + ''''
if ( ( @birth <> getdate() ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + ' AND Birth = ''' + @birth + ''''
else if ( @birth <> getdate() AND @id = 0 AND @name is null )
SET @str = ( 'Birth= '' + @birth + ''' )
if ( @address is not null AND ( @id <> 0 OR @name is not null OR @birth <> getdate()) )
SET @str = @str + ' AND ADDRESS = ''' + @address + ''''
else if ( @address is not null AND @id = 0 AND @name is null AND @birth = getdate() )
SET @str = 'Address = ''' + @address + ''''
if ( @phone is not null AND ( @id = 0 OR @name is not null OR @birth <> getdate() OR @address is not null ))
SET @str = @str + ' AND PHONE = ''' + @phone + ''''
else if ( @phone is not null AND @id = 0 AND @name is null AND @birth = getdate() AND @address is null )
SET @str = 'PHONE= ''' + @phone + ''''
if ( @state is not null AND ( @id <> 0 OR @name is not null OR @birth <> getdate() OR @address is not null OR @phone is not null ))
SET @str = @str + ' AND STATE = ''' + @state + ''''
else if ( @state is not null AND @id = 0 AND @name is null AND @birth = getdate() AND @address is null AND @phone is null )
SET @str = 'STATE = ' + @state + '''' if (@str is null)
select * from customer
else if (@str is not null )
exec('select * from customer where '+@str)
end
如果是取当前日期, 则用 CONVERT(varchar(10), GETDATE(), 120)
CREATE PROCEDURE sp_displaychosencustomers
@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5)
AS
begin
DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = 'ID = ' + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + ' AND NAME = ''' + @name + ''''
else if ( (@name is not null) AND @id = 0 )
SET @str ='NAME = ''' + @name + ''''
if ( ( datediff(dd,@birth,getdate())!=0 ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + ' AND Birth = ''' + @birth + ''''
else if ( datediff(dd,@birth,getdate())!=0 AND @id = 0 AND @name is null )
SET @str = ( 'Birth= '' + @birth + ''' )
if ( @address is not null AND ( @id <> 0 OR @name is not null OR datediff(dd,@birth,getdate())!=0 ))
SET @str = @str + ' AND ADDRESS = ''' + @address + ''''
else if ( @address is not null AND @id = 0 AND @name is null AND datediff(dd,@birth,getdate())=0 )
SET @str = 'Address = ''' + @address + ''''
if ( @phone is not null AND ( @id = 0 OR @name is not null OR datediff(dd,@birth,getdate())!=0 OR @address is not null ))
SET @str = @str + ' AND PHONE = ''' + @phone + ''''
else if ( @phone is not null AND @id = 0 AND @name is null AND datediff(dd,@birth,getdate())=0 AND @address is null )
SET @str = 'PHONE= ''' + @phone + ''''
if ( @state is not null AND ( @id <> 0 OR @name is not null OR datediff(dd,@birth,getdate())!=0 OR @address is not null OR @phone is not null ))
SET @str = @str + ' AND STATE = ''' + @state + ''''
else if ( @state is not null AND @id = 0 AND @name is null AND datediff(dd,@birth,getdate())=0 AND @address is null AND @phone is null )
SET @str = 'STATE = ' + @state + '''' if (@str is null)
select * from customer
else if (@str is not null )
exec('select * from customer where '+@str)
end
CREATE PROCEDURE sp_displaychosencustomers
@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5) AS DECLARE @str varchar(255)
if ( @id <> 0)
SET @str = 'ID = ' + @id
if ( (@name is not null) AND @id <> 0 )
SET @str = @str + ' AND ( NAME = ''' + @name + ''' ) '
else if ( (@name is not null) AND @id = 0 )
SET @str = 'NAME = ''' + @name + ''' ) '
if ( ( @birth <> getdate() ) AND ( @id <> 0 OR ( @name is NOT null ) ))
SET @str = @str + ' AND ( Birth = ''' + @birth + ''' ) '
else if ( @birth <> getdate() AND @id = 0 AND @name is null )
SET @str = 'Birth= ''' + @birth + ''' )'
if ( @address is not null AND ( @id <> 0 OR @name is not null OR @birth <> getdate()) )
SET @str = @str + ' AND ( ADDRESS = ''' + @address + ''') '
else if ( @address is not null AND @id = 0 AND @name is null AND @birth = getdate() )
SET @str = 'Address = ''' + @address + ''' ) '
if ( @phone is not null AND ( @id = 0 OR @name is not null OR @birth <> getdate() OR @address is not null ))
SET @str = @str + ' AND ( PHONE = '''+ @phone + ''' ) '
else if ( @phone is not null AND @id = 0 AND @name is null AND @birth = getdate() AND @address is null )
SET @str = 'PHONE= '''+ @phone + ''' ) '
if ( @state is not null AND ( @id <> 0 OR @name is not null OR @birth <> DateTime.Today OR @address is not null OR @phone is not null ))
SET @str = @str + ' AND ( STATE = '''+ @state + ''' )'
else if ( @state is not null AND @id = 0 AND @name is null AND @birth = DateTime.Today AND @address is null AND @phone is null )
SET @str = 'STATE = ''' + @state + ''' )' if (@str is null)
select * from customer
else if (@str is not null )
exec('select * from customer where '+@str)
@id int,
@name varchar(50),
@birth datetime,
@address varchar(50),
@phone varchar(50),
@state varchar(5) AS
DECLARE @SQL nvarchar(4000)
SET @SQL = 'select * from customer where 1=1'
IF @id<>0
SET @SQL=@SQL+' and id='+ cast(@id as varchar(50))
IF @name is not null
SET @SQL=@SQL+' and Name ='''+@name+''''
IF @birth<>getdate()
SET @SQL=@SQL+' and Birth='''+@birth+''''
IF @address is not null
SET @SQL=@SQL+' and ADDRESS='''+@address+''''
IF @phone is not null
SET @SQL=@SQL+' and phone='''+@phone+''''
IF @state is not null
SET @SQL=@SQL+' and STATE='''+@state+''''exec(@SQL)