ProgrammingOcean

After changed the name or add new column in the table, that changes would not reflect in the view if that field used in that view. For that you just run this system stored procedure with view name as parameter rather open the view and update it. Sp_refreshview yourviewname For an Example: I am using Table_A, Table_B and View_C In the View C I have used Table A and Table B. After created the View C I added one more column call Status in the Table A and run the View C, you would not see that newly added column as view have not been refreshed yet as shown below. For this you can simply update the view just using the above stored procedure as shown below, Sp_refreshview View_C After run the script you can able to see that added column in the view as shown below,

In Oracle you can retreive the field names as shown below, DESC Table_Name

For this there is a function call CHARINDEX(). This is very similar to InStr function of VB.NET and IndexOf in Java. This function returns the position of the first occurrence of the first argument in the record. SELECT CHARINDEX('r','server') This would return 3 as it is start from 1.

Cursors are useful thing in SQL as it is enable you to work with a subset of data on a row-by-row basis. All cursor functions are non-deterministic because the results might not always be consistent. A user might delete a row while you are working with your cursor. Here after a few functions that work with cursors. When you work with Cursor , you will Have to follow these steps . 1. Declare Cursor 2. Open Cursor 3. Run through the cursor 4. Close Cursor 5. Deallocate the Cursor 1. Declare cursor Declare Emp_Cur Cursor For Select Emp_Code From Employee_Details Declare the cursor with select query for a Table/View as shown above. 2. Open Cursor Open Emp_Cur Fetch Next From Emp_Cur Into @mCardNo Open the Declared cursor and Fetch them into declared local variables for row-by-row basis. In given example, open cursor Emp_Cur and Fetch the Emp_Code records and assigned into a local Variable called @mCardNo. 3. Run through the Cursor For this there is a Cursor function called @@FETCH_STATUs w

Stored procedures are stored in SQL Server databases. The simplest implication of stored procedures is to save complicated queries to the database and call them by name, so that users won’t have to enter the SQL statements more once. As you see, stored procedures have many more applications, and you can even use them to build business rules into the database. How to create a Stored Procedure, As shown given below, created a Stored Procedure for Inserting records into Table call Holiday_Details, which has Code and Description fields. In this Stored Procedure, passing two parametrs as INPUT Parameters and one OUTPUT parameter. Normally in Stored Procedure we can pass parameters as Input / Output Stored parameters. When you define output parameters, we have to implicitly specify the OUTPUT Keyword. Here I have shown the simple stored procedure. CREATE PROCEDURE [dbo].[SP_Holiday] @Code char(3), @Desc varchar(100), @flag bit, @Err Varchar(MAX)=Null OUTPUT AS Begin Transaction if @flag=0

The SQL Server provides very useful feature which is Begin, Commit, Rollback Transaction. When we use Begin Transaction before we use DML Queries, we can Commit or Rollback that Transaction after the confirmation. This is very useful if you update anything wrongly then you can rollback that transaction. For example,As shown below, I am trying to update the NodeID column data to 5 from 1. begin transaction update DownLoad_Data set NodeID=5 But after I updated, You can check whether you have been updated properly. But here, I realised I did not mention the Where clause. select * from DownLoad_Data So I have to rollback this transaction. For that I can use Rollback Transaction since I used Begin Transaction. Rollback transaction So again I changed the Query and run it. Begin transaction update DownLoad_Data set NodeID=5 where RecNo=1 Still you can check whether have been updated properly. If it is updated correctly then, Run the Commit Transaction to make all updates permanently. Commit