SQL Server PRINT Keyword

SQL Server PRINT Keyword

To display something in plain text as a result of a statement, type PRINT followed by what to display. Therefore, PRINT uses the following formula:

PRINT 'WhatToPrint'

The item to display can also be an operation or the result of an operation. If you want to display a character, a word, or a sentence, include it between single-quotes. If you want to include a single-quote in your statement, double it;

How to convert varchar data type column to int in SQL Server while querying?

How to convert varchar data type column to int in SQL Server while querying?

There are two ways to convert varchar data type column to int while writing query statements.

1. CAST
2. CONVERT

Examples :

CONVERT(int,discountfee)

CAST(discountfee as int)

Here discountfee is column name and int declared as data type for column varchar data type discountfee.

SQL Server NULLIF function

SQL Server NULLIF function

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows:

NULLIF ("expression 1", "expressions 2")

It is the same as the following CASE statement:

SELECT CASE ("column_name")

  WHEN "expression 1 = expression 2 " THEN "NULL"

  [ELSE "expression 1"]

  END

FROM "table_name";

For example, let's say we have a table that tracks actual sales and sales goal as below:

Table Sales_Data

Store_Name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:

SELECT Store_Name, NULLIF (Actual, Goal) FROM Sales_Data;

Result:

Store_Name      NULLIF (Actual, Goal)

Store A                                 NULL

Store B                                     40

Store C                                     25

SQL Server USE Keyword

SQL Server USE Keyword

The USE keyword is used to select a database in SQL Server. The syntax is as follows:

USE "database_name";

For example, if you want to connect to a database called "World", you can type in the following:

USE World;

SQL Server: TOP WITH TIES

SQL Server: TOP WITH TIES

TOP clause is commonly used to get top required rows from a result set. Beauty of this clause is that it can be used with WITH TIES clause, to retrieve all similar rows to base result set.

Syntax :

[

      TOP (expression) [PERCENT]

      [ WITH TIES ]

]

Arguments:

expression: is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT: indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES: specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example:

DECLARE @TEST TABLE(

ID int, Amount int)

INSERT INTO @TEST VALUES

(1,10),(2,30),(3,60),

(4,50),(5,50),(6,70),

(7,20),(8,70),(9,70),

(10,44),(11,80),(12,90)

Below is the result-set using TOP without and with clause WITH TIES.