Update selected rows
Sometimes you need to select a batch of records, and then updated them. The problem is, sometimes you have no idea what records you selected. For example, the script below selects the top 75 rows to send a batch of emails out for a newsletter. We know the criteria, but don’t know exactly what 75 rows the database decided to choose. This example takes that select, and updates the flag that the email was sent, so the row will not be selected again, or skipped over.
select
top 75 p.ProfileID, p.ScreenName ScreenName, p.Active Active, p.Email
Email,
into #NewsletterList
from
Profile p
where
(p.NewsletterSent
= 0)
and
(p.SendWeeklyNewsletter
= 1)
order
by p.ProfileID;
with ProfileNewsletter
as
(
SELECT *,
ROW_NUMBER() OVER(ORDER
BY p.ProfileID)
AS RowNum
FROM Profile p
where NewsletterSent = 0
)
update ProfileNewsletter
set NewsletterSent
= 1
where RowNum <= 75;
select
* from #NewsletterList
drop table #NewsletterList
Case sensitivity
If your database is case-insensitive, and you need to validate a column for case-sensitive reasons, you can do this. An example would be a password. You might not want the user’s account name to be validated for case-sensitivity, as you would not have duplicate names with the same spelling, just different case. But, for security reasons, you may want the password to be case sensitive.
WHERE
CAST(Subscriber AS
varbinary(8))
= CAST(@Subscriber AS
varbinary(8))
AND
CAST(Sub_Password
AS varbinary(15))
= CAST(@Sub_Password
AS varbinary(15))
AND Subscriber = @Subscriber
Set dropdown selected item
You have an option list, say, displaying the States of the Union. You need to show the state that has already been selected by the user. If you can pass in to the JavaScript side of the world the value for the state, you can do this. The variable ‘disiplayState’ contains the value.
$("#slState option[value='"
+ displayState + "']").attr('selected',
'selected');