How to execute all rows in a dynamic query

[vyas]

Hi,

I want to create a Procedure in sql server.That will search data dynamically from different table and columns
Data is like in a Hierarchical form
('\Locations\F-Olympia Armory Complex (OL-01)\Olympia Armory Building (102660)')
So my paramter in the fuction are
table name
column name
level
( e.g. if i give value as 2 it will return "F-Olympia Armory Complex (OL-01)")
I am able to write this for one row ,but i want to enchance this for all rows


Create Procedure funct_Hierarchy(@p_table_name varchar(200),
@Col_name varchar(200),@level int)

As
Begin
DECLARE @v_str NVARCHAR(400)
Declare @v_col varchar(200)
DECLARE @i integer
DECLARE @pos integer
DECLARE @pos1 INTEGER
DECLARE @v_sql NVARCHAR(500)
Declare @P_sql_msg varchar(200)

Set @v_str=''
Set @v_sql=''

Set @v_sql=N'Select @v_col='+ @Col_name + ' From ' + @p_table_name + ' Where spec_id=3005993 '

Exec sp_EXECUTEsql @v_sql , N'@v_col VARCHAR(200) out'
,@v_str OUT

IF @@ERROR <> 0
BEGIN
SET @P_sql_msg = -1
END

Set @pos1=0
WHILE (@i <= @level )
BEGIN
set @pos = patindex('%\%',@v_str)
SET @pos1 = @pos1 + @pos
set @v_str = SUBSTRING ( @v_str , @pos+1 , len(@v_str)-@pos )
SET @i = @i+1
END

set @pos = patindex('%\%',@v_str)
IF (@pos > 0)
BEGIN
SET @v_str = SUBSTRING ( @v_str ,1, @pos-1)
END

SET @P_sql_msg = @v_str
print @P_sql_msg

End