SQL Server 2012 Data Type

Below is the list of SQL -Server 2012 Data Type

Thx,
RS

Change select * with Select columns

This is my first video article in this I am showing how to replace select * from will all the columns 

Thx,

RS

SQL formatting standards

In my job we are following some formatting standards for SQL. I have searched over the internet and found that every company is having their own SQL formatting standards. But these are the very common standards which every company is using. Below are the some examples to cover all the query.

Example of SELECT statement:

SELECT

    ST.ColumnName1,

    JT.ColumnName2,

    SJT.ColumnName3

FROM

    FirstTable FT

INNER JOIN

    SecondTable ST

ON

    FT.Id = ST.Id

    AND

    FT.ColumnName = ST.ColumnName

INNER JOIN

    ThirdTable TT

ON

    ST.SourceTableID = TT.SourceTableID

    AND

    ST.Column3 = TT.Column4

WHERE

    ST.SourceTableID = X

    AND

    TT.Column3 = Y

Example of UPDATE statement:

UPDATE

    TestTable

SET

    ColumnName1 = @value,

    ColumnName2 = @value2

WHERE

    Condition1 = @test

Example of INSERT statement:

INSERT INTO TestTable

(

    ColumnName1,

    ColumnName2,

    ColumnName3

)

VALUES

(

    @value1,

    @value2,

    @value3

)

Example of IF Block statement:

IF (Condition)

BEGIN

      --Logic

END

Thx,

RS

How to return a value from the SQL Stored procedure

Que: How to return a value from the SQL Stored procedure.

Ans: With the out parameter we can return the value from the SQL Server.

Below is example stored procedure created in SQL Server 2005 AdventureWorks sample database.

CREATE PROCEDURE GetImmediateManager

   @employeeID INT,

   @managerID INT OUTPUT

AS

BEGIN

   SELECT @managerID = ManagerID

   FROM HumanResources.Employee

   WHERE EmployeeID = @employeeID

END

Value of Out parameter @managerid we can get in C#.

Now If I want to return the value in SQL from the stored procedure then we need to follow the below approach.

Below are the example stored procedure.

CREATE PROCEDURE TestSP1

AS

BEGIN

      SELECT 1

END

GO

CREATE PROCEDURE TestSP2

AS

BEGIN

      DECLARE @Temp INT

      EXEC @Temp = TestSP1

END

GO

EXEC TestSP2

Thanks,
RS

Physical Path of Database in SQL

Hi All,

Below are the two queries which help us to find the full path of the data folder in SQL.

This query Return Full physical path till Data Folder in SQL

                                                         

SELECT SUBSTRING(FILENAME, 1, CHARINDEX(N'<database_name>', LOWER(FILENAME)) - 1)

FROM master.dbo.sysdatabases WHERE name = '<database_name>'

This query Return Full physical path till Data Folder in SQL for all the database present in sql

SELECT

   mf.name

  ,mf.physical_name

  ,reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))

 FROM sys.master_files mf 

Thanks,

RS

How to download and save Facebook look back video

Hi All,

Facebook has presented the feature of creating a look back video.

If you want to download this video then you have to follow the below steps.

1. You should have Google Chrome install in your computer.
2. Open your facebook look back video or login to facebook and click on https://www.facebook.com/lookback/ in google chrome and press Ctlr+Shift+J key.
3. Now Copy the below code and paste in the window which is coming after step 2 then Hit Enter.

var xmlhttp;

if (window.XMLHttpRequest) {

xmlhttp = new XMLHttpRequest();

} else {

xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");

}

xmlhttp.onreadystatechange = function() {

if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {

ss = xmlhttp.responseText.split('[["params","')[1].split('"],["width","960"]')[0];

var x = ss;

var r = /\\u([\d\w]{4})/gi;

x = x.replace(r, function (match, grp) {return String.fromCharCode(parseInt(grp, 16)); });

x = unescape(x);

console.log(JSON.parse(x).video_data[0].hd_src);

prompt("Here's your video URL (HD)! Press Ctrl + C to copy it!", JSON.parse(x).video_data[0].hd_src)

}

}

xmlhttp.open("GET", "/lookback", true);

xmlhttp.send();

4. Now one popup will come with the url. Copy that url and paste it into new tab.

5. Now Hit Ctrl + S and save the video.

Thanks,

RS

Add error messages in SQL-Server

Hi All,

Below is the example how we can add our own error messages in SQL-Server.

Que: I want to add my own error message in sql server what can I do for this.

Ans: We can defined our own error using sp_addmessage. Below is the syntax.

exec sp_addmessage 50001,16, 'Hello Rahul Singi'

Now when we will raise the error 50001 we will get the message “Hello Rahul Singi”

--- output ---

Msg 50001, Level 16, State 1, Line 2

Hello Rahul Singi

Thanks,
RS