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')  

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());
}

Question. How to determine the Last Row identity?

Answer

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

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  

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  

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  

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  

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

Answer

Sp_tables ‘%table_name%’  

For example

Sp_tables '%ds_%'  

Question. How to determine a procedure using a Query?

Answer

sp_helptext ‘Procedure_name’  

For example

Sp_ helptext 'GetImageID'  

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%'  

Summary

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