Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: What am I missing here? Using a stored proc output to build table rows

    Hi all,

    I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.

    To continue with the set up, here is the stored procedure in question:
    Code:
    CREATE PROCEDURE [dbo].[HE_GetStks] AS
    
    select top 15
    	Rating,
    	rank, 
    	coname,
    	PriceClose,
    	pricechg,
    	DailyVol,
    	symbol
    from
    
    (select	f.rating,
    	f.rank, 
    	s.coname,
    	cast ( f.priceclose as decimal(10,2)) as PriceClose,
    	cast ( f.pricechg as decimal(10,2)) as pricechg,
    	f.DailyVol,
    	f.symbol
    from dailydata f,  snames s
    where f.tendcash = 0
    and f.status = 1
    and f.typ = 1
    and f.osid = s.osid) tt 
    order by rating desc, rank desc
    
    GO
    The code in the calling bat file is:
    Code:
    REM *************************
    REM BCP .WRK FILE
    REM *************************
    bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:\TABLES\INPUT\HE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
    This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.

    Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.

    I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.

    Any thoughts?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by TallCowboy0614
    but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
    Create a view from the select statement and base your 2 sprocs off that. Then you only have the one place to change the SQL
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you mean:
    Code:
    INSERT INTO mytable
    EXEC    dbo.myproc
    ?????

    If the select changes then you need to change the DDL of the destination table of course. There is a fudge to get the result of the execute into a dataset that you can use like a derived table - I think it is using OPENQUERY, or at least one of the pass through commands - I forget which.

    Or maybe I missed your point entirely....

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    fyi, there are a couple cases where INSERT...EXEC does not work. (looks like you won't hit either of these though)

    one is if the proc you are calling itself uses INSERT...EXEC (into a temp table for example). Basically they can't be nested. not an issue in this case as your proc is just a simple select.

    The other case is if the proc uses a linked server call and that linked server is a loopback (pointing to itself). not a best practice to have a linked server back to yourself anyway, but sql server will let you create one, so there it is. also not an issue for you from the look of your proc.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    The other case is if the proc uses a linked server call and that linked server is a loopback (pointing to itself). not a best practice to have a linked server back to yourself anyway, but sql server will let you create one, so there it is. also not an issue for you from the look of your proc.
    Now you mention it - is that the method I was thinking of to create a set out of a sproc output?

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    no. the OPENQUERY thing is actually one way to get around the nested INSERT...EXEC limitation.

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by pootle flump
    Do you mean:
    Code:
    INSERT INTO mytable
    EXEC    dbo.myproc
    ?????

    If the select changes then you need to change the DDL of the destination table of course. There is a fudge to get the result of the execute into a dataset that you can use like a derived table - I think it is using OPENQUERY, or at least one of the pass through commands - I forget which.

    Or maybe I missed your point entirely....
    Thanks guys-n-gals...I actually THOUGHT I tried this...Oh no, I didn't...*LOL* I KNEW it was going to bite me in the ass if I left out "insiginificant" code from my example. My insert actually adds a smalldatetime value for the current day, THEN the rest of the columns from the sproc. That is probably my problem with using the EXEC on the insert (I tried to use the EXEC in a subquery, which failed). Let me make sure this works for me as presented, then I will post the code and see what can be done, if anything.

    I can always do the insert and then update the date field in a separate select, or default it...just not sure the latter will work because the insert by EXEC probably won't let me use a list of columns on the insert. I'll play with it a bit and see what I can do.

    It all sounds kind of kludgy to me anyway, but I suppose if this select ever changes anyway, it would affect an interim table the same way as an insert from the EXEC (meaning I would still have to change MY table's DDL if the select changes). It makes me nervous anyway, but I guess the underlying risk of a change is always there no matter how you do it.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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