Top with Ties option

Today, I will explain how we can use Ties option with Top.Basically Sqlserver reaturn all the rows from the select Query but with Top Option We can specify criteria on that basis Select Query Return the Records.

–Select Query

Select SaleitemQty from Sales

This Query will return the alll the SaleitemQty from Sales table.

–Select Query with Top Clause

Select Top 10 SaleitemQty from Sales

This Query will return the top 10 SaleitemQty from the Sales table.

Now, we come to Top clause with Ties option.

Create table like this

Create Table Sales (CityName varchar(100),SaleQty int)

Insert Rows in this table

Insert into Sales values(‘Delhi’,15)

Insert into Sales values(‘Bombay’,17)

Insert into Sales values(‘London’,20)

Insert into Sales values(‘Newyork’,22)

Insert into Sales values(‘Tokyo’,15)

Now, we check first for the Only Top option.

— Query with Top clause only

select Top 4 SaleQty,cityname from sales order by SaleQty desc

— Result Set of Top clause

SaleQty     cityname

———– ———————————————————–

22          Newyork

20          London

17          Bombay

15          Tokyo

Like that you can see in the result set that, there are Two tables that have saleQty 15 but only one City comes in the result set.

— Query with Top clause and Ties Option

select Top 4 with Ties SaleQty,cityname from sales order by SaleQty desc

— Result Set of Ties option

SaleQty     cityname

———– ———————————————————–

22          Newyork

20          London

17          Bombay

15          Delhi

15          Tokyo

Ties option alllows the last place to include multiple rows if those rows have eaual values in the columns used in the order by clause.

In this example Two city has same SaleQty 15, with ties option we can get both the citys In the result set with same SaleQty.

I hope this example will clear the Ties option with Top clause.

How to get Data from One procedure to another Procedure or Dynamic Sql?

Today I will explain the very small but very useful features which I use in the past for the procedures. When I am new to sqlserver I am facing many problems this is one of this that I will explain to you today.

That you all know that, User defines Function has limitation that we can not use #temp table or Table variable in UDF. Because of that we can not get the data from another function in the UDF. At that time I find the solution that we are now create the procedure and we get data from that procedure for further manipulation. Question is how to get data from another procedure?. You can get the answer of this question in this post..

e.g., suppose procedure ReturnStudentDetail return the table with two fields Name and City so how can you get that table in another procedure for further manipulation.

–Code

— Create Table

Create table #StudentDetail

(Name varchar (50), City varchar (100))

— Below code give you data in the procedure

Insert into #StudentDetail

Exec ReturnStudentDetail

This syntax works only if you create table like this. You also use Dynamic Sql output for inserting data in #temp table.

— Create Table

Create table #StudentDetail

(Name varchar (50), City varchar (100))

Declare @sql as varchar (500)

Set @sql = ‘Select Name, City from tbl_StudentDetail where ……..’

— Below code give you data in table

Insert into #StudentDetail

Exec (@sql)

So, this is how you can get the data from another procedure or dynamic sql in Table.

Basic thing is whatever the structure return on the basis of that you have to create the table and use that table to get data from procedure or dynamic sql.

I hope this feature is useful for in future. If any query feel free to ask me.

How to get Data from Dataset into Sqlserver Table by using OPENXML Method?

Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.

For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.

Today, I will explain how to get data from Dataset into Sqlserver Table.

— Code for .Net

Dim ds as new Dataset

Dim dt as new Table

dt.TableName = “MyTable”

ds.Tables.add(dt)

Dim lStrWriter As New StringWriter

Dim lStr As String

ds.WriteXML(lStrWriter)

lstr = lStrWriter.Tostring

Pass this lstr variable to the procedure as parameter – @xmldoc.

Now write the code procedure to get the data from Dataset.

— Code in Sqlserver Store Procedure

CREATE PROCEDURE [dbo].[TestXML]

@xmldoc text — Give Text datatype for getting xml from dataset

as

———Read the Datset  From XML —————–

— call sp_xml_preparedocument

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #TestTable

FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)

WITH (pk_Userid  BigInt,

UserName  Varchar(10))

EXEC sp_xml_removedocument @idoc

———- End Read ———-

END

BY the use of this procedure you get all the data from dataset in to the “#Testtable” and we can use this as Sqlserver table in procedure.

I hope this article will be useful to you, Please give feedback on this article.

How to Convert Cursor into While Loop In Sqlserver ?

I personally face this issues in many Store Procedures i.e. Cursor taking too much time to execute. I am sure many of you will be having similar problem.

I have worked on that issue and found following solution. It may not be perfect for cursor optimization but it does work in my case.

— Original Cursor Code —

Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint

declare CUR_MEDICATIONORDERID cursor FOR
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()

OPEN CUR_MEDICATIONORDERID

FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId

WHILE @@FETCH_STATUS =0
BEGIN

Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId

Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”

FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId

END

CLOSE CUR_MEDICATIONORDERID
DEALLOCATE CUR_MEDICATIONORDERID

——————-

— Modified Cursor Code (faster) —

Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint

Declare Table for the fields you need in the cursor.

declare @IDList table (ID Bigint)

Insert into @IDList
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()

while (select count(ID) from @IDList) > 0
begin
select top 1 @fk_MedicationOrderId = ID from @IDList

Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId

Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”

delete from @IDList where ID = @fk_MedicationOrderId

end

——————-

What I have done here is to convert “cursor” into “while loop”. It is just to get your data in any temporary table or table variable after that just delete the rows from that at end of loop.

It helps in improving performance, I have personally tried it in many stored procedures.

Let me know if you find any other useful way of improving performance.