Retrieve column UserID from various recordsets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Retrieve column UserID from various recordsets

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-27 : 22:50:53

I've got a stored proc which has the job of queueing rows into an "email" table. The problem is, I'd like to have any of several different reports (all of which call different stored procs) use this same procedure to queue the outbound email.

The server side code is ASP, and the reports are built there, each report has it's "own" string of SQL, something like:

EXHIBIT A:
EXEC dbo.ReportProcedure10 @Parm1= , @Parm2= etc.

Result recordsets from all these procedures have one thing in common. They all return a column "UserID".

What I'd "like" to do when the decision is made to send email to everyone in a report is call the stored procedure (dbo.DoEmail) and pass the string "EXHIBIT A" above as a parameter.

The trick becomes... how can procedure dbo.DoEmail execute the string and get at the column "UserID" in the returned recordset when it doesn't know the number or names of the other columns?

Is there a way to execute the passed SQL string and create a #temporary table with all the columns??

Here is what I've tried:

CREATE TABLE #temptable (
UserID INT NOT NULL
)

insert into #temptable (UserID)
EXEC (@SQL)

Insert Error: Column name or number of supplied values does not match table definition.

The fix for this solution requires #temptable to be created with all the right columns. Is there a way to do this?

Another solution I had some hope for:

SELECT UserID
INTO #Temptable
FROM (EXEC(@SQL))

Syntax error. can't do that.

Any ideas??

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-01-28 : 01:14:44

can you convert these stored procs into UDFs.
That would make it as easy as..


SELECT UserID FROM dbo.ReportProcedure10(Parm1,Parm2,...etc)

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-28 : 01:40:55

Create table #t (userId int not null)
insert into #t exec(Procedure 'parameters'' )
Select * from #t
drop table #t

Madhivanan

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-01-28 : 03:29:16

quote:
Originally posted by madhivanan

Create table #t (userId int not null)
insert into #t exec(Procedure 'parameters'' )
Select * from #t
drop table #t

Madhivanan

How is it different from what Sam is doing?
The procedures return multiple parameters out of which USERID is one. He is looking a way to extract only this column from the unknow number of output columns from any of the stored proc.

Sam, Correct me if I am wrong..

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-28 : 07:03:31

you could always try it with a global temp table inside the sproc...

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-28 : 08:18:35

Thanks for the help so far. Looks like I am still stuck.

None of the proposals above are a slam-dunk. I suppose I could write stored procedures as pairs, one being a UDF and the report procedure calling the UDF, but this would really convolute matters in other ways: The ASP web code knows of the report procedure, not the UDF, so it can only pass the report proc call to the email routine (as described in my first post).

A Global Temp Table sounds possible, but again not clean. How would the the Global Temp table be recognized by the calling procedure? How would race conditions be resolved, and where does the clean-up occur (these tables might be large).

Another solution I had in mind was to add another parameter: @ReturnEmailUserID=1 or something like that to ALL the stored procedures. The email procedure could append this parameter to the string so the following would work:

insert into #temptable (UserID)
EXEC (@SQL)

The problem with this approach is that it doubles the code inside every reporting procedure. I would be banned from the church of minimalism if I did this.

But it may be the only way.

Any other suggestions appreciated...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-28 : 09:09:28

Would something like this work?:

Each of your individual report SPs has an input parm (@QueueUserID bit) and
You have a permanent table: EmailQueue (UserID, <email details>)

when any ReportProcedure is called from doEmail the @QueueUserID flag is set and the ReportProcedure inserts necessary details to EmailQueue.

Maybe there is a job that does nothing but sends the queued email and flags the record as sent when successull (or deletes them).

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-28 : 09:39:41

quote:
Originally posted by TG

Each of your individual report SPs has an input parm (@QueueUserID bit) and
You have a permanent table: EmailQueue (UserID, <email details>)

The choice of sending an additional flag doubles the code in the stored procedure either way. I guess it's the way to go...

Another solution is to build "knowledge" of the recordsets of each of the stored procs. It would require a lot of IF/THEN resulting in a cascade like:


IF CHARINDEX('dbo.ReportProcedure10', @SQL) > 0 BEGIN
   CREATE TABLE #temptable (
      UserID INT NOT NULL ,
      -- CREATE ALL OTHER COLUMNS OF ReportProcedure10 here
   )

   insert into #temptable (UserID, list all other columns of ReportProcedure10 here )
      EXEC (@SQL)
END ELSE IF CHARINDEX('dbo.ReportProcedure20', @SQL) > 0 BEGIN
  -- Create a table here and insert all rows
END ELSE IF CHARINDEX ('dbo.ReportProcedure30', @SQL) > 0 BEGIN
  -- Create a table here and insert all rows
END

While the above seems messy, it has the benefit of NOT doubling the code in every reporting procedure.

The downside is that if a recordset is revised in any reporting procedure, you'll need to revise the cascade above to reflect the new column(s).

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-28 : 09:45:21

This is getting complicated, let me restate the problem again without the fluff.

EXEC (@SQL) -- will return several columns. All I want is UserID, I don't know the other columns.

How can I get at the UserID column to do a join on table dbo.Users (UserID)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources