asked
Getting values from Database instead of using Declare
I am using declare @starttime datetime = '2015-10-28 10:00', @endtime datetime = '2015-10-28 12:00', @interval int = 30 at this stage i would like to retrieve these values from a table instead of declaring them as static values. I have a separate table that has these 3 values (Startdate,Enddate,interval). I tried creating a temp table an then inserting values into it but the naming convention fails to work if i want to use the vales in my further query. my entire query is this and in this i want the startdate, enddate,interval to be fetched from the database instead of being static. create table #booking (start datetime, [end] datetime) insert into #booking values ('2015-10-28 08:00','2015-10-28 08:30'), ('2015-10-28 10:00','2015-10-28 10:30'), ('2015-10-28 10:30','2015-10-28 11:00') declare @starttime datetime = '2015-10-28 08:00', @endtime datetime = '2015-10-28 12:00', @interval int = 30, @slots int select @slots = datediff(minute, @starttime, @endtime)/@interval SELECT TOP (@slots) N=IDENTITY(INT, 1, 1) INTO #Numbers FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns b; select dateadd(minute,((n-1)*@interval),@starttime) as start, dateadd(minute,(n*@interval),@starttime) as [end] into #slots from #numbers select s.*, b.* from #slots s left join #booking b on s.start = b.start and s.[end] = b.[end] where b.start is null drop table #numbers, #booking, #slots
If you just want to set those 3 variables from the first row in your temp table, something like this should do the trick: SELECT TOP(1) @starttime = b.[start] , @endtime = b.[end] , @interval = DATEDIFF(minute, b.[start], b.[end]) FROM #booking b ORDER BY b.[start] ; If that's not what you're looking for, please provide more details so we can offer better advice.
18 People are following this question.
