sql-function - w3toppers.com

Registering a SQL function with JPA and Hibernate

You might read articles telling you to register the SQL function by extending the Hibernate Dialect, but that’s a naive solution. Since Hibernate ORM 5.2.18 and 5.3.1, the best way to register a SQL function is to supply a MetadataBuilderContributor like this: public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor { @Override public void contribute(MetadataBuilder metadataBuilder) { metadataBuilder.applySqlFunction( … Read more

Why do SQL Server Scalar-valued functions get slower?

In most cases, it’s best to avoid scalar valued functions that reference tables because (as others said) they are basically black boxes that need to be ran once for every row, and cannot be optimized by the query plan engine. Therefore, they tend to scale linearly even if the associated tables have indexes. You may … Read more

How to create Daylight Savings time Start and End function in SQL Server

As pointed out in comments, right now (March 2022) this calculation looks likely to change next year: US may not switch off of DST in the fall. Don’t forget that daylight saving time schedules change depending on country, and also are subject to change over the years: the current (as of 2013 through 2022) US … Read more

SQL date format convert? [dd.mm.yy to YYYY-MM-DD]

Since your input is a string in the form 03.09.13, I’ll assume (since today is September 3, 2013) that it’s dd.mm.yy. You can convert it to a date using STR_TO_DATE: STR_TO_DATE(myVal, ‘%d.%m.%y’) Then you can format it back to a string using DATE_FORMAT: DATE_FORMAT(STR_TO_DATE(myVal, ‘%d.%m.%y’), ‘%Y-%m-%d’) Note that the year is %y (lowercase “y”) in … Read more

Why MySQL is giving error “Not allowed to return a result set from a function”?

Because SELECT left_id AS c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0; doesn’t set the variable c1, it returns a set with a column named c1 You want SELECT left_id INTO c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0; Similarly for c2.

SQL Query – Concatenating Results into One String [duplicate]

If you’re on SQL Server 2005 or up, you can use this FOR XML PATH & STUFF trick: DECLARE @CodeNameString varchar(100) SELECT @CodeNameString = STUFF( (SELECT ‘,’ + CodeName FROM dbo.AccountCodes ORDER BY Sort FOR XML PATH(”)), 1, 1, ”) The FOR XML PATH(”) basically concatenates your strings together into one, long XML result (something … Read more

How to Replace Multiple Characters in SQL?

One useful trick in SQL is the ability use @var = function(…) to assign a value. If you have multiple records in your record set, your var is assigned multiple times with side-effects: declare @badStrings table (item varchar(50)) INSERT INTO @badStrings(item) SELECT ‘>’ UNION ALL SELECT ‘<‘ UNION ALL SELECT ‘(‘ UNION ALL SELECT ‘)’ … Read more

How to create a temporary function in PostgreSQL?

I needed to know how to do a many time use in a script I was writing. Turns out you can create a temporary function using the pg_temp schema. This is a schema that is created on demand for your connection and is where temporary tables are stored. When your connection is closed or expires … Read more

MySQL stored procedure vs function, which would I use when?

The most general difference between procedures and functions is that they are invoked differently and for different purposes: A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records. A function is invoked within an expression and returns … Read more

Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed). A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set. A point worth noting from comments, which summarize the … Read more