Is there a way to do this without dynamic sql?

Question

Is there a way to do this without dynamic sql?

Viewing 5 posts - 16 through 19 (of 19 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

TheGreenShepherd (6/13/2014)
Yep, exactly. Thanks.

Glad it worked. FYI, I did have to make some modify your inserts a bit to get things working. You should see the differences by comparing my code to what you had posted.

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 2

Yeah, thanks. This isn't like my production code. I was just trying to generalize an example while I was on the train home from work. Sorry if it wasn't perfect. I appreciate your help.

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 3

Lynn Pettis (6/13/2014)
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.

I got that. I'm just not even sure what I was thinking. The whole question was how to do it without dynamic SQL and I turn around and propose something based on just that. I'm going to try and avoid touching anything important today!

Cheers

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

jfogel (6/13/2014)
Lynn Pettis (6/13/2014)
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.
I got that. I'm just not even sure what I was thinking. The whole question was how to do it without dynamic SQL and I turn around and propose something based on just that. I'm going to try and avoid touching anything important today!

I know how you feel.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

Another use of LEAD function

SELECT QueueId,TaskId,TaskName,Rundatetime FROM(

SELECT T.QueueId,T.TaskId,T.TaskName,T.RunDateTime, LEAD(taskid,MaximumQueueLength)OVER(PARTITION BY T.QueueId ORDER BY taskid DESC)lid FROM Tasks T

INNER JOIN Queue Q ON T.QueueId=Q.QueueId

)T1 WHERE lid IS NULL ORDER BY TaskName

EDIT: Works with SQL Server 2012 and above

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server