Tag: SQL Server

How to disable Foreign Key constraints using T-SQL in SQL Server ?

You can enable or disable the foreign key constraints for a table in a SQL Server using the ALTER statement. How to disable Foreign Key constraints using T-SQL in SQL Server ? Here’s the T-SQL query to disable all the constraints of the table “Employee”. If you want to enable all the constraints of the table , here’s how you do it.

Exit the Current Scope with a return value in SQL Server

You can use the RETURN statement to discontinue the execution of a T-SQL batch statement or a stored procedure and provide a status code or value on return. For example , you want to display the Employees whose MaritalStatus is Divorced. You want to return a value -1 to indicate that no records exist and also You do not want the SQL Statements following it…

Exit the Current Scope without returning a value in SQL Server

You can use the RETURN statement to discontinue the execution of a the T-SQL batch statement. For example , you want to display the Employees whose MaritalStatus is Divorced. You donot want the SQL Statements following it to be executed if this condition doesn’t match. You can use the IF NOT EXISTS and use the RETURN statement as shown in this example. The second statement…

CASE expression in a Query in SQL Server

You can use the CASE expression in a T-SQL query in SQL Server to get the same behavior of the switch statement in the programming languages. Below is a sample Query demonstrating the implementation of the CASE expression in a query in SQL Server. CASE expression in a Query in SQL Server The query associates a Department group name to a country USA and UK…

How to Get a value to a Variable in SQL Server ?

When you use the declare a variable using the DECLARE statement. The same variable can be used to store a value retrieved from the database and later can be used in your SQL Query. How to Get a value to a Variable in SQL Server ? For example , you want to retreive the value of the column JobTitle and store it in the variable…

How to ALTER a Column from Null to NOT Null in SQL Server ?

Assume a scenario where you have a table that contains columns which are Nullable integer column. You might want to update them to NOT NULL column (adding NOT NULL constraint) and set its value to zero. How to ALTER a Column from Null to NOT Null in SQL Server ? This would be a two step process where you should first update the column values…

How to Declare a variable in SQL Server ?

You will most likely declare a variable in the SQL Server and use it in the SQL Statements. You can use the DECLARE statement and specify the variable and datatype and optionally provide a default value. For example , You can declare an NewEmployeeJobTitle variable using the DECLARE statement and provide a default value as ‘Chief Executive Officer’ DECLARE @NewEmployeeJobTitle nvarchar(50) = ‘Chief Executive Officer’;…

How to get the date part from the date time in SQL Server ?

If you are on SQL Server 2008 and higher version and want to get the date part of the datetime data in SQL Server , you can use the CONVERT method as shown in this post. How to get the date part from the date time in SQL Server ? If you want to get the current date leaving out the time from the datetime…

Using FETCH and OFFSET to get N records in SQL Server

In SQL Server , you can use the OFFSET and FETCH and apply paging and retreive N records at a time. OFFSET AND FETCH clause are part of the ORDER BY clause and hence you must include the ORDER BY clause. For example , you might want to skip the first 4 records and retrieve 5 records from the query , you could use the…

How to check if a column exists in a table in SQL Server ?

If you want to find out if a column exists in a SQL Server table , you can use the sys.columns table and specify the name of the column and table as parameter. How to check if a column exists in a table in SQL Server ? For example , you want to find out if the GroupName column exists in the table “HumanResources.Department” table…

Liked this content?. Please spread the word :)