Click here to Skip to main content
15,887,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi all...
what are the possible ways to get the table names dynamically in stored procedure...
if it possible ple guide me with an example...........

thanks in advance.....
Posted
Comments
Tejas Vaishnav 17-Sep-11 1:12am    
Please elaborate your question so we understand what you want..
MemberCva 17-Sep-11 3:03am    
here is the sample senario,
am using cursor, for the first fetch it exec a select operation from one table i have set that as secondary.
then for the next fetch it exec a select operation from another table during this execution, how i hav to get the values of the first executed fetch to this execution.....

am using cursor here, if cursor not needed for this then how i can do this.....

thanks n advance.....

To execute queries on runtime specified tables you have to run dynamic queries using exec. There are a lot of security concerns associated with this.
Read the MSDN link : EXECUTE (Transact-SQL)

For example:
SQL
exec('select * from ['+@tablename +']')
 
Share this answer
 
Comments
MemberCva 17-Sep-11 3:17am    
thnks Mehdi,
the table name parameter @tablename1 given here is for the first fetch using cursor
my need is for the second time of fetch it should select the another table declared parameter @tablename2 during this process i need the executed result of the first fetch from that table, the first executed result should be the sub query for the second execution of the cursor fetch at the second time......

thanks in advance...........
Mehdi Gholam 17-Sep-11 3:19am    
just change the @tablename variable each time.
MemberCva 17-Sep-11 6:12am    
thanks,
this is my SP for the need i mention above , the table name to be get dynamically

create procedure SP_M
(
@proc nvarchar(50),
@vc nvarchar(255)
)
as
begin

declare @TABLENAME varchar(50)
declare @TABLENAM_IDENTITY nvarchar(50)
declare @prtable nvarchar(50)
declare @sectable nvarchar(50)

DECLARE @curs CURSOR
SET @curs = CURSOR FOR
SELECT TBL_R_ID,TBL_IDENTITY
FROM PR_TBL_IDENTITY WHERE PR_R_ID=@proc

OPEN @curs
FETCH NEXT
from @curs into @TABLENAME,@TABLENAM_IDENTITY

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @TABLENAME ---- t1 and t2, t1 has some set of datas, t2 has some set of datas
PRINT @TABLENAM_IDENTITY --- t1 is primary, t2 is secondary

IF @TABLENAM_IDENTITY='PRIMARY'
begin

set @prtable= 't'+@TABLENAME+'_V' --table 1

exec('select I_ID from'+ @prtable+'where VAL='+@vc)

end
----- i dont know what to do here to achieve my need @prtable for secondary

IF @TABLENAM_IDENTITY='secondary'
BEGIN
set @sectable='JITS_'+@TABLENAME+'_VALUE' -- table 2

exec('select C_N,C_V from'+@sectable+' where I_L_ID
in(select I_ID from'+ @prtable )

END

FETCH NEXT
FROM @curs INTO @TABLENAME,@TABLENAM_IDENTITY
END
CLOSE @curs
DEALLOCATE @curs
end

pls guide me
thnks in advance....
i have got the result by small changes on that,
but now the result on cursor is executing continuously how to rectify the problem
 
Share this answer
 
Comments
MemberCva 20-Sep-11 7:22am    
thanks for all ........
i got the final result to instead of using while i use if condition for the fetch status... it works......

with regards....
siva

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900