September 13, 2018 at 12:36 pm
Over the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:
set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
September 13, 2018 at 12:43 pm
thelenj - Thursday, September 13, 2018 12:36 PMOver the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
The 2 set statements can be rewritten as a single select statement.set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
SELECT TOP (1)
@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE id = @id;
September 13, 2018 at 12:50 pm
DesNorton - Thursday, September 13, 2018 12:43 PMthelenj - Thursday, September 13, 2018 12:36 PMOver the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
The 2 set statements can be rewritten as a single select statement.
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
SELECT TOP (1)
@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE id = @id;
Excellent.
Would this work even better?
SELECT TOP (1)
@id = Id
,@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE IsSent=0
I need the id to update the record if it is processed. This helps. Thanks.
September 13, 2018 at 1:09 pm
SELECT TOP (1)
without an ORDER BY is a somewhat suspect practice.
If more than one row matches the WHERE clause, the result which is returned is likely to change from one execution to another, because SQL Server query results do not have a default order.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 13, 2018 at 1:10 pm
I need the id to update the record if it is processed. This helps. Thanks.
Can't you do this all in a single step?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 13, 2018 at 1:21 pm
Phil Parkin - Thursday, September 13, 2018 1:09 PMSELECT TOP (1)
without an ORDER BY is a somewhat suspect practice.If more than one row matches the WHERE clause, the result which is returned is likely to change from one execution to another, because SQL Server query results do not have a default order.
In this case I'm just sending an email if the record is found.
The order is unimportant - it is very low volume and will run from a SQL Job that checks for candidates every 5 minutes.
But yes, you're correct - TOP should have an order by clause but here TOP is just limiting the result to 1 record and that is its only function.
Is there a best practice for that I'm missing?
September 13, 2018 at 1:27 pm
thelenj - Thursday, September 13, 2018 12:50 PMDesNorton - Thursday, September 13, 2018 12:43 PMthelenj - Thursday, September 13, 2018 12:36 PMOver the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
The 2 set statements can be rewritten as a single select statement.
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
SELECT TOP (1)
@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE id = @id;Excellent.
Would this work even better?
SELECT TOP (1)
@id = Id
,@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE IsSent=0I need the id to update the record if it is processed. This helps. Thanks.
My bad. I missed that the 2nd and 3rd queries used the result of the 1st query.
Yes. You can definitely do it as 1 query.
September 13, 2018 at 1:29 pm
thelenj - Thursday, September 13, 2018 1:21 PMPhil Parkin - Thursday, September 13, 2018 1:09 PMSELECT TOP (1)
without an ORDER BY is a somewhat suspect practice.If more than one row matches the WHERE clause, the result which is returned is likely to change from one execution to another, because SQL Server query results do not have a default order.
In this case I'm just sending an email if the record is found.
The order is unimportant - it is very low volume and will run from a SQL Job that checks for candidates every 5 minutes.
But yes, you're correct - TOP should have an order by clause but here TOP is just limiting the result to 1 record and that is its only function.
Is there a best practice for that I'm missing?
You could add an ORDER BY id. Assuming that id is just an incrementing integer value, you would be processing the mails in a FIFO method.
September 13, 2018 at 8:01 pm
One subtle thing to be careful of is the difference between these two styles if you don't get any rows as a result from the query. Using SET will result in the variables being changed to NULL, but doing it in a SELECT will leave the variables unchanged. If that's going to upset your code, make sure you reset the variables to NULL first.
September 14, 2018 at 5:47 am
andycadley - Thursday, September 13, 2018 8:01 PMOne subtle thing to be careful of is the difference between these two styles if you don't get any rows as a result from the query. Using SET will result in the variables being changed to NULL, but doing it in a SELECT will leave the variables unchanged. If that's going to upset your code, make sure you reset the variables to NULL first.
Ah, thank you for that heads up!
In my use case I only call the procedure every 5 minutes or so and I run it once if a record is found so there is no real difference; however, your point here would have been a difficult bug for me to figure out had this been put in a loop or something. (This is a very low volume temporary solution as the application will be replaced within the next year)
Nice.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply