8
votes
Find & Delete Duplicate Records on All Tables in current MSSQL Database
You started out with all your keywords being capitalized and then you abandoned your capitalization haphazardly throughout the script, this makes it a little hard to read because your brain says that ...
- 28.6k
7
votes
Stored procedure to query custom data tables as dynamic SQL
Does this open the door to destructive injection attacks?
It depends, but potentially, yes it can, especially with dynamic SQL, which you should try to avoid unless it is absolutely necessary. You ...
- 20.3k
7
votes
Accepted
Execute every Monday that is not the Monday following last Saturday in month
Sometimes, comments aren't the clearest way to make a script readable:
IF (
(--is it a monday?
SELECT DATEPART(dw,@now)
) = 2
...
- 74.9k
7
votes
Accepted
Science Fiction Wall of Fame (Shame?)
Problem Statement
Tiered Architecture
This database is the first part of the project, I couldn’t really
create a friendly user interface until the database is working. Due to
feature creep ...
- 13.8k
6
votes
Accepted
Coin flips and Dice rolls in a stored procedure
Overall, I think your procedure is very good. It's consistent in layout, capitalisation and style, and well commented. I had no problems reading through it and understanding what it was doing (and how)...
5
votes
Accepted
Executing a stored procedure with optional attributes
The DbConnection class implements the IDbConnection interface, so unless you need something exposed by the Component base class, ...
- 30.8k
5
votes
Accepted
SQL Procedure that will calculate the Last Price paid for a certain Part
Considering that your subselect is basically the same as your main select, chances are that this subselect is indeed a bit of overkill. Looking at your logic, what you want to achieve is to get the <...
- 11.1k
5
votes
Find & Delete Duplicate Records on All Tables in current MSSQL Database
Aside from Malachi's remarks about capitalization of keywords and indentation of the code, I would add two points about variable naming:
DON'T use variable names that start with ...
- 992
4
votes
Accepted
3
votes
Accepted
SQL procedure for Archiving and Deleting
Nitpicks
You use separate statements for DECLARE and SET quite a bit. While there is nothing wrong with that, it's often just ...
- 20.3k
3
votes
Accepted
Stored procedure to insert a new person
Per billinkc in the comments, rollback before throw would do what you intend.
Per Michael Green, avoid using ...
- 150
3
votes
Insert or update user data
Dead code
In this:
COMMIT TRANSACTION
select null
END TRY
This line of code: select null does nothing useful (read: ...
- 20.3k
3
votes
SQL Procedure that will calculate the Last Price paid for a certain Part
pre: this question sound more fitting for "Stack overflow", than "Code Review"
how about (so often 'underused') group by / having with ...
- 131
3
votes
SQL Procedure that will calculate the Last Price paid for a certain Part
@BCdotWEB already provided a good solution, so I will mention some other ways to improve your code.
Your formatting makes your query difficult to read. Try using some indentation and advantageously ...
- 20.3k
3
votes
T-SQL Hmac (Rfc2104 SHA2_256) Implementation
Looks OK to me, without knowing Transact SQL anyway. Some things to consider:
obviously this implements just HMAC-SHA256, so the function should be named that way;
the key parameters is 8000 ...
- 4,598
3
votes
Searching school data
It's odd that the TaUsers result column is NVARCHAR(100) rather than a nullable number.
The setting of the local variables (<...
- 143k
3
votes
Accepted
Stored procedure to describe the reason that a particular employee is unsuitable for a particular task
A handful of critiques:
As mentioned in the comments, this is undefined behavior: SELECT @val = @val + ColumnName FROM AnyTable. As such, you should avoid it. If ...
- 7,724
3
votes
Accepted
3
votes
Fetch data from database either with a stored procedure or a query
My C# is quite rusty, so take everything I'm saying with a grain of salt; I stand by the general principles of what I'm writing, but the syntax/style might be a little off.
The first problem is that ...
- 7,724
3
votes
Accepted
MySQL procedure to extract a letter from a phrase
There's another interesting way of doing it with RIGHT and LEFT operators:
...
- 166
2
votes
Return most recent non null field values for multiple unknown fields
Since you are using a stored procedure, it is not much of a stretch to extend that to be a trigger.
Triggers are the natural tool to use for this type of problem, and are designed to have two '...
- 97.1k
2
votes
Accepted
Create variable and use in one stored procedure
You can make it shorter by having a single query, thus avoiding the extra variable @SiteIDOriginal and possibly make it a little faster:
...
- 1,786
2
votes
Accepted
Updating user log in time while he logs in to the web page
The first point I notice looking at your code is that it looks as though you are storing passwords in plain text. You should not do this. It is highly irresponsible. They should be salted and hashed ...
- 794
2
votes
T-SQL Pbdfk2 (Rfc2898 SHA2_256) Implementation
I took a very, very wild swing at this. I also couldn't get it down to something entirely set-based, but I was able to get it to an inline TVF with a recursive CTE (two of them, unfortunately). With ...
- 7,724
2
votes
Accepted
Stored procedure to classify policies by sales date
I will try to cover several points:
1) Naming
Try to use homogeneous naming. Pascal/Camel case seem too be used most often, so stick to it. E.g. @Policy_ID should ...
- 1,786
2
votes
Optimize store proc performance being called in c# on array loop
I think this is a very strange way to validate anything and it's extremely unmaintainable.
Why don't you (the BI team) just return an error code (and maybe additional information in other columns). ...
- 44.1k
2
votes
Stored Procedure enhancement for updating columns in a subset
Anonymized code
Object/variable names should tell us something about what they actually mean. For instance, these variables:
...
- 20.3k
2
votes
SQL Trigger - Audting Config Table - Send Email with update details
If you're using SELECT TOP 1 1 in an effort to improve performance, know that this isn't always what will happen. In particular, the ...
- 7,724
2
votes
Accepted
Working out statistics for unique users
I don't think you need a cursor, looks like a simple update:
...
- 1,172
2
votes
Locking in MySQL stored procedure for deduplication
Will this work as intended?
Not really.
You've indented the code as if you expect that the statements betweeb DO GET_LOCK... and ...
- 121
Only top scored, non community-wiki answers of a minimum length are eligible