data integrity, and error avoidance. This tip explores how to handle NULL values in SQL Server using the COALESCE() function using various queries and reviewing the results.
developers can use the SQL function ListMonthNames() that will return a list of month names along with month numbers. Then the sql script can be build by using a dynamic pivot table query like shown in following section.
Each of the prior examples shows EXISTS as part of an IF statement. It is also common to use them in WHILE loops. This method is an easy way to perform one task per row in a query without using a cursor. While this code is easy to follow and understand, it should not replace set-...
In the above example, we have created a SQL Function to calculate the age of the employee from the DOB so the function takes @EmpDOBas a parameter and returns NVARCHAR(50). We will see this in action when we run this function. In step, we have created this function. Then we have d...
order_by(SQL('cost').desc())) # To iterate over the results, it might be easiest to use namedtuples: for row in query.namedtuples(): print(row.member, row.facility, row.cost) Produce a list of all members, along with their recommender, using no joins. How can you output a ...
We’ll use PostgreSQL syntax in our queries. However, the concepts easily translate to other flavors of SQL. PRIMARY KEY Constraint The university aims to identify each student uniquely. Utilizing first and last names for this purpose isn't advisable due to the possibility of name duplication ...
Using SQL DELETE TOP statements We can use the TOP statement in the delete queries. The following query deletes a random row from the ProductListColorstable. 1 2 DELETETOP(1) FROMProduction.ProductListColors; If we want to delete the first row of the table according to a certain order, ...
The @xmlDocument can also be of xml type or of (n)varchar(max) type. If <CustomerID> and <ContactName> in the XML document are subelements, the element-centric mapping retrieves the values. SQL Copy DECLARE @XmlDocumentHandle int; DECLARE @XmlDocument nvarchar(1000); SET @XmlDocument ...
Here’s an example of a SQL query to show the default value of n when used in a variable declaration: DECLARE @myVariable AS VARCHAR = ‘abc’; DECLARE @myNextVariable AS CHAR = ‘abc’; –The following returns 1 SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable); GO The char...
declare @sql varchar(max),@i int set @i =3 SET @sql ='select LocationID,LocationName from locations where LocationID = ' + cast(@i as varchar(10)) EXEC (@SQL) Executing queries on a remote server AT linked_server_name clause along with EXEC command is used to execute queries on ...