dynamic sql for multiple view definitions

Last Post 17 Jan 2008 08:52 AM by pattymelt. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
pattymelt
New Member
New Member

--
17 Jan 2008 07:47 AM
Inside a beginning stored procedure, I need to write code that will retrieve data for this scenario:
For a given request, there might be N views (each view might have a different definition.) Each view needs to be executed, and for each row returned, the data field values need to be captured.

I have some beginning dynamic sql that gives me the name of each view (I am using a cursor to process through the views.) Now I need more dynamic sql to be able to process each row returned from each view. I'm guessing that the column names for each view would have to be obtained from information_schema.columns and then some dynamic sql used to build the select statement which probably needs to be another cursor.

Does anyone have any suggestions?
pattymelt
New Member
New Member

--
17 Jan 2008 08:52 AM
This is a simplified version of the Stored Procedure code I have now (did not include variable declares):

DECLARE c1 CURSOR
FOR
SELECT RequestID,ValView
from ValSel -- This Select gives each view for each request.

OPEN c1

FETCH NEXT FROM c1
INTO @RequestID, @ValView

WHILE @@FETCH_STATUS = 0
BEGIN

SET @strSql = 'select @cnt=count(*) from ' + @ValView

PRINT @strSql
EXEC sp_executesql @strSql, N'@cnt int OUTPUT', @cnt OUTPUT -- See if the view will return rows.

IF (@cnt > 0)
BEGIN
PRINT @cnt
/* Call function BuildColumnNames to get the column names from this view
and return the names as one string. */

I won't include this function's code, but it simply uses information_schema.columns to get the
column names from the view and made one string from them. I could use this information to
build a dynamic string of column names, but then I'm not sure how to proceed.
SET @strColNames = dbo.BuildColumnNames(@ValView)
PRINT @strColNames

/* Run the view and retrieve each row's values. */

DECLARE c2 cursor
FOR SELECT
??? This is where I'm stuck; how do I dynamically declare this cursor
with the column names I've gotten from information_schema.columns
OR is there a better way?????
FROM @ValView
????
END


FETCH NEXT FROM c1
INTO @RequestID,@ValView

END

CLOSE c1
DEALLOCATE c1
pattymelt
New Member
New Member

--
17 Jan 2008 10:06 AM
The
set @cols = 'colA, colB'
and the
fetch next from c into @a, @b
that you mention are my problem. I don't know this information ahead of time.

The number of columns could be one or ten or twenty depending on the view (there are multiple views per request, and they all have different columns.) I have to dynamically determine the columns for each view and then fetch those column values and "do something" with them.
pattymelt
New Member
New Member

--
17 Jan 2008 10:46 AM
For each row retrieved from the view, I have to attach the value of each column back to the original request information.

Request
Multiple views (each with different columns)
Multiple rows per view - each row's column data values to be attached to the original request
SQLUSA
New Member
New Member

--
17 Jan 2008 10:56 AM
Patty,

Handling multiple (and different) result sets is very difficult in app environment.

You should envelope each view into its own stored proc and have the app call the appropriate stored proc and process the results.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
You are not authorized to post a reply.

Acceptable Use Policy