question

fashraf avatar image
fashraf 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
sql-server-2008temporary-table
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.