Skip to main content
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 ...
dfhwze's user avatar
  • 13.9k
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 ...
Dan Oberlam's user avatar
  • 7,814
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 ...
Maarten Bodewes's user avatar
3 votes
Accepted

SQL Server Stored Procedure quality

Let's just take ...
Peter Taylor's user avatar
  • 24.1k
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 ...
Dan Oberlam's user avatar
  • 7,814
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 ...
Kris Lawton's user avatar
3 votes
Accepted

MySQL JSON diff procedure

You are missing a second part of the diff. I've added the missing part, renamed the variables a bit to make it easier to work with, and added some comments. ...
daniel.gindi's user avatar
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: ...
lemon's user avatar
  • 166
3 votes

A procedure that receives a letter and a number that shows for example : (A,3) A AA AAA

Your procedure works, but it can be improved for clarity, efficiency, and better coding practices. Here are some suggestions: Use meaningful variable names. Avoid unnecessary SELECT statements. Handle ...
Picture with words's user avatar
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: ...
Phrancis's user avatar
  • 20.4k
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 ...
Dan Oberlam's user avatar
  • 7,814
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). ...
t3chb0t's user avatar
  • 44.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 ...
Dan Oberlam's user avatar
  • 7,814
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 ...
Michael - sqlbot's user avatar
2 votes
Accepted

Working out statistics for unique users

I don't think you need a cursor, looks like a simple update: ...
dnoeth's user avatar
  • 1,172
2 votes
Accepted

Stored procedure for processed orders in SQL Server

Table variables Because you are using them only once, and as part of a bigger query, there is really no benefits in using a table variable nor a temporary table. The best choice in this case is to ...
this's user avatar
  • 2,029
2 votes

Filtering query by date built from integers

Here is the revised SQL I came up with Below this code is a list of tips for writing better SQL. ...
aduguid's user avatar
  • 490
2 votes
Accepted

Calculate NPS in a stored procedure

I ended up getting some excellent help in chat (though it seems I couldn't talk anyone into posting an answer), and this is what I came up with: ...
FreeMan's user avatar
  • 1,290
2 votes

SQL SP creating certificates for shipping

The following are a few suggestions on how I'd write the stored procedure. Source Control If you don't already have a database project, create one in Visual Studio. Then check it in to source ...
aduguid's user avatar
  • 490
2 votes

Stored procedure with output parameters in MSSQL

You're looking for a JOIN. SELECT i.id, l.pid, l.sid FROM invoice i JOIN log l ON i.pid = l.pid WHERE i.batchid = @batchid; Use EXPLAIN SELECT ... to verify the ...
J_H's user avatar
  • 29k
2 votes

Stored procedure with output parameters in MSSQL

If you want to optimize the query and use out parameters to return IDs please refer to below code: ...
hassaan mustafa's user avatar
1 vote

SQL Stored Procedure to export specified data

You could always have 31 days by day number as column headers in the pivot. That way it would always be a consistent header and you wouldn't need to use dynamic SQL. Side note: Go K-State! Results ...
aduguid's user avatar
  • 490
1 vote

Stored procedure to implement a search filter

A user could cause a dos attack by passing in complex like filters, you might want to sanitize the @name parameter. https://www.owasp.org/index.php/Testing_for_SQL_Wildcard_Attacks_(OWASP-DS-001)
C.M.'s user avatar
  • 117
1 vote
Accepted

Stored procedure for sending emails at specified times after license expiration

You can use below reference for your requirement. I did for positive date difference only. you can write for negative values as well in CASE WHEN. ...
JERRY's user avatar
  • 130
1 vote

Inner Join only if satisfies If condition

I did something like this ...
J M's user avatar
  • 119
1 vote

Univariate analysis with SQL stored procedure

My comments is limited because I know next to nothing about statistics and I admit I don't understand what most of the calculations are supposed to do or how to interpret the results. That said, you ...
this's user avatar
  • 2,029
1 vote

Stored procedure for processed orders in SQL Server

Further simplifying @this answer. Assuming there's a 1:1 relation between order ids the CountOfPaidOrders cte doesn't need the Left Join as you don't access any ...
dnoeth's user avatar
  • 1,172
1 vote

SQL Server Stored Procedure quality

That code is very hard to read. No indent, inconsistent capitalization, and inconsistent use of [ ]. It is bad. ...
paparazzo's user avatar
  • 6,016
1 vote
Accepted

Creating a join table entry by looking up name

I'm not a postgres expert by any means, so take this as just an opinion: ...
404's user avatar
  • 2,306
1 vote

Stored procedure for product search with dynamic filters

Dynamic Sorting You could reduce half of the CASE statements by removing the explict ASC/DESC sorting. For example, the ...
JBond's user avatar
  • 295

Only top scored, non community-wiki answers of a minimum length are eligible