ProgrammingOcean

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

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.

In sql server there are 2 built-in stored procedures for drop the already existing backup device and create the new device in the user defined path. Before create the backup device, must drop the device. Because when you create a backup device, if backup device had already been created, sql server throw a error. So very first time have to create a backup device manually. Afterwards you can use this script. This is very use ful as user can take backup where user wants it since this procedure takes the path as parameter. Create Backup device manually in Sql Server 2008 Go to Server Object where right click on Backup Device, Then choose New Backup Device. if you choose that, sql server let you to create the New Backup Device. (Please refer the figures as shown below) Figure 1 Figure 2 Figure 3 Figure 4 Figure 5 Script for Drop the Backup Device EXEC sp_dropdevice 'Time_Attendance' Script for Create the Backup Device EXEC sp_addumpdevice 'disk', 'Time_Attendance', @

This very frequent needful thing for developers as they need to create so many reports based on this concept. For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This would add each and every vale with previous value and give like Running Total. Query for this, SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY, SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL FROM PRODUCT_LOCATION WHERE CLIENT_C=UPPER(‘MSWG’) AND UNRESTRICTED_QTY>0 AND LOCATION_NO=’RECEIPT_BAY‘ row in the result set and adding up the values with currently reading value which is specified by CURRENT ROW up to last record of the record set. And ordering results by PRODUCT_NO The result of the above query shown below.

Here it s the query for retrieve the table structure in sql server, SELECT Ordinal_Position,Column_Name,Data_Type,Is_Nullable,Character_Maximum_Length INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table Name'

drop table ##temp create table ##temp (id char(3) ,marks int ) create table ##temp2 (id char(3) ,marks int ) insert into ##temp(id,marks) ----Here we are inserting duplicate select '001',50 ----records for each ID union all select '001',60 union all select '002',66 union all select '002',88 union all select '003',92 union all select '003',64 union all select '004',44 union all select '005',67 ----Here we are getting the distinct records and insert then into another Tempory table insert into ##temp2 select distinct id,max(marks) from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) group by id ---And delete those duplicate records from original Table delete from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) ---And again inser the inserted reocrds from temporary Table insert into ##t

Blogger Buzz: Blogger integrates with Amazon Associates

We can use this clause to sum/count/avg/max/min so on. This clause will give you the output as detail and summary which is based on the fields you want to summarize. select * from #temp order by student compute sum(marks) by student in above compute by clause, you must specify the field you want to sum in Compute clause and specify the field in By clause based on which field you need to compute. Very important thing is you must specify the Order By clause in which specify the fileds whatever you specify in By clause in Compute clause. The output of above query is, When we try with max,min,avg, the query and output would be as shown below, Using Max() select * from #temp order by student compute max ( marks ) by student Using Min () select * from #temp order by student compute min ( marks ) by student Using Avg() select * from #temp order by student compute avg ( marks ) by student

We can use rowcoun t sql property to set the number of rows to be shown in the output. for an example, lets say there are 10 records in a table, if we set the rowcount to 5 then when retrieve records from that table, only 5 records will be shown. if you rowcount to 0 then all records will be retrieved and shown in output. SET ROWCOUNT 5 SELECT ref_num FROM tbl_po_master in above example only 5 rows have been retrieved and shown in output as we set the rowcount to 5.

In SQL Server you can retreive the field names as shown below, SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name='Table_Name')

There is way find what are the parameter list for a storedprocedure in sql server rather find them by open individually. SELECT PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='AddDefaultPropertyDefinitions' Hope this would be very useful for developers who are working with database.

Here it is the procedure to Split the words using comma seperator. still you can use different character for split instead of comma(','). Here i m using 'E,l,e,p,h,a,n,t' as word with comma characters. so the output should be 'E','l','e','p','h','a','n','t'. Declare @name as varchar(20) Declare @i as int Declare @char as char Declare @word as varchar(20) select @name='E,l,e,p,h,a,n,t' set @word='' set @i=1 while @i<=len(@name) begin set @char=substring(@name,@i,1) if @char<> ',' begin set @word=@word+@char end else if (@char=',' and @i<>len(@name)) begin print @word set @word='' end ---Print the last word if @i=len(@name) begin print @w

In SQL Server there is a built-in function called Datepart() which is takes 2 paramaters which are return date option and date value. for the 1st paramater pass the date option as 'dw' and for second parameter pass the date value as shown below, SET dateformat dmy Select DATENAME(dw,'09/03/2010') Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek if you execute this query, output will be, in SQL Server, by default the week start with 'Monday' which is 1. so in this example, the week is Tuesday. So the number of the Tuesday is 2. You can check, what is default start week number by using @@DATEFIRST. Select @@DATEFIRST Since SQL Server default start week number is 1(Monday), it is giving 1 in output. Default Value for Week in SQL Server, Monday - 1 Tuesday - 2 Wednesday - 3 Thursday

There is a built-in function call DateName() in SQL Server to get the Weekday Name. This function takes 2 parameters in which first is return date option whereas second one date value from which you want to get the weekday name. To get the weekday name you have to specify the date option as 'dw' . for example, set dateformat dmy SELECT DATENAME(dw,'09/03/2010') Weekday in this example '09' is day of march. so if you use this sql function as i given above, it will return the exact name of the weekday. in output, it is give you week day name.

There is a built-in function called datename() for find the month name from given date. for example:

In this post i thought to expalin how to get one column data in a single row with comma separator. as i shown shown below a table have one column call Choice which have 4 rows. So our task is how to bring up all these 4 rows into one single row with comma separator. We can do this in 2 methods, Using COALESCE() function Using ISNULL() Function Actually both are playing same role. 1. Using COALESCE() function As shown below in the SQL Query below, COALESCE() function takes 2 parameters. It is check whether passed field value is Null or not if it is null then place with blank whereas if the field value is not null then concatenate value with comma. 2. Using ISNULL() Function This query also working similar to COALESCE() function. here value 'A' repeating twice. so how to get distinct value from this? here it is the SQL query for that, We just write subquery to filter only distinct values and using that as a Table for Outer query. So here only gives A,B,C.

We can get the Connected Sql Server Name using the Built-in function SQL Server as shown below,

Normaly extarcting date only from DATETIME is commaon and serious problem in SQL SERVER. Here i have given some common methods to extract on the date from datetime Method 1 ------------ SELECT CAST ( FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME ) Method 2--but it is not correct ------------------------------------ This is similar to first method but we have used INT instead of FLOAT . But it will seldom correct. It will not give you the exact date of what you expecting. So better not to use INT. SELECT CAST ( FLOOR(CAST(GETDATE() AS INT)) AS DATETIME ) Method 3 ---------- i would recommend this method to extract inly date though there are many methods. because this method is very simple and easy. SELECT CAST ( STR(DAY(GETDATE()))+ '/' + STR(MONTH(GETDATE())) +'/'+ STR(YEAR(GETDATE())) AS DATETIME ) Method 4 ---------- In this we have used CONVERT function with 113 date type. SELECT CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 113)) Method 5 ---------- Here we have u

Introduction The PIVOT() function in MS SQL Server is very useful function. This is works like Cross-Tab table in Crystal Report. Explanation For an instance, as shown below table, we have customer,Yr,Mothname, and TTL columns. The Year column consisting distinct records 2009,2010 year. If you want to show the Year data as each column header name, then you will have to use the PIVOT() function to show the distinct records as column header. SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] , isnull(sum(total_value),0) TTL FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date) Using PIVOT() function In PIVOT() function, you must specify the column values as shown below, select * from (SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] , isnull(sum(total_value),0) TTL FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date) ) s PIVOT ( sum(

if we want to insert multiple rows records, we can still use insert into command for each row as shown below, but you can insert all those rows by using one insert into command rather than using more insert into command as shown below,