CREATE procedure sp_test @part nvarchar(10) as declare @strWhere nvarchar(1000) declare @basicSql nvarchar(1000) declare @strSql nvarchar(1000) declare @aa nvarchar(1000) begin set @strWhere = 'datediff(day,regdate,getdate())=0 and part='''+@part+''' ' set @basicSql = 'select top 1 id from tUser where ' + @strWhere + 'order by age asc' if exists(select @basicSql)--这边出错,怎样执行一个动态语句呢? begin print('is null') return set @strSql = @basicSql end else begin print ('is not null') return set @strWhere = 'datediff(day,regdate,getdate())=1 and part='''+@part+''' ' set @strSql = 'select top 1 * from tUser where ' + @strWhere + 'order by age asc' end exec sp_executesql @strSql end GO
其实用一条语句就行了 exec(' select top 1 id from tUser a where not exists(select 1 from tUser where datediff(day,regdate,a.regdate)=0 and regdate>a.regdate) '+@strwhere+' order by regdate desc')
改一点exec(' select top 1 id from tUser a where not exists(select 1 from tUser where datediff(day,regdate,a.regdate)=0 and regdate>a.regdate) '+@strwhere+' order by a.regdate desc')
if exists(select @basicSql)--这边出错,怎样执行一个动态语句呢?
改一下就行了exec(' select top 1 id,name,age,其他字段 from tUser a where not exists(select 1 from tUser where datediff(day,regdate,a.regdate)=0 and age>a.age) '+@strwhere+' order by a.regdate desc')
and age>a.age 这里改了啊
如果能保证昨天有数据,那么很简单了 create procedure sp_test as begin declare @i int select @i = count(*) from tUser where datediff(day,regdate,getdate())=0 if @i > 0 select top 1 id,username,age,regdate from tUser where datediff(day,regdate,getdate())=0 order by age else select top 1 id,username,age,regdate from tUser where datediff(day,regdate,getdate())=1 order by age end
@part nvarchar(10)
as
declare @strWhere nvarchar(1000)
declare @basicSql nvarchar(1000)
declare @strSql nvarchar(1000)
declare @aa nvarchar(1000)
begin
set @strWhere = 'datediff(day,regdate,getdate())=0 and part='''+@part+''' '
set @basicSql = 'select top 1 id from tUser where ' + @strWhere + 'order by age asc'
if exists(select @basicSql)--这边出错,怎样执行一个动态语句呢?
begin
print('is null')
return
set @strSql = @basicSql
end
else
begin
print ('is not null')
return
set @strWhere = 'datediff(day,regdate,getdate())=1 and part='''+@part+''' '
set @strSql = 'select top 1 * from tUser where ' + @strWhere + 'order by age asc'
end
exec sp_executesql @strSql
end
GO
exec('
select top 1 id
from tUser a
where not exists(select 1 from tUser where datediff(day,regdate,a.regdate)=0
and regdate>a.regdate)
'+@strwhere+'
order by regdate desc')
select top 1 id
from tUser a
where not exists(select 1 from tUser where datediff(day,regdate,a.regdate)=0
and regdate>a.regdate)
'+@strwhere+'
order by a.regdate desc')
select top 1 id,name,age,其他字段
from tUser a
where not exists(select 1 from tUser where datediff(day,regdate,a.regdate)=0
and age>a.age)
'+@strwhere+'
order by a.regdate desc')
create procedure sp_test
as
begin
declare @i int
select @i = count(*) from tUser where datediff(day,regdate,getdate())=0 if @i > 0
select top 1 id,username,age,regdate from tUser
where datediff(day,regdate,getdate())=0
order by age
else
select top 1 id,username,age,regdate from tUser
where datediff(day,regdate,getdate())=1
order by age
end