I can't write such a simple sentence, I'm depressed

8424cyy747

declare @pagesize varchar(10)-----Display the size of each page of data
declare @pageindex varchar(20)-----current index page
declare @testtime datetime -------Establish test time
select @pagesize=10,@pageindex=9000,@testtime=getdate()

declare @sql nvarchar(1000)
set @sql='select top'+@pagesize+' *,datediff(ms,'+cast(@testtime as varchar(30))+',getdate()) from #testtable where tid not in (select top'+cast (@pagesize * (@pageindex-1) as varchar(30))+' a.tid from #testtable a order by a.tid desc) order by tid desc'
exec (@sql)
-------------------------------------------------- -------------
I remove the sentence datediff(ms,'+cast(@testtime as varchar(30))+',getdate()) and it's fine. I want to count the query time, how to write it? ? Note that the query time may be very small! !

waxiaoning

datediff(ms,'''+cast(@testtime as varchar(30))+''',getdate())

8424cyy747

Is it really okay, don’t you understand? ? Why add''? ? ? ? ?

gaoqingfeng

Try:
set @sql='select top'+@pagesize+' *,datediff(ms,'''+cast(@testtime as varchar(30))+''',getdate()) from #testtable where tid not in (select top'+cast(@pagesize * (@pageindex-1) as varchar(30))+' a.tid from #testtable a order by a.tid desc) order by tid desc'

knightzk

Use quotation marks
Because what you passed in is a string of time, it must be enclosed in single quotes

gaoqingfeng

select top 10 *,datediff(ms,01 6 2017 1:14PM,getdate()) from #testtable where tid not in (select top 89990 a.tid from #testtable a order by a.tid desc) order by tid desc

Take out your SQL analysis, datediff(ms,01 6 2017 1:14PM,getdate()), there are two single quotes missing in the middle, so single quotes should be added, and in the dynamic statement, two single quotes replace one apostrophe