Is there a way to do this without dynamic sql?

  • select

    Queue.ScheduledTaskQueueId QueueId,

    Queue.QueueMaximumLength,

    Queue.IsLargeQueue

    from

    ScheduledTaskQueue Queue

    I use the above SQL to find a list of queues to fill with scheduled tasks. Based off of the QueueMaximumLength value, I need to get the TOP X records from my ScheduledTask table ordered by the ScheduledTask.RunDateTime column.

    Is there a way to do this without dynamic SQL?

  • TOP clause accepts variables and expressions.

    Example:

    SELECT TOP(DATEPART( HH, GETDATE()))

    *

    FROM master.sys.all_columns

    I'm not sure if that helps you because I'm not sure on how do you want to limit the rows.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Ok, right..but if I have multiple queues, each with a different MaximumQueueLength, how would I use TOP without using either a cursor or dynamic SQL?

  • I am not sure what you are trying to accomplish. The query you posted doesn't appear to be what you are asking about, or I just don't understand the question.

  • Ok, let me give the whole picture.

    I have three tables - Queue, Tasks, and QueueTaskLink

    CREATE TABLE Queue

    (

    QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    MaximumQueueLength INT NOT NULL

    )

    CREATE TABLE Tasks

    (

    TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    TaskName NVARCHAR(100) NOT NULL,

    RunDatetime DATETIME NOT NULL

    )

    CREATE TABLE QueueTaskLink

    (

    QueueTaskLinkId NOT NULL PRIMARY KEY IDENTITY(1,1),

    QueueId INT NOT NULL,

    TaskId INT NOT NULL

    )

    Task is a table that contains a list of scheduled tasks. This table gets populated from various sources. Each task belongs in a queue, and each queue has a maximum number of tasks that can be run at a given time. I have a process that needs to get a list of all tasks that can be run for each queue.

    Previously, I was accomplishing this with a SSIS task that gets a list of all queues, loops through each queue to build a string containing the select statement to get TOP X Tasks, where X is the MaximumQueueLength.

    This feels cludgy to me and I'm wondering if there isn't a better way to do it.

  • TheGreenShepherd (6/12/2014)


    Ok, let me give the whole picture.

    I have three tables - Queue, Tasks, and QueueTaskLink

    CREATE TABLE Queue

    (

    QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    MaximumQueueLength INT NOT NULL

    )

    CREATE TABLE Tasks

    (

    TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    TaskName NVARCHAR(100) NOT NULL,

    RunDatetime DATETIME NOT NULL

    )

    CREATE TABLE QueueTaskLink

    (

    QueueTaskLinkId NOT NULL PRIMARY KEY IDENTITY(1,1),

    QueueId INT NOT NULL,

    TaskId INT NOT NULL

    )

    Task is a table that contains a list of scheduled tasks. This table gets populated from various sources. Each task belongs in a queue, and each queue has a maximum number of tasks that can be run at a given time. I have a process that needs to get a list of all tasks that can be run for each queue.

    Previously, I was accomplishing this with a SSIS task that gets a list of all queues, loops through each queue to build a string containing the select statement to get TOP X Tasks, where X is the MaximumQueueLength.

    This feels cludgy to me and I'm wondering if there isn't a better way to do it.

    Okay, so where does ScheduledTaskQueue table come in? That is the table in your query in your initial post.

    Now that we have some of the tables, perhaps some sample data and the expected results based on the sample data and we may be able help you better.

  • Forget about the table I initially mentioned. I generalized it down to those in my last post (and, actually, I'm going to generalize it even further to get rid of my QueueTaskLink, so that Task just has a QueueId foreign key column).

    From those tables, you could insert these records:

    INSERT INTO Queue (MaximumQueueLength) VALUES (3) --QueueId 1

    INSERT INTO Queue (MaximumQueueLength) VALUES (5) --QueueId 2

    INSERT INTO Queue (MaximumQueueLength) VALUES (1) --QueueId 3

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 1', 1, GETDATE()+1)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 2', 1, GETDATE()+2)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 3', 1, GETDATE()+3)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 4', 1, GETDATE()+4)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 5', 1, GETDATE()+5)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report A', 2, GETDATE()+6)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report B', 2, GETDATE()+7)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report C', 2, GETDATE()+8)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report D', 2, GETDATE()+9)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report E', 2, GETDATE()+1)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report F', 2, GETDATE()+2)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report G', 2, GETDATE()+3)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Big Export 1', 3, GETDATE())

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Big Export 2', 3, GETDATE()+1)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Big Export 3', 3, GETDATE()+2)

    Ok, so now we have our tables populated. Queue 1 can handle 3 tasks at a time. Queue 2 handles 5. Queue 3 handles 1.

    For each queue, I want to - select Top QueueMaximumLength TaskId from Task order by RunDatetime.

    So, for Queue 1, I should get TaskId's 1,2,3 and not 4,5.

    For Queue 2, I should get TaskId's 6,7,10,11,12 and not 8,9.

    For Queue 3, I should get TaskId 14 and not 15,16.

    Make sense?

  • The inserts don't match the table definitions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • In an empty database run the following and let us know if this is what you are looking to achieve.

    CREATE TABLE dbo.[Queue]

    (

    QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    MaximumQueueLength INT NOT NULL

    )

    CREATE TABLE dbo.Tasks

    (

    TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    TaskName NVARCHAR(100) NOT NULL,

    QueueId int,

    RunDatetime DATETIME NOT NULL

    )

    go

    INSERT INTO dbo.[Queue] (MaximumQueueLength) VALUES (3) --QueueId 1

    INSERT INTO dbo.[Queue] (MaximumQueueLength) VALUES (5) --QueueId 2

    INSERT INTO dbo.[Queue] (MaximumQueueLength) VALUES (1) --QueueId 3

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 1', 1, GETDATE()+1)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 2', 1, GETDATE()+2)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 3', 1, GETDATE()+3)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 4', 1, GETDATE()+4)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Excel Export 5', 1, GETDATE()+5)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report A', 2, GETDATE()+6)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report B', 2, GETDATE()+7)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report C', 2, GETDATE()+8)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report D', 2, GETDATE()+9)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report E', 2, GETDATE()+1)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report F', 2, GETDATE()+2)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Report G', 2, GETDATE()+3)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Big Export 1', 3, GETDATE())

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Big Export 2', 3, GETDATE()+1)

    INSERT INTO Tasks (TaskName, QueueId, RunDatetime)

    VALUES

    ('Big Export 3', 3, GETDATE()+2)

    select * from dbo.[Queue];

    select * from dbo.Tasks order by QueueId, RunDatetime;

    select

    q.QueueId,

    ca.TaskId,

    ca.TaskName,

    ca.RunDatetime

    from

    dbo.[Queue] q

    cross apply (select top (q.MaximumQueueLength) t.TaskId, t.TaskName, t.RunDatetime from dbo.Tasks t where t.QueueId = q.QueueId order by t.RunDatetime asc)ca

    ;

    go

    drop table dbo.[Queue];

    drop table dbo.Tasks;

    go

  • Yep, exactly. Thanks.

  • Your insert statements, etc are a complete mess! That didn't help at all, but I hope what is posted below will. I do not claim this is the best, or even an efficient way to do this. And because I had difficulty prepping the that it is likely the results for task/queue are off. The point is to demonstrate a suggested way to pull off what you are looking to do.

    set nocount on

    declare

    @SqlStmt nvarchar(max),

    @Roller int,

    @MaximumQueueLength int

    Declare @RollerStore table(QueueID int, MaximumQueueLength int)

    create table #Results (QueueID int, TaskID Int, RunDateTime date)

    insert @RollerStore select QueueID,MaximumQueueLength from Queue

    while exists (select 1 from @RollerStore)

    begin

    select top 1 @Roller=QueueID, @MaximumQueueLength=MaximumQueueLength from @RollerStore

    set @SqlStmt='insert into #Results

    select top '+convert(varchar(5),@MaximumQueueLength)+' T0.QueueID, T0.TaskID, t1.RunDatetime

    from tasks t1 inner join QueueTaskLink T0

    on T0.TaskID=T1.TaskID

    where T0.QueueID='+convert(varchar(5),@Roller)+'

    order by RunDatetime'

    exec sp_executesql @SqlStmt

    delete @RollerStore where QueueID=@roller

    end

    select * from #Results

    The results are:

    QueueID TaskID RunDateTime

    ----------- ----------- -----------

    1 1 2014-06-14

    1 2 2014-06-15

    1 3 2014-06-16

    2 13 2014-06-14

    2 14 2014-06-15

    2 15 2014-06-16

    2 9 2014-06-19

    2 10 2014-06-20

    3 4 2014-06-13

    Cheers

  • Here are the create/insert statements I used in my example:

    drop table Queue

    drop table Tasks

    drop table QueueTaskLink

    CREATE TABLE Queue(

    QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    MaximumQueueLength INT NOT NULL)

    CREATE TABLE Tasks(

    TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

    TaskName NVARCHAR(100) NOT NULL,

    RunDatetime DATETIME NOT NULL)

    CREATE TABLE QueueTaskLink(

    QueueTaskLinkId int NOT NULL PRIMARY KEY IDENTITY(1,1),

    QueueId INT NOT NULL,

    TaskId INT NOT NULL)

    INSERT INTO Queue (MaximumQueueLength) VALUES (3) --QueueId 1

    INSERT INTO Queue (MaximumQueueLength) VALUES (5) --QueueId 2

    INSERT INTO Queue (MaximumQueueLength) VALUES (1) --QueueId 3

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Excel Export 1', GETDATE()+1)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Excel Export 2', GETDATE()+2)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Excel Export 3', GETDATE()+3)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Big Export 1', GETDATE())

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Big Export 2', GETDATE()+1)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Big Export 3', GETDATE()+2)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Excel Export 4', GETDATE()+4)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Excel Export 5', GETDATE()+5)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report A', GETDATE()+6)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report B', GETDATE()+7)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report C', GETDATE()+8)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report D', GETDATE()+9)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report E', GETDATE()+1)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report F', GETDATE()+2)

    INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report G', GETDATE()+3)

    insert QueueTaskLink values(1,1)

    insert QueueTaskLink values(1,2)

    insert QueueTaskLink values(1,3)

    insert QueueTaskLink values(1,7)

    insert QueueTaskLink values(1,8)

    insert QueueTaskLink values(2,9)

    insert QueueTaskLink values(2,10)

    insert QueueTaskLink values(2,11)

    insert QueueTaskLink values(2,12)

    insert QueueTaskLink values(2,13)

    insert QueueTaskLink values(2,14)

    insert QueueTaskLink values(2,15)

    insert QueueTaskLink values(3,6)

    insert QueueTaskLink values(3,5)

    insert QueueTaskLink values(3,4)

    Cheers

  • Hey thanks for the compliment. I was doing my best, trying to type it in on my phone on the train home from work. But thanks again. Also, Lynn's cross apply is what I was looking for. Thanks.

  • Ah, I see. Once I remarked out two of the select statements in Lynn's query before the cross apply I see just the desired result. Much more graceful method than mine.

    Cheers

  • jfogel (6/13/2014)


    Ah, I see. Once I remarked out two of the select statements in Lynn's query before the cross apply I see just the desired result. Much more graceful method than mine.

    The first two selects were to display the initial data after the inserts. I used that to do a visual verification of my results.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply