Two questions...
1) In a stored proc, I'm trying to execute a simple query 'on the fly' and stuff the results in a temp table.
I've seen code posted on this forum that does the following...
**************************
Declare @lcSql VarChar(1000)
CREATE TABLE #returnvalue ( cReturn VarChar(1000) )
Set @lcSql = 'Select cField From Table where ' + @tcWhere
INSERT INTO #returnvalue Exec (@lcSql)
*****************************
Here's my problem...in this example, the SELECT statement was pulling back a static/fixed set of columns. I will be bringing back multiple columns in my 'dynamic SELECT' statement, and I don't know what the columns are until runtime. [The SELECT field list is coming from a function]. So doing a CREATE statement on one field doesn't work for me.
In partial T-SQL, partial FoxPro, here's what I'm trying to do...
DECLARE @cSQLLine VARCHAR(1000)
DECLARE @cSQLCmd VARCHAR(1000)
@cSQLLine = GetFieldList('sometable') --- this is a stored proc that will
return a comma delimited list
@cSQLCmd = 'SELECT ' + cSQLLine + ' FROM INSERTED'
INSERT INTO #TempTbl EXEC (@cSQLCmd)
2) Related question...I know that UPDATE triggers have access to the INSERTED/DELETED tables. [It seems that INSERTED/DELETED are only visible inside a trigger] I'd like for an external stored proc to have access to these. The only way I know is to do the following...
SELECT * INTO #TEMPINSERTED FROM INSERTED
...and then my stored proc will have access to it. Is there any other way for a trigger to 'pass' the INSERTED/DELETED system tables to a stored proc?
Thanks a million to whomever can help me solve this puzzle!
Kevin