Hello
i have one prob . i m using sql server 2000 . and i have write a store procedure to fetch data from multiple table using cursor . this query exculate in sql server . but i can't fetch this data from in our page . how to fetch data from using multiple table .plz help
thnx
Hi shruram.gore,
You don't need to use cursor for this unless you need to do some operations (processess) before displaying the data.
Cursor will not help you in displying the data, to display the data use SELECT and if you need to fetch the data from more than one table, use JOIN.
Example:
SELECT a.col1, a.col2, b.col2FROM table1 aINNERJOIN table2 bon (a.id = b.id)
Good luck.
|||
thnx , for suggestion
But dont have fix table name . in my prob my table name is variable plz check the query . this query we will use to bind treeView to show parent child releation . i have execute data from this query but we cant fetch data in dataset
thanx in advance
CREATE PROCEDURE [dbo].GetRootChild
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @.parentId int
declare @.TableName varchar (50)
declare @.RelIDName char(10),@.s int,@.sql varchar(4000)
declare comments_list CURSOR FOR
select distinct [PcRelation].[ChID],[ObjectType].[ObjName],[ObjectType].[RelIdName] from PcRelation,ObjectType where [PcRelation].[ChObjectID]=[ObjectType].[ID] and [PcRelation].[PID]='0' and [PcRelation].[PObjectID]='0'
OPEN comments_list
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE product_cursor CURSOR FOR
select @.TableName as TableName
--select * from Company
select @.sql ='select * from [dbo].['+ @.TableName +'] where ['+ @.TableName +'].['+@.RelIDName+']= '+convert(char(10),@.parentId)+''
OPEN product_cursor
FETCH NEXT FROM product_cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
--select @.TableName as TableName
exec(@.sql)
FETCH NEXT FROM product_cursor
END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
END
CLOSE comments_list
DEALLOCATE comments_list
GO
and fetch data from sql
if cant image refer this url
http://shriramgore.ifastnet.com/?q=node/41
thnx
|||
Hi,
Based on my understing, since you didn't do any row-based operations on the table(after you have fetched each row, seems you didn't perform any operations), there is no need for you to use cursors. If what you want is just to fetch data from multi tables, a simpleJoin will work in your case. Use dynamic query if you cannot determine your table name until run-time. Like:
declare @.str nvarchar(100) ,@.str1 varchar(100)set @.str='table_1'set @.str1='table_2'exec ('select * from '+@.str+' as a inner join '+ @.str1+' as b on a.id=b.id') Hope my suggestion helps|||
thnx for replyBo Chen
but i have following like ......... please check example ...
I have data from multiple table, those table's Names are store as Variable in a table . in this Case i have written 1 store procedure using Cursor . this query Execute in server like (Please check this Url -http://shriramgore.ifastnet.com/?q=node/41&size=_original ) . after executing storeprocedure i get data in different tabels this data i can't fetch in single dataset .if i will fetch all tables(results of storeprocedure) then it eaiser to me for next part .
e.g
DataSet ds = new DataSet();
ds = proc.GetDataSet();
for (int i= 0; i< ds.Tables.Count; i++)
{
for (int y= 0; y< ds.Tables [i].Rows.Count; y++)
{
String str=ds.tabels[i].rows[y]["ColumnName"].toString();
}
}
in this example i enable to get other tabels (dsParent1.Tables[int] ).
please help
thnx in advance
|||
Hi shriram.gore,
I can see your problem. In my opinion, i think you can try to revise your stored procedure, like this: pass three or four parameters(as you need) into your stored procedure and make them as output paramters. In your stored procedure, you can assign them the table-name values during sql clause execution. For example:
create procedure sp_get_tbl_name (@.str1 varchar(100) output,
@.str2 varchar(100) output, @.str3 varchar(100) output...)
as
select @.str1=tablename
from yourtablename
where .....
go
select @.str2=tablename
from yoursecondtablename
where...
go
..
Hope my suggestion helps
|||
Hello ,
thnx for Reply
Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .
excuse i m not using sqlserver 2005
thnx
shriram
|||
Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .
If i understand you correct, that's not a problem.
Since now you have got those table names, you can user your sldataadpater to perform a sql data retrieval against each data table name--after that you will get server datasets, say, ds1, ds2,ds3,ds4..etc. Generally speaking each dataset you get contains only one table(it depends on your select command)
Then you can create a new dataset named ds, run : ds.tables.add(ds1.table[0]); ds.tables.add(ds2.table[0]),ds.tables.add(ds3.table[0])...etc..
Now your dataset ds contains all the tables you need. You can perform your loop on it.
BTW, what does "m i rt. " stand for??
thanks
|||
HelloBo Chen ,
thank u very much . my prob is solved .
thnx
shriram
This alternate syntax is the new standard, but the old way is still quite common. Here is the above rewritten to the new standard:
nevets2001uk2: