Stored Procedure - Urgent Help Needed

  • manoj2001

    SSCrazy

    Points: 2175

    Hi All Experts,

    I am having one stored procedure which is written by third part and i can't able to make single character change in it. it returns 2 recordset - recordset 1 and recordset 2. By any way it is possible through SQL coding so it return only 2nd recordset (using that SP as input or any way). I dont want 1st recordset. In addition to that i dont want to use SSIS/ASP.NET .... I just want using pure T-SQL statments , Stored procedures...

    Is this possible ? waiting for solution

    Thanks

  • crazy4sql

    SSCoach

    Points: 19590

    if we have the code then it will be easy to comment.

    But as of now, I think one value if you dont want then you can make it to return default which you can define.

    ----------
    Ashish

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Here's an easy way but it depends upon the structure and content of the two result sets:

    -- Make a test proc returning 2 result sets

    CREATE PROCEDURE dbo.junk AS

    SELECT SetID = 1, today = GETDATE() UNION ALL SELECT 1, GETDATE()+(1/24.00)

    SELECT SetID = 2, today = GETDATE() UNION ALL SELECT 2, GETDATE()+(1/24.00)

    RETURN 0

    go

    -- Test it

    EXEC dbo.junk

    GO

    -- Pick one of the result sets

    DROP TABLE #MyTable

    CREATE TABLE #MyTable (SetID INT, today DATETIME)

    INSERT INTO #MyTable (SetID, today) EXEC dbo.junk

    SELECT * FROM #MyTable WHERE SetID = 2

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • manoj2001

    SSCrazy

    Points: 2175

    Hi Chris,

    Thanks for your reply.

    but the problem is types and number of columns are not common in both recordset.

    Thanks

    Manoj

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Sorry, but Chris's solution is probably the best solution you will get without posting the code that's causing you this heartache. We need to see the problem in order to understand it and get you the best possible solution.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • manoj2001

    SSCrazy

    Points: 2175

    Thanks for the help.

    I am explaining my prob:

    create eproc test_2ndrec

    as

    begin

    select name from test_N1 where id = 2 -- returns single row single column

    select * from test3 -- returns 5 rows with 10 columns

    end

    now i want to use second recordset and dont want 1st recordset or you can say i want to hide 1st recordset. procedure modification is also not possible (becuase 3rd party procedure).

    hope now you can understand my proble clearly

    Thanks

    Manoj

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Does "name" in the first result come close to being the same datatype of any of the columns in the second result set?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • manoj2001

    SSCrazy

    Points: 2175

    Yes Definitely. it is of nvarchar() and is there in 2nd resultset too.

  • Brandie Tarvin

    SSC Guru

    Points: 172757

    Then take Chris's code and adjust it. Expand the temp table's column list, making sure the Name column is the first one, then do the insert as he says. Make sure the columns are nullable.

    See if that works for you (it may not, but it's worth a shot).

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Thanks for stepping up Brandie 🙂

    I tried it with a 2-column and 3-column output. Since the result sets are both run into the same table, you get a column mismatch error. I don't think there's a way round this in TSQL other than making minor changes to the sproc.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply