declare @a1 char(4),@a2 char(2),@3 char(4),@a4 char(2)
set ...declare @sql varchar(1000)
set @sql = ' year(日期) > ' + @a1
if @a2=''
set @sql = @sql + ' and 1=1 '
else
set @sql = @sql + ' and month(日期) > '+ @a2
if @a3=''
set @sql = @sql + ' and 1=1 '
else
set @sql = @sql + ' and year(日期) < '+@a3
if @a4=''
set @sql = @sql + ' and 1=1 '
else
set @sql = @sql + ' and month(日期) < '+@a4
set @sql = ' select * from 表 where ' + @sql
exec(@sql)
set ...declare @sql varchar(1000)
set @sql = ' year(日期) > ' + @a1
if @a2=''
set @sql = @sql + ' and 1=1 '
else
set @sql = @sql + ' and month(日期) > '+ @a2
if @a3=''
set @sql = @sql + ' and 1=1 '
else
set @sql = @sql + ' and year(日期) < '+@a3
if @a4=''
set @sql = @sql + ' and 1=1 '
else
set @sql = @sql + ' and month(日期) < '+@a4
set @sql = ' select * from 表 where ' + @sql
exec(@sql)
解决方案 »
- 求SQL一条语句..在线等 谢谢
- 写一个update语句 应该简单,可我就不会,该省省了
- sqlserver2008 在与sqlserver 建立连接时出现与网络相关的或特定于实例的错误
- 如何使用sql2000调试存储过程?
- SQL数据库置疑问题
- 我在SQL SERVER中写了个作业来定时备份数据库,必须要启动SQL SERVER Agent服务吗?启动了这个服务后跟平常的SQL SERBER 服务有什么区别
- 将Sqlserver导出到Excel表中,如何覆盖原Excel表中的数据
- 请j9988进来看看(关于最小二乘法)
- 如何实现极小化的数据库程序?
- 200MB的excel用sql自带的导入,显示内存不足
- 比如现在的日期是2003-3-7,我想得到20030307怎么不行?
- 这个SQL语句会出错?大力看一下!
CREATE PROC AAA
@year1 varchar(4),@year2 varchar(4),@month1 varchar(2),@month2 varchar(2)
As
declare @years1 varchar(4),@years2 varchar(4),@months1 varchar(2),@months2 varchar(2)
select @years1=@year1,@years2=@year2,@months1=@month1,@months2=@month2
if @months1='' @month1='01'
if @years2='' @year2=@year1
if @months2='' @month2='01'
if @months1<>'' and @years2=''
@month2=@month1
select * from 表 where convert(varchar(6),date1,112) between @year1+@month1 and @year2+@month2
Go
@byear int=null, --要查询的开始年份
@bmonth int=null, --要查询的开始月份
@eyear int=null, --要查询的结束年份
@emonth int=null --要查询的结束月份
as
set nocount on
declare @tj varchar(8000),@str varchar(20)
if isnull(@byear,0)<>0
if isnull(@bmonth,0)<>0
set @str=cast(@byear as varchar)+'-'
+cast(@bmonth as varchar)+'-1'
set @str=cast(@byear as varchar)+'-1-1'
if isdate(@str)=0
set @tj=''
else
set @tj='datediff(day,[time],'''+@str+''')>=0'if isnull(@eyear,0)<>0
if isnull(@emonth,0)<>0
set @str=cast(@eyear as varchar)+'-'
+cast(@emonth as varchar)+'-1'
set @str=cast(@eyear as varchar)+'-1-1'
if isdate(@str)<>0
set @tj='where '+case @tj when '' then '' else @tj+' and ' end
+ 'datediff(day,[time],'''+@str+''')<=0'
exec('selct * from table1 '+@tj)
set nocount off
go
Dim iRe As ADODB.RecordsetSet iCmd = New ADODB.Command
With iCmd
.ActiveConnection = 数据库连接字符串
.CommandType = adCmdStoredProc
.CommandText = "p_qry"
.Parameters.Refresh
.Parameters("@byear")=textbox1.text
.Parameters("@bmonth")=textbox2.text
.Parameters("@eyear")=textbox3.text
.Parameters("@emonth")=textbox4.text
set iRe=.Execute
End With
Begin
if 'text3.text.trim' is not null
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'%' and covert(varhcar(30),a.时间,112)<='convert(varchar(20)cast('textbox3.text.trim '+'textbox4.text.trim' as smalldatatime),112)'
else
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'%'end
Begin
if 'text3.text.trim' is not null
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'%' and covert(varhcar(30),a.时间,112)<='convert(varchar(20),cast('textbox3.text.trim '+'textbox4.text.trim' as smalldatatime),112)'
else
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'%'end
imports system.data
imports system.data.sqlclientDim
Dim iCmd As new SqlCommand
Dim prmSql as SqlParameter
With iCmd
.SqlConnection = 数据库连接字符串
.CommandType = CommandType.StoredProcedure
.CommandText = "p_qry"
End WithprmSql=iCmd.Parameters.Add("@bmonth",textbox2.text.trim())
prmSql.Direction=ParameterDirection.Input
prmParameter.SqlDbType=SqlDbType.varchar
...............'以此类推
...............Dim SqlDa as new sqldataAdapter(iCmd)
Dim DsDataSet as new DataSet()
sqlDa.Fill(DsDataSet)
DsDataSet.Dispose()
SqlDa.Dispose()
Dim iRe As ADODB.RecordsetSet iCmd = New ADODB.Command
With iCmd
.ActiveConnection = 数据库连接字符串
.CommandType = adCmdStoredProc
.CommandText = "p_qry"
.Parameters.Refresh
.Parameters("@byear")=textbox1.text
.Parameters("@bmonth")=textbox2.text
.Parameters("@eyear")=textbox3.text
.Parameters("@emonth")=textbox4.text
set iRe=.Execute
End With
数字肯定可以转换为字符,所以前面不用做处理.if isdate(@str)=0 --这句就可以判断出给出的参数是否是有效的年月了.
set @tj=''
至于程序中的错误处理,由楼主自行考虑.
1.if isnull(@bmonth,0)<>0
set @str=cast(@byear as varchar)+'-'
+cast(@bmonth as varchar)+'-1'
set @str=cast(@byear as varchar)+'-1-1' //这行不用加else吗?
2.if isdate(@str)=0
set @tj=''
else
set @tj='datediff(day,[time],'''+@str+''')>=0'//这行什么作用?
3.
if isdate(@str)<>0
set @tj='where '+case @tj when '' then '' else @tj+' and ' end
+ 'datediff(day,[time],'''+@str+''')<=0'
这个我也看不懂什么意思,我很多sql用法都没有见过,惭愧。
set @str=cast(@byear as varchar)+'-'
+cast(@bmonth as varchar)+'-1'
set @str=cast(@byear as varchar)+'-1-1' //这行不用加else吗?这里我原来的思维错了,应该是要用else.
2.if isdate(@str)=0
set @tj=''
else
set @tj='datediff(day,[time],'''+@str+''')>=0'//这行什么作用?
这行是生成条件语句.就是说,如果你传递的年份或年份+月份是有效的话,就生成第一个判断条件:开始日期3.
if isdate(@str)<>0
set @tj='where '+case @tj when '' then '' else @tj+' and ' end
+ 'datediff(day,[time],'''+@str+''')<=0'和第2一样,判断传递的参数是否有效,如果有效.生成第二个判断条件.
set @str=cast(@byear as varchar)+'-'
+cast(@bmonth as varchar)+'-1'
set @str=cast(@byear as varchar)+'-1-1' //这行不用加else吗?这里我原来的思维错了,应该是要用else.
2.if isdate(@str)=0
set @tj=''
else
set @tj='datediff(day,[time],'''+@str+''')>=0'//这行什么作用?
这行是生成条件语句.就是说,如果你传递的年份或年份+月份是有效的话,就生成第一个判断条件:开始日期3.
if isdate(@str)<>0
set @tj='where '+case @tj when '' then '' else @tj+' and ' end
+ 'datediff(day,[time],'''+@str+''')<=0'和第2一样,判断传递的参数是否有效,如果有效.生成第二个判断条件.
@byear int=null, --要查询的开始年份
@bmonth int=null, --要查询的开始月份
@eyear int=null, --要查询的结束年份
@emonth int=null --要查询的结束月份
as
set nocount on
declare @tj varchar(8000),@str varchar(20)
if isnull(@byear,0)<>0
if isnull(@bmonth,0)<>0
set @str=cast(@byear as varchar)+'-'
+cast(@bmonth as varchar)+'-1'
else
set @str=cast(@byear as varchar)+'-1-1'
else
set @str=''
if isdate(@str)=1
set @tj='where datediff(day,[time],'''+@str+''')>=0'
else
set @tj=''
if isnull(@eyear,0)<>0
if isnull(@emonth,0)<>0
set @str=cast(@eyear as varchar)+'-'
+cast(@emonth as varchar)+'-1'
else
set @str=cast(@eyear as varchar)+'-1-1'
else
set @str=''
if isdate(@str)=1
set @tj=case @tj when '' then 'where ' else @tj+' and ' end
+ 'datediff(day,[time],'''+@str+''')<=0'exec('select * from table1 '+@tj)
set nocount off
go
set @str=cast(@byear as varchar)+'-'
+cast(@bmonth as varchar)+'-1'
set @str=cast(@byear as varchar)+'-1-1' //这行不用加else吗?这里我原来的思维错了,应该是要用else.
2.if isdate(@str)=0
set @tj=''
else
set @tj='datediff(day,[time],'''+@str+''')>=0'//这行什么作用?
这行是生成条件语句.就是说,如果你传递的年份或年份+月份是有效的话,就生成第一个判断条件:开始日期3.
if isdate(@str)<>0
set @tj='where '+case @tj when '' then '' else @tj+' and ' end
+ 'datediff(day,[time],'''+@str+''')<=0'和第2一样,判断传递的参数是否有效,如果有效.生成第二个判断条件.
'楼主也在.NET区发了问题。
'在你的程序中,只要调用它就行了:
imports system.data
imports system.data.sqlclient
Dim iCmd As new SqlCommand
Dim prmSql as SqlParameter
With iCmd
.SqlConnection = 数据库连接字符串
.CommandType = CommandType.StoredProcedure
.CommandText = "p_qry"
End WithprmSql=iCmd.Parameters.Add("@bmonth",textbox2.text.trim())
prmSql.Direction=ParameterDirection.Input
prmSql.SqlDbType=SqlDbType.varchar '------在数据版的这个地方写错了。
...............'以此类推
...............Dim SqlDa as new sqldataAdapter(iCmd)
Dim DsDataSet as new DataSet()
sqlDa.Fill(DsDataSet)
DsDataSet.Dispose()
SqlDa.Dispose()
'楼主也在.NET区发了问题。
'在你的程序中,只要调用它就行了:
imports system.data
imports system.data.sqlclient
Dim iCmd As new SqlCommand
Dim prmSql as SqlParameter
With iCmd
.SqlConnection = 数据库连接字符串
.CommandType = CommandType.StoredProcedure
.CommandText = "p_qry"
End WithprmSql=iCmd.Parameters.Add("@bmonth",textbox2.text.trim())
prmSql.Direction=ParameterDirection.Input
prmSql.SqlDbType=SqlDbType.int '------在数据版的这个地方写错了。
...............'以此类推
...............Dim SqlDa as new sqldataAdapter(iCmd)
Dim DsDataSet as new DataSet()
sqlDa.Fill(DsDataSet)
DsDataSet.Dispose()
SqlDa.Dispose()
dim str,tj,sSQL as string
if combobox1.text<>"" then
if combobox2.text<>"" then
str=ComboBox1.Text + "-" + ComboBox2.Text + "-1"
Else
str = ComboBox1.Text + "-1-1"
end if
else
str=""
end if
if isdate(str)=true then
tj="where datediff(day,[time],"'"+str+"'")>=0"
else tj=""
end if
if combobox3.text<>"" then
if combobox4.text<>"" then
str=ComboBox3.Text + "-" + ComboBox4.Text + "-1"
Else
str = ComboBox3.Text + "-1-1"
end if
else
str=""
end if
if isdate(str)=true then
tj="case tj when "" then where else tj"+" and end" +"datediff(day,[time],"'"+str+"'")<=0" //这一句很多引号我看不懂什么意思,很有可能出错了。
sSQL="select * from table1" +tj
然后去执行sSQL这个查询语句。以上的步骤是我按照上面存储过程的写法写的,但是老是出错。哪个看得懂vb.net的大侠麻烦帮忙看看吧,我都要着急死了。
Begin
if 'text3.text.trim' is not null
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'%' or covert(varchar(30),a.时间,112) like ''textbox3.text.trim()'+'textbox4.text.trim()'%'
else
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'+ '%'''
End
select a.* from yourTable where covert(varchar(30),a.时间,112) like ''textbox1.text.trim()'+'textbox2.text.trim()'%' or covert(varchar(30),a.时间,112) like ''textbox3.text.trim()'+'textbox4.text.trim()'%'