Useful Commands in SQL

Question: Can an INSERT query return a value?

Answer: Yes

SQL Query: 

  1. insert into DS_UserTypes (UserTypeName,[Description])  
  2. output inserted.UserTypeid,inserted.UserTypeName values  
  3. ('TeamLead','Nothing')  
user typeid

C# Code:
  1. string Query = "insert into DS_UserTypes (UserTypeName,[Description]) "  
  2. "output inserted.UserTypeid,inserted.UserTypeName values "  
  3. "('TeamLead','Nothing')";  
  4. con.Open();  
  5. SqlCommand cmd = new SqlCommand(Query, con);  
  6. SqlDataReader rdr = cmd.ExecuteReader();  
  7. while (rdr.Read())  
  8. {  
  9.    Console.WriteLine("User type Id :" + rdr[0].ToString());  
  10.    Console.WriteLine("User type name :" + rdr[1].ToString());  
  11.   
  12. }  
Question: How to determine the Last Row identity?

Answer:
  1. select distinct @@IDENTITY 'Last Row Id' from DS_UserTypes  
  2. select * from DS_UserTypes  
Output:

last row id

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

Answer:
  1. with budget as (select RANK()over(partition by uniquematterid,assignmentid  
  2. order by BudgetApprovedDate descas [Rank],  
  3. from AS_Budget )  
  4.   
  5. select [RANK],Budget_ID 'Id',MatterId 'TaskId',LawfirmID 'VendorId'  
  6. ,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'  
  7. from budget where MatterId=2  
Query output:

rank

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

Now we can re-write our query as:
  1. select [RANK],Budget_ID 'WorkId',MatterId 'TaskId',LawfirmID 'VendorId'  
  2. ,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'  
  3. 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:

user id
  1. select * from DS_User  
After updating:
  1. update a set a.active='False' from DS_User a inner join DS_UserTypes b on a.UserType=b.UserTypeid  
  2. where b.UserTypeid=5  
  3.   
  4. select * from DS_User  
user id after update

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

Answer:
  1. Sp_tables ‘%table_name%’  
For example:
  1. Sp_tables '%ds_%'  
table

Question: How to determine a procedure using a Query?

Answer:
  1. sp_helptext ‘Procedure_name’  
For example:
  1. Sp_ helptext 'GetImageID'  
create procedure

Question: How determine is specific table is used in any of the stored procedure?

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: 
  1. SELECT DISTINCT so.name FROM syscomments sc INNER JOIN  
  2. sysobjects so on sc.id=so.id WHERE sc.text LIKE '%users%'  
ASUsetActivityReport