Database

deepseeindeepsy wrote:

Is it possible to fetch it?

Alter Procedure GetMultipleRecords

As

Begin

       -- First select

          Select * from test1

        -- Second   select

          Select * from test2
End

From Asp.net application, by using DataAdapter get the records set into the DataSet

     DataSet ds = getDataRecords();->You will get the records from database

     if(ds!=null && ds.tables.count > 0)
     {

   //Store the result for Select * from test1 into datatable firstDt

          DataTable firstDt = ds.tables[0]; 

//Store the result for Select * from test2 into datatable secondDt  

          DataTable secondDt = ds.tables[1]; 
     }

Select A.ID, A.RefID, (Select count(*) from Table where RefID = ID) Total
From Table

declare @tbl table(id int identity, refid int)
insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1

select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total
from @tbl t
left join
(
select refid,count(refid) as cnt
from @tbl
where refid <> 0
group by refid
having (count(refid)>0)) x
on t.id = x.refid

1	0	3
2	1	1
3	2	0
4	1	0
5	1	0

sarang_k wrote:

you wil get another window then click on add and choose your database from the pen drive.

Case <expression>
when 0 then <expression>
when 1 then <expression>
ELSE <expression> 
END

Mycroft Holmes wrote:

Send me the codz.

sarang_k wrote:

How to use the Case in stored procedure

is a normal thing.

Create procedure TestCaseStatement
as

Begin
	declare @tbl table(id int)
	insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1
		
	select 
		id
		,case when id = 1 then 'One' else 'Other Number' end as Value	
	 from @tbl
End

Create procedure TestCaseStatement
as

Begin
	declare @tbl table(id int)
	insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1
		
	select 
		id
		,Value = case when id = 1 then 'One' else 'Other Number' end 
	 from @tbl
End

0	Other Number
1	One
2	Other Number
1	One
1	One

select *
From Widgets
Where CustomerID = @CustID

select count(*),WidgetTypeID
From Widgets
Where CustomerID = @CustID
GroupBy WidgetTypeID

Insert OtherTable(CustID,WidgetTypeID, Qty)
select CustID, WidgetTypeID, count(*)
From Widgets
Where CustomerID = @CustID
GroupBy CustID, WidgetTypeID

declare @tblCustomers table(CustID int,CustomerName varchar(50))
declare @tblWidgets table(CustID int,WidgetTypeID int, Qty int)
declare @tblTarget table(CustID int,WidgetTypeID int, Qty int)
insert into @tblCustomers values(101,'Cust1'),(102,'Cust2'),(103,'Cust3')
insert into @tblWidgets values(101,1,5),(101,2,1),(101,3,3),(102,10,5),(103,4,1)

insert into @tblTarget
select CustID,WidgetTypeID,Qty
from
(
	select w.CustID,w.WidgetTypeID,w.Qty from @tblWidgets w
	inner join @tblCustomers c
	on c.CustID = w.CustID
)X where X.CustID =101
select * from @tblTarget

CustID	WidgetTypeID	Qty
101	1	5
101	2	1
101	3	3

ffowler wrote:

So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces.

What I need to end up with is a new table with 9 entries. 1 for each widget piece.

CustId  WidgetID
A       W1
A       W1
A       W1
A       W1
A       W1
A       W2
A       W3
A       W3
A       W3