Useful Commands in SQL

Question. Can an INSERT query return a value?

Answer: Yes

SQL Query

insert into DS_UserTypes (UserTypeName,[Description])
output inserted.UserTypeid,inserted.UserTypeName values
('TeamLead','Nothing')

SQL

Copy

C# Code

string Query = "insert into DS_UserTypes (UserTypeName,[Description]) " + "output inserted.UserTypeid,inserted.UserTypeName values " + "('TeamLead','Nothing')";
con.Open();
SqlCommand cmd = NEW SqlCommand(Query, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) { Console.WriteLine("User type Id :" + rdr [ 0 ].ToString());
Console.WriteLine("User type name :" + rdr [ 1 ].ToString());
}

SQL

Copy

Question. How to determine the Last Row identity?

Answer

select distinct @@IDENTITY 'Last Row Id' from DS_UserTypes
select * from DS_UserTypes

SQL

Copy

Output

Question. How to get the latest rows against an attribute in a table?

Answer

with budget as (select RANK()over(partition by uniquematterid,assignmentid
order by BudgetApprovedDate desc) as [Rank],
* from AS_Budget )

select [RANK],Budget_ID 'Id',MatterId 'TaskId',LawfirmID 'VendorId'
,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'
from budget where MatterId=2

SQL

Copy

Query output

From this, we can see that the latest approved work id has the rank 1.

Now we can re-write our query as.

select [RANK],Budget_ID 'WorkId',MatterId 'TaskId',LawfirmID 'VendorId'
,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'
from budget where [Rank]=1

SQL

Copy

Then it will return only the latest row of data against each attribute in the partition by clause.

Question. Update the table with a Join query.

Answer

Before updating

select * from DS_User

SQL

Copy

After updating

update a set a.active='False' from DS_User a inner join DS_UserTypes b on a.UserType=b.UserTypeid
where b.UserTypeid=5

select * from DS_User

SQL

Copy

Question. How to determine whether a table exists in the database?

Answer

Sp_tables ‘%table_name%’

SQL

Copy

For example

Sp_tables '%ds_%'

SQL

Copy

Question. How to determine a procedure using a Query?

Answer

sp_helptext ‘Procedure_name’

SQL

Copy

For example

Sp_ helptext 'GetImageID'

SQL

Copy

Question. How do we determine whether a specific table is used in any stored procedures?

Answer

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN

sysobjects so on sc.id=so.id WHERE sc.text LIKE '%table_name%'

For example

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN
sysobjects so on sc.id=so.id WHERE sc.text LIKE '%users%'

SQL

Copy

Summary

In this article, we learned about some valuable commands in SQL.