March 19, 2010 at 6:07 am
I have a store procedure that contains a subquery in the WHERE clause, and returns the results that are ok, but returns another table that has a field with the value 0 and the name of the field is "Return Value".
Why does the store procedure return the second result and how can I do to avoid it.
Thanks a lot
March 19, 2010 at 6:24 am
The second result set is the return code of the stored procedure, and is probably manufactured by whatever data access technology you are using to connect to SQL Server.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 7:06 am
and how can I do to avoid the second result ?
March 19, 2010 at 7:14 am
sistemas_casinomnes (3/19/2010)
and how can I do to avoid the second result ?
It depends on exactly what you are using to connect to SQL Server.
The more you tell us about it, the more likely we are to be able to help.
Returning the return code as a second result set is not something SQL Server does normally.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 12:27 pm
Paul White NZ (3/19/2010)
sistemas_casinomnes (3/19/2010)
and how can I do to avoid the second result ?It depends on exactly what you are using to connect to SQL Server.
The more you tell us about it, the more likely we are to be able to help.
Returning the return code as a second result set is not something SQL Server does normally.
Please provide more information here. For starters, please provide the script. Then we can provide better more accurate answers to your situation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2010 at 1:42 pm
This is my store procedure that returns two tables as result. Thanks for your help
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PedidosSeleccionar]
@idmasterpedido int=Null,
@idsector int=Null,
@idclasificacion int=Null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [idpedido]
,[idmasterpedido]
,[idsector]
,[sector]
,[clavefabricante]
,[nro_parte]
,[producto]
,[idclasificacion]
,[clasificacion]
,[idsubclasificacion]
,[subclasificacion]
,[idunidad]
,[unidad]
,[idunidadempaque]
,[unidadempaque]
,[cantidad]
,[idusuario]
,[fechagrabacion]
FROM [GestionStock].[dbo].[Pedidos]
where ([idmasterpedido]=@idmasterpedido or @idmasterpedido is null)
and idpedido not in (SELECT idpedido
FROM dbo.OrdenCompra
where idpedido is not null)
and (idsector=@idsector or @idsector is null)
and (idclasificacion=@idclasificacion or @idclasificacion is null)
END
March 19, 2010 at 9:27 pm
If you run that procedure from SSMS, you will only get one result set.
The second result set is being generated by something between your application and SQL Server.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 12:04 am
sistemas_casinomnes (3/19/2010)
This is my store procedure that returns two tables as result. Thanks for your help
set ANSI_NULLS ONset QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PedidosSeleccionar]
@idmasterpedido int=Null,
@idsector int=Null,
@idclasificacion int=Null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [idpedido]
,[idmasterpedido]
,[idsector]
,[sector]
,[clavefabricante]
,[nro_parte]
,[producto]
,[idclasificacion]
,[clasificacion]
,[idsubclasificacion]
,[subclasificacion]
,[idunidad]
,[unidad]
,[idunidadempaque]
,[unidadempaque]
,[cantidad]
,[idusuario]
,[fechagrabacion]
FROM [GestionStock].[dbo].[Pedidos]
where ([idmasterpedido]=@idmasterpedido or @idmasterpedido is null)
and idpedido not in (SELECT idpedido
FROM dbo.OrdenCompra
where idpedido is not null)
and (idsector=@idsector or @idsector is null)
and (idclasificacion=@idclasificacion or @idclasificacion is null)
END
This query can only return one result set since it is a single select. You do have a subquery attached to it, but that will filter your results for the outer query.
Please provide the additional query that is creating the second result set.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 20, 2010 at 1:30 am
CirquedeSQLeil (3/20/2010)
Please provide the additional query that is creating the second result set.
The second result just contains the return code from the procedure. I believe this to be a driver or data-access-layer thing, possibly not directly visible to the OP. I have come across such things before.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply