Home / Database / SQL Server / SQL Server Tricks and Help
sql

SQL Server Tricks and Help

If Exists command in SQL Server

Description:
“If Exists” accepts the select statement or any string/number & returns true or false.Sometimes we come across a condition where if some value exists in table we want to perform certain activity. That time we can use “If Exists” clause.

Example:
Declare @word12 int;
IF EXISTS (SELECT itemId FROM  ItemTable WHERE itemNum=’181005′)
begin
set @word12 = 1
end
else
begin
set @word12 = 0
end
print @word12

Above query checks for ItemNum in Table ItemTable & selects ItemId. If ItemId Exists then @word12 will be set as 1 else as 0.

Searching Table/Column in database

Searching Column in database
select * from INFORMATION_SCHEMA.columns where column_name = ‘custom’

Searching Tables in Databases
select * from INFORMATION_SCHEMA.Tables where table_name like ‘%backup%’
The above query search for the column/table in the complete Sql Server instance for the searched column/table & returns the column/table information.

Guidelines to Create/Alter Store Procedures

1.  Create Store Procedure & name it based on date of creation. Prefix store procedures names with number to maintain the order of execution if Store Procedures (sp).
E.g. 1.CreateStoreProcedure_Create_MMDDYY
1 represents the execution order of Store Procedures (sp).
“CreateStoreProcedure” is the name of Store Procedures (sp).
“Create” indicates the type of Store Procedures (sp).
MMDDYY is the date format.
Note: for altering Store Procedures (sp) same guidelines can be followed by replacing create with alter.

2.  On creation of Store Procedures (sp) add commenting to it.
Comments should be added to get the information about the Store Procedures (Sp) creation data, modified date & author information.
E.g. At the start of sp
Author: Author Name
Create date: 28 July 2014
Updated date:
Description: Purpose of SP

3.  Script should contain go keyword at the end of sp.

4.  Try to add description in design of table against the column if column is having different name than its associated column.
E.g. in third party softwares like ERP, Excel files column name is “Csutom#” but in table we write “CustomNumber”. To show mapping betwwen those 2 columns just add the description against the column to make it more readable & understandable.

5.  Table column should not contain Special characters or numbers or spaces (underscore (_) can be used).

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Database

Database level Queries & Identity functions

Get list of tables in the Database: We can get all the tables in the ...