Stored Procedure - Urgent Help Needed

Question

Post reply

Stored Procedure - Urgent Help Needed

manoj2001

SSCrazy

Points: 2285
More actions
July 26, 2011 at 11:50 pm

#241458

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

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

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: 186127 More actions · Answer 2

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

manoj2001 SSCrazy Points: 2285 More actions · Answer 3

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: 173080 More actions · Answer 4

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: 2285 More actions · Answer 5

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: 173080 More actions · Answer 6

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: 2285 More actions · Answer 7

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

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 8

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: 186127 More actions · Answer 9

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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