Category: Database
All about databases
Store historical records in a history table in SQL Server
You can maintain historical changes made in a table by creating another table with the same schema which holds historical data. SomeTable is the base table containing current data rows SomeTable-History has exact same schema with 2 additional columns trans_type varchar(10) date_time datetime and this table will hold historical data Below triggers created on SomeTable […]
SQL Server Handle Transaction and Try Catch
SQL Server handles exceptions just like any programming language. Below is a stub to help understand SQL exception handling and you can use in your Procedures as a starting point. BEGIN TRAN BEGIN TRY EXEC P1 EXEC P2 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN DECLARE @ErMessage NVARCHAR(2048), @ErSeverity INT, @ErState INT SELECT @ErMessage […]
SQL SERVER – Remove the last character in a string
Sometimes it is required to remove last character usually a comma from SQL string, below are 3 way to achieve it. Option 1: Using LEFT function DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,5,' SELECT @String As [String] ,LEFT(@String,LEN(@String)-1) As [Last Character removed from string] GO Option 2: Using STUFF function DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,5,' […]
DataTable to HTML Table C# code
C# DataTable to a HTML Table Below is C# function to generate HTML table string from DataTable with custom options like Custom headers and Selected fields. “Format” in parameters will contain comma separated values like column1=column 1 name, column2=column 2 name and so on. public static string DataTableToHtmlTable(DataTable TableData, string Format) { try { Dictionary<string, […]
Datatable to CSV file
C# DataTable to a CSV file C# DataTable to a CSV file with default options i.e. all columns with default column names public static string DataTableToCsvFile(DataTable TableData, string FileName) { try { // change this with any delimiter you want string Delimiter = ","; int i = 0; StreamWriter sw = null; sw = new […]
SQL Server CONVERT() Function
Definition and Usage The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats. Syntax CONVERT(data_type(length),expression,style) Value Description data_type(length) Specifies the target data type (with an optional length) expression Specifies the value to be converted style […]
SQL Snippets
Rounding off to 2 decimal places cast(round([value],2) as decimal(18,2)) Compare only Year from datetime field select * from [table_name] where year(date_time_field) = year(getdate()) check if a SQL server string is null or empty ISNULL(NULLIF(text, ''), '') AS Offer_Text The differences between LEN and DATALENGTH in SQL Server LEN Returns the number of characters, rather than […]
Accessing MySQL databases from remote computer
To expose MySQL other than localhost uncomment following line in /etc/mysql/my.cnf and assign to your computers IP address and not loopback bind-address = xxx.xxx.xxx.xxx # replace xxx with IP Next for a remote user to connect with the correct privileges you need to have that user created in both the localhost and '%' as in. […]
SQL Joins
Comprehensive image to show how SQL joins work
- 1
- 2