July 29, 2011 at 5:50 am
Hi All,
This is not critical, but would like to know what the logic to do this work is.
There is a procedure which is returning 2 separate record sets (Refer 2nd Part).
I need to get both the record sets from the SP and make to a single record set. For e.g.:- In the 4th part, there are two SELECT queries. Both should return same results.
Can we do this without modifying the stored procedure? Assume any code is NOT available for 1st and 2nd part. We have only "pDataGet" stored procedure with us and it is not able to modify. We can do modification in 3rd and 4th part.
You can check the work around below. It will throw error in the 3rd Part.
--==========================================================
--== 1st Part
--==========================================================
--// Create test table tOne.
CREATE TABLE tOne
(
citID INT,
cvcName VARCHAR(100)
)
GO
--// Insert test data to table tOne.
INSERT INTO tOne(citID, cvcName)
SELECT 1, 'Name_1'
UNION ALL
SELECT 2, 'Name_2'
GO
--// Create test table tOne.
CREATE TABLE tTwo
(
citID INT,
cvcName VARCHAR(100),
cdtDOB DATETIME
)
GO
--// Insert test data to table tOne.
INSERT INTO tTwo(citID, cvcName, cdtDOB)
SELECT 11, 'Name_11', '1975-01-01'
UNION ALL
SELECT 12, 'Name_12', '1980-02-15'
UNION ALL
SELECT 13, 'Name_13', '1970-02-15'
GO
--==========================================================
--== 2nd Part
--==========================================================
--// Create procedure with 2 select queries.
CREATE PROCEDURE pDataGet
AS
BEGIN
SELECT citId, cvcName FROM tOne
SELECT citId, cvcName, cdtDOB FROM tTwo
END
GO
--// Testing procedure
EXECUTE pDataGet
--==========================================================
--== 3rd Part
--==========================================================
--// Create temp table table insert data from procedure.
CREATE TABLE #tAllData
(
citID INT,
cvcName VARCHAR(100),
cdtDOB DATETIME
)
GO
--// Insert data from procedure
INSERT INTO #tAllData EXECUTE pDataGet
GO
--==========================================================
--== 4th Part
--==========================================================
SELECT citID, cvcName, cdtDOB FROM #tAllData
GO
SELECT citId, cvcName, NULL FROM tOne
UNION ALL
SELECT citId, cvcName, cdtDOB FROM tTwo
--// Drop the objects
DROP TABLE tOne
DROP TABLE tTwo
DROP PROCEDURE pDataGet
DROP TABLE #tAllData
--End==========================================================
_____________________________________________
One ounce of practice is more important than tonnes of dreams
July 29, 2011 at 7:02 am
Hi,Shaiju.
I see
"Column name or number of supplied values does not match table definition."
You can try a bit of a change in the procedure
-----------------------------------------------------------
CREATE PROCEDURE pDataGet
AS
BEGIN
SELECT citId, cvcName,'1900-01-01'cdtDOB FROM tOne
UNION
SELECT citId, cvcName, cdtDOB FROM tTwo
END
-----------------------------------------------------------
I think that not everything here is very good,
but not everything is bad.
In such situations, they say, you can change the image.
Sergey.
July 29, 2011 at 8:15 am
Good Morning,
I'm not sure if this is possible using T-SQL, the following page discusses this a bit more:
July 30, 2011 at 3:42 am
setiv (7/29/2011)
There is a possibility of a competing tOne.citID and tTwo.citID.This is where the rack is pepper?
I see
Server: Msg 213, Level 16, State 7, Procedure pDataGet, Line 9
Column name or number of supplied values does not match table definition.
You can try a bit of a change in the procedure
-----------------------------------------------------------
CREATE PROCEDURE pDataGet
AS
BEGIN
SELECT citId, cvcName,'1900-01-01'cdtDOB FROM tOne
UNION
SELECT citId, cvcName, cdtDOB FROM tTwo
END
-----------------------------------------------------------
Yes, we can do this by using a UNION after modifying the SP. But my question was how we can do this without modifying SP. Assume we do not have permission to do that.
NuNn DaddY (7/29/2011)
I'm not sure if this is possible using T-SQL, the following page discusses this a bit more:
Had gone through that link :(. But there they provided an other technique to do that by using ADODB. Anyway, atleast there is one solution. Thanks 🙂
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 1, 2011 at 3:49 am
C.K.Shaiju (7/29/2011)
Hi All,...
There is a procedure which is returning 2 separate record sets (Refer 2nd Part).
I need to get both the record sets from the SP and make to a single record set. For e.g.:- In the 4th part, there are two SELECT queries. Both should return same results.
Can we do this without modifying the stored procedure?
...
Basically your question is: Can I modify the stored procedure without modifying it.
I'm very sorry but I don't think you can do it.
However, from the theory of relativity point of view, it should be possible. You just will need to move your hardware with the speed of light 😀
Have you considered the option of combining two resultset into one in your client app?
August 1, 2011 at 5:20 am
Eugene Elutin (8/1/2011)
Basically your question is: Can I modify the stored procedure without modifying it.
🙂 lol. I like that. But this just clear my curiosity 😀
Assume we do not have permission to modify the sp. Something like a SYSTEM stored procedure or client is not allowing us to modify the stored procedure and which is giving more than 1 recordset. In that case, we have to get the code of the sp and recreate by giving a new name. Even that also not possible if that stored procedure is ENCRYPTED or denied VIEW_DEFINITION permission.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 1, 2011 at 5:24 am
C.K.Shaiju (8/1/2011)
Eugene Elutin (8/1/2011)
Basically your question is: Can I modify the stored procedure without modifying it.🙂 lol. I like that. But this just clear my curiosity 😀
Assume we do not have permission to modify the sp. Something like a SYSTEM stored procedure or client is not allowing us to modify the stored procedure and which is giving more than 1 recordset. In that case, we have to get the code of the sp and recreate by giving a new name. Even that also not possible if that stored procedure is ENCRYPTED or denied VIEW_DEFINITION permission.
Just ask the vendor for a 2nd version that does xy instead of yz. They usually don't mind billing more hours for that kind of stuff...
August 1, 2011 at 5:31 am
Ninja's_RGR'us (8/1/2011)
Just ask the vendor for a 2nd version that does xy instead of yz. They usually don't mind billing more hours for that kind of stuff...
🙂 OK.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 1, 2011 at 9:56 am
Ask vendor 2nd version is entirely reasonable
would not create the impression
the client is trying to invade the server.
------------------------------------------------------
------------------------------------------------------
Hi all.
I was pleased to get acquainted with all the present team
and I will be happy to share the same views with all
the discussion of regular tasks.
Sergey.
August 2, 2011 at 2:25 am
Yeah setiv
_____________________________________________
One ounce of practice is more important than tonnes of dreams
August 2, 2011 at 3:07 am
Hi all.
I was pleased to get acquainted with all the present team
and I will be happy to share the same views with all
the discussion of regular tasks.
Sergey.
August 2, 2011 at 3:10 am
Hi all.
I was pleased to get acquainted with all the present team
and I will be happy to share the same views with all
the discussion of regular tasks.
Sergey.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply