Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002

    Unanswered: SProc results as parameters to 2nd SProc

    This is a bit of an odd nut that I am having trouble cracking. I'll do my best to explain.

    First let me explain that I am having to work within the limitations of the system and do NOT have the flexibility to go changing around some of these things...

    I am trying to write a semi-generic process that will move data from one table to another. For auditing reasons, I should be doing this through the existing stored procedures in the system. There's a sproc that will return the data for me. There's another that will write the data to the new table. So, I now have both end points, I now need to hook the two end points up.
    If the results of the sproc mirrored the table structure, I would simply do an Insert Into Exec .... but 1) the structure of the one doesn't match the other and 2) of the 7 fields returned, I only need 4, and two of the remaining 3 need to have their values replaced.

    Further complicating the issue:the source isn't a single sproc... it could be a number of any sprocs that are specific to the data being sought. Each time one of these sprocs is run, I get the ID, the VALUE, COMMENTS, and a date range.... ID is a uniqueidentifier, Comments is always a nvarchar, and the date ranges are always datetime ... that much is standard and expected.

    Hitch #1 - The datatype for VALUE .... isn't consistant... in one sproc it maybe a varchar, in an another a uniqueidentifier, or a datetime, or even an integer.

    Hitch #2 - due to the architecture, the actual sproc that's being run is done so via dynamic SQL - don't ask, it is what it is, I'm not a big fan of it, but it's part of the architecture and I'm not in a position to make any kind of changes to it.

    So, the question is how do I get these 4-5 value out of the sproc results, into variables so I can pass them as parameters to another sproc? And all with out it complaining that it can't convert my datetime into a uniqueidentifier or the other way around?


  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    It might be better if you show us some psuedo code of the sprocs you're trying to work with.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2002
    The "source" sproc will look something like this:

    create procedure [dbo].[USP_SomeObscureNamingConventionHere_GETINFORMATIONBYID]
    	@ID uniqueidentifier = null
    		set nocount on;
    			SOMEFIELD as VALUE, -- the SOMEFIELD may vary from sproc to sproc... but it's always returned as VALUE
    			ID = @ID;
    "SomeObscureNamingConventionHere" is derived at runtime, there isn't a way to know what it is until then... I thought about dumping the results into a temp table... I've done that in other areas of the system, but this is done inside of a loop, where a different sproc will get called each time (which is why the datatype for VALUE changes) ... But that means creating a temp table, inserting into it, moving the data, then dropping the temp table on each round of the loop. bleh.

    I've also got a view that can return similar data, and from which I was able to assign the results to variables, but does some transaction on the data under certain circumstances. I've actually got it "working" with the view ... but it meant I had to add a couple of case statements and a series of If statements to properly type the VALUE parameter. I never thought I'd hear myself ever actually say this but: I wish there was a variant type like VB has.

    Grrr.... it's only Tuesday... and too early for a

    Here's the assembly of the dynamic SQL using the view method:
    set @PARAMS = @PARAMS + '@COMMENT nvarchar(255) output, @STARTDATE datetime output, @ENDDATE datetime output, @ID uniqueidentifier'
    Just before that, I have a case statement that first set up the @VALUE parameter (in the @PARAMS variable) to the correct type....

    I then have 10 @VALUE_X variables (where X is 0 to 9) ... that are also properly typed (there are 10 possible type codes)

    Following that, are the 10 IF statements:
    		if @DATATYPECODE = 0
    			EXEC sp_executesql @SQL, @PARAMS, @VALUE=@VALUE_0 output, @ID=@VALUEID, @COMMENT=@COMMENT output, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output 
    		if @DATATYPECODE = 1
    			EXEC sp_executesql @SQL, @PARAMS, @VALUE=@VALUE_1 output, @ID=@VALUEID, @COMMENT=@COMMENT output, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output
    		if @DATATYPECODE = 2
    			EXEC sp_executesql @SQL, @PARAMS, @VALUE=@VALUE_2 output, @ID=@VALUEID, @COMMENT=@COMMENT output, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output
    I did it like this so that the proper @VALUE datatype is passed in and out ala @VALUE_X

    It's a mess, it's a hack... but it seems to be giving me everything I need 99% of the time (the 1% is a special case - where the view does translation on the VALUE field, but I think I can work around that.)

    Unless someone's got a better idea.


  4. #4
    Join Date
    May 2002
    I think I've got it... I ended up doing what I was trying to avoid. I dumped the view as it was doing too much translation/transformation on the data for me. Went back to the sproc, on each iteration, I create a ##tempTable, with a single field. Then based on the expected datatype, add the VALUE column of hte correct type, followed then by the rest of the standard fields. I then use the dynamic sql to run the sproc and send the results into the ##tempTable. From there I select out what I need into variables, build another dynamic sql and pass the parameters to that when executing the insert sproc. Kludge, I know, but it seems to get the job done. Wow... sometimes I don't which is more challenging... implementing what the client wants/needs.... or having to work within existing architecture.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts