In this tip, we will see, how to use custom formula for setting condition of any trigger on SuiteFlow actions. We can set custom formula using Javascript/Suitescript(Client Trigger) and using SQL functions(Server Trigger). Here, I am explaining with simple examples of SuiteFlow action. Suppose, there is a requirement such as- when an Item record is created, […]

While creating query we may need a column which will hold the sequential number like 1, 2, 3… Also we may need to show that sequential number according to a particular partition. In that situation we can use the ‘ROW_NUMBER’ keyword. Below syntax can be followed :-   To get sequential number irrespective of any partition.  […]

It is quite a common request to create a Transact-SQL query to compare a parent and a child table and find out if there are any parent records that don’t have a corresponding record in the child table. Generally, this can be done with one of the three ways: So which one of the below is a better practice.Lets take a look. # […]

Customized deletion of  Users  from core database tables of a DNN application . Core tables which contain dnn application user informations are :: aspnet_Membership,  aspnet_Users ,UserProfile ,UserPortals ,UserRoles ,Users .Note:: This is needed in many cases such as database cleanup of all test users created .As it’s quite hectic to delete each users one by […]

As I have mentioned in one of my previous tips that using ROW_NUMBER() we can get the serial number which is here. Using the same ROW_NUMBER() function you can also easily delete the Duplicate Records present inside a table. Lets say I have a Users Table which is having duplicate records. I need to delete […]

If you are required to select TOP N records from a table being ordered by a column,  you can use the following query:         SELECT TOP N [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME]        But there is one problem with the above statement is that if the table has multiple records having the same value  as that of the selected list […]

We use JOIN in the SELECT statement often to retrieve data from multiple related tables. But, some times we can get into a situation where we may need to use UPDATEstatement by joining multiple tables. Consider a situation: A table named as USER_DATA is there which contains user details. This table has one column(along with other columns) named as  IS_ACTIVE […]

With SQL Server 2005’s TRY – CATCH block error handling is now  a cheese bite. Lets have an example CREATE PROCEDURE spGetAccountDetails @bookName varchar(100), @ISBN bigint AS BEGIN END GO BEGIN TRY –Starts the Try Block.. BEGIN TRANSACTION — transaction..starts UPDATE BookShelf  SET BookName= @bookName WHERE ISBN= @ISBN UPDATE Student SET Withdrawed_Book= @bookName WHERE StudentID= @StudentID COMMIT TRAN — Success! END […]

This tip demonstrates how to use special characters like _, %, [  to perform a wildcard search on string values stored in database (SQL Server in this instance) Let us take the example of a table named “TIPS“, which contains a TITLEcolumn (that holds the title of all Tips ). Now, If you want to search a […]

Last day i came across a situation, where i needed to write a SELECTstatement which will use join with different tables but according to some conditions.  Lets take the below situation:   We have three tables as below : Table : MEMBERS; Columns : MEMBERID, MEMBERNAME, MEMBERPHONE Table : COMPANY; Columns : COMPANYID, FKMEMBERID, WORKPHONE Table : […]

1st Tip:   In SQL Server 2000, VARCHAR data type can’t hold long strings. The maximum length it supports is 8000 characters, So if you want to store data more than that, then you can’t use VARCHAR. If you are using SQL Server 2005, then you have an alternative to useVARCHAR(MAX), but in SQL Server 2000 it […]

As most of us know we  have two ways of assigning values to local variables –  SET and SELECT statements. DECLARE @Var1  INT, @Var2 INT SET  @Var1  = 1 SELECT @Var2 = 2   However, there are some differences between the two statements.   1. SET is the ANSI standard which can assign only one variable at a time. If […]

To populate the databases in SQL Express 2005 we use SQL Server Management object(SMO). This process retrieves all databases created by user along with the system databases. For getting the list of databases created by users only we have to use IsSystemObject property of databases. If the property is False then these are the databases […]

If we are using SELECT TOP N query, then it returns exactly N records, and drops any record arbitrarily that has the same value as the last record in the result set. Suppose we want a report showing top 50 costly items. There may be a situation in which the price of 50th item is […]

Use of CASE Expressions in SQL query is sometimes extremely useful.For example Using CASE in SELECT statement provides developer the power to manipulates the data at presentaion layer without changing data at backend.So there are various use of CASE Expressions and it can be used in, including in statements like [SELECT, UPDATE, DELETE, SET ] and […]

This tip demonstrates how to sort data according to the values of two columns. In the following example we have a table called “EVENTS” which has 4 fieldsSrNo, EventName, StartDate and EndDate SrNo EventName StartDate EndDate 1 Event1 14/03/2010 18/03/2010 2 Event2 20/03/2010 null 3 Event3 02/03/2010 21/03/2010 The requirement is that the events should […]

At times you may need to UPDATE a row from a Table and at the same time you would also want to get the column value from the same SQL UPDATE query. Following is a solution using SQL OUTPUT clause . SQL OUTPUT clause :     The OUTPUT Clause from SQL server provides a very […]

Sometimes you need to insert data from one table to another and there are different solutions for this. Following is the one of them. Data insertion from one table to another can be done either of the following ways : CASE-I ( When both source and destination table are exist ) Example : INSERT INTO Destination(FullName,ContactNum) […]

Some Useful SQL Queries Here are some SQL queries that I like to use. TO COUNT NUMBER OF TABLES IN A DB SELECT [TABLECOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE=’U’ TO COUNT NUMBER OF STORED PROCEDURES IN A DB SELECT [SPCOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE=’P’ GET LIST OF TABLES NAMES AND […]

While registering a new user to our application we generally check if the userId already exist by doing a select query. If the userId exists we should not allow to create another user with same ID. Below is a handy script which explains how we can avoid doing the select query and register a new […]

There is an issue of reading the xml result set returned from SQL with ExecuteScalar method. The xml content gets truncated after some characters. For large XML string , SQL returns the result in multiple rows of XML each of 2033 characters , so using ExecuteScalar to read XML result string returns the first 2033 […]