June 19, 2011 at 9:50 pm
guys i need help on this one. they said that this is not possible but i still want to hear from an expert like you guys, if this is not possible.
currently i have this declaration and usage of this sp. The sp accept int value for activity id and varchar for userid
declare @returnCd varchar(255)
exec @returnCd = spname 1, 'testid111'
select @returnCd confirmationCd
The output of that sp is this:
SELECT 'request blocked' AS tMessage
SELECT 1 AS tCode
as you will notice it has 2 dataset, but i want to do is to only have a 1 dataset so the output should look like this
declare @tTable table(tMessage varchar(255), tcode int)
INSERT INTO @tTable (tMessage,tCode)VALUES('request blocked',1)
SELECT * FROM @tTable
if you will asked me to do an alteration on the sp itself, i can't. they don't allow me to do any alteration on that sp. all i can do is to call it.
I hope someone could help me on this one.
June 20, 2011 at 9:45 am
I'm not sure I understand exactly what the problem is.
Are you saying that, the stored procedure, when called, returns a recordset containing results, as well as a second recordset with a message, similar to something like:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sp_Test]
(
@ID INT,
@User VARCHAR(MAX)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @ID, @User
SELECT 'TestCode'
END
GO
If so, then no, you can't really do what you're looking to do. If you try to insert the resultset from that stored procedure into a temp table, like so:
DECLARE @Table TABLE
(
Test1 VARCHAR(MAX),
Test2 VARCHAR(MAX)
)
INSERT INTO @Table
EXEC sp_Test 1, 'testid111'
SELECT * FROM @Table
You'll get an error, since the two recordsets don't have the same definition.
What you probably would have to do is get them to change the stored procedure to produce an output variable as well as a recordset, and return the message in the output variable, like so:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [sp_Test]
(
@ID INT,
@User VARCHAR(MAX),
@Message VARCHAR(MAX) OUTPUT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @ID, @User
SET @Message = 'Success!'
END
GO
Then what you can do is get them as so:
DECLARE @Table TABLE
(
Test1 VARCHAR(MAX),
Test2 VARCHAR(MAX)
)
DECLARE @Output VARCHAR(MAX)
INSERT INTO @Table
EXEC sp_Test 1, 'testid111', @Output OUTPUT
SELECT *, @Output FROM @Table
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply