sql-function

MySQL procedure vs function, which would I use when?

by

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

Calling Scalar-valued Functions in SQL

by

Are you sure it’s not a Table-Valued Function? The reason I ask: CREATE FUNCTION dbo.chk_mgr(@mgr VARCHAR(50)) RETURNS @mgr_table TABLE (mgr_name VARCHAR(50)) AS BEGIN INSERT @mgr_table (mgr_name) VALUES (‘pointy haired boss’) RETURN END GO SELECT dbo.chk_mgr(‘asdf’) GO Result: Msg 4121, Level 16, State 1, Line 1 Cannot find either column “dbo” or the user-defined function or … Read more

SQL function return-type: TABLE vs SETOF records

by

When returning SETOF record the output columns are not typed and not named. Thus this form can’t be used directly in a FROM clause as if it was a subquery or a table. That is, when issuing: SELECT * from events_by_type_2(‘social’); we get this error: ERROR: a column definition list is required for functions returning … Read more

pass parameter in table valued function using select statement

by

use outer/cross apply: select * from Employee as E cross apply dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TC if you still have to filter by TC.EmployeeId = E.EmployeeId, you can do this with subquery: select * from Employee as E cross apply ( select TT.* from dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TT where TT.EmployeeId = E.EmployeeId … Read more

How to Replace Multiple Characters in SQL?

by

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

SQL Query – Concatenating Results into One String [duplicate]

by

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