Friday, March 23, 2012
Name part search. Stumped...
I'm having a problem making a select statement to return the
correct results.
Here is an example of what I'm trying to do. Two tables:
@.TblNameParts which contains the master list of all the name
parts and the MasterNameID which is a pointer to the master
record.
@.TblCriteria which is based on the parts of the name being
searched for.
I need a select statement which will return a list of the
valid MasterNameIDs. I know there is a simple solution
I'm just having a hard time finding it.
The logic is:
1) All @.TblCriteria.PartValue's must be in @.TblNameParts.PartValue
grouped by the @.TblNameParts.MasterNameID.
2) @.TblCriteria.PartValue's can be in any order in @.TblNameParts.
3) A @.TblNameParts.PartValue can only be used once for a
@.TblCriteria.PartValue. This is where I'm running into
trouble. You should be able to glean what I mean by the
example's correct results below.
I really appreciate any assistance you can provide, thanks.
*/
SET NOCOUNT ON
-- Main master name parts table.
DECLARE @.TblNameParts TABLE (
MasterNameID int,
PartIndex int,
PartValue varchar(50)
)
-- MasterNameID 10: 'Stacy Smith'
INSERT INTO @.TblNameParts VALUES (10, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (10, 1, 'Smith')
-- MasterNameID 15: 'John Doe'
INSERT INTO @.TblNameParts VALUES (15, 0, 'John')
INSERT INTO @.TblNameParts VALUES (15, 1, 'Doe')
-- MasterNameID 20: 'Stacy Ann Smith'
INSERT INTO @.TblNameParts VALUES (20, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (20, 1, 'Ann')
INSERT INTO @.TblNameParts VALUES (20, 2, 'Smith')
-- MasterNameID 25: 'Stacy Stacy'
INSERT INTO @.TblNameParts VALUES (25, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (25, 1, 'Stacy')
-- MasterNameID 30: 'Smith Stacy'
INSERT INTO @.TblNameParts VALUES (30, 0, 'Smith')
INSERT INTO @.TblNameParts VALUES (30, 1, 'Stacy')
-- Criteria to find a master name ID.
DECLARE @.TblCriteria TABLE (
CriteriaID int,
PartValue varchar(50)
)
-- Search 1 Criteria: 'John Doe'
INSERT INTO @.TblCriteria VALUES (0, 'John')
INSERT INTO @.TblCriteria VALUES (1, 'Doe')
-- Search 1 Correct Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 2 Criteria: 'Stacy'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
-- Search 2 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 3 Criteria: 'Stacy Smith'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
INSERT INTO @.TblCriteria VALUES (1, 'Smith')
-- Search 3 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 4 Criteria: 'Stacy Stacy'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
INSERT INTO @.TblCriteria VALUES (1, 'Stacy')
-- Search 4 Correct Result:
-- MasterNameID 25
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 25
SET NOCOUNT OFFGoogle for "relational division". There's a very comprehensive article on th
e
subject by Joe Celko.
ML
http://milambda.blogspot.com/
Wednesday, March 21, 2012
Naive question on UDFs
Is it a requirement of SQL server, that UDFs return values?
kdReturn is a non optional keyword so you have to give back something, but you
are forced to use it anyway :-)
So returning 0 and not using would be fine.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kd" <kd@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DC0C8797-CFEE-4B64-807E-EA22CF731A37@.microsoft.com...
> Hi,
> Is it a requirement of SQL server, that UDFs return values?
> kd|||kd
Are you talking about that you cannot pefrorm UPDATE/DELETE/INSERT to
underlaying tables?
"kd" <kd@.discussions.microsoft.com> wrote in message
news:DC0C8797-CFEE-4B64-807E-EA22CF731A37@.microsoft.com...
> Hi,
> Is it a requirement of SQL server, that UDFs return values?
> kd|||Since a UDF cannot perform Inserts Updates or Deletes, it would be pointles
s
to call a function that did a bunch of processing internally, that had no
external effect, and did not return anything to the caller. What would be
the point?
Having said that, I don;t think that SQL Server mandates that you actually
return anything...
"kd" wrote:
> Hi,
> Is it a requirement of SQL server, that UDFs return values?
> kd
Wednesday, March 7, 2012
my Store procedure spend TOO MANY TIME, why?
I have a sql that return about 35 rows
If I execute that sql in the analizer it spend 4 seconds (with
variables)
If I execute the same sql in a store procedure it spend 35 seconds
(with parameters)
The size of the variables or parameters are the same as the columns of the
tables so...
Why it can be?
This is the store procedure
CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
@.Campania int,
@.FechaDesde nchar(10),
@.FechaHasta nchar(10),
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2)
+ '-' + Left(@.FechaDesde, 2)
SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2)
+ '-' + Left(@.FechaHasta, 2)
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
-- DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <=
CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >=
CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
@.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
ORDER BY TGENERAL.PVP
END
RETURN @.@.ROWCOUNT
GORal,
First of all, you must change the name of your stored procedure.
Procedures named sp_* are treated differently by the query
processor. This name is used to indicate that there is a system
stored procedure in the master database available from all databases,
so the processor must first search the objects in master, find no
system procedure, then look for your procedure in your database.
If this doesn't solve the problem, consider adding WITH RECOMPILE
to the definition of the stored procedure. What you may be seeing is
the result of a cached plan for the stored procedure that is optimal
for one set of parameters and very bad for another. When you run
this query outside a procedure, the plan will not be cached, but
will be recompiled for the particular variable values.
If that still doesn't work, try declaring local variables for your
parameters and reassigning them inside the procedure:
...
declare @.Campania2 int,
@.FechaDesde2 nchar(10),
@.FechaHasta2 nchar(10),
@.Pais2 nchar(5),
@.Ciudad2 nchar(5),
@.Duracion2 int,
@.Precio2 int
select
@.Campania2 = @.Campania,
@.FechaDesde2 = @.FechaHasta,
..
This sometimes improves the choice of plan with or
without adding WITH RECOMPILE. In your case, it
might also be worthwhile to use local datetime variables for
the result of CONVERT(DATETIME, @.FechaHasta, 102),
CONVERT(DATETIME, @.FechaDesde, 102), and
CONVERT(DATETIME, GETDATE(), 102)
Queries this complicated with many parameters can be difficult
to optimize, particularly if the optimal query plan depends on
the particular parameter values.
Finally, I didn't look closely, but I don't quite understand the
purpose of
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
instead of just putting .Multimedias.IDtipoMultimedia = 0 into
the where clause before grouping. I'm also not sure why the
extra . before Multimedias.
Steve Kass
Drew University
Ral Martn wrote:
>Hi
> I have a sql that return about 35 rows
> If I execute that sql in the analizer it spend 4 seconds (with
>variables)
> If I execute the same sql in a store procedure it spend 35 seconds
>(with parameters)
>The size of the variables or parameters are the same as the columns of the
>tables so...
> Why it can be?
>This is the store procedure
>CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
> @.Campania int,
> @.FechaDesde nchar(10),
> @.FechaHasta nchar(10),
> @.Pais nchar(5),
> @.Ciudad nchar(5),
> @.Duracion int,
> @.Precio int
>AS
>
>DECLARE @.PrecioMin int, @.PrecioMax int
>SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2
)
>+ '-' + Left(@.FechaDesde, 2)
>SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2
)
>+ '-' + Left(@.FechaHasta, 2)
>DECLARE @.TablaAcrear varchar(2000)
>DECLARE @.STRINGINSERT varchar(4000)
>DECLARE @.STRINGadevolver varchar(1000)
>DECLARE @.NombreTablaTemporal nvarchar(250)
>BEGIN
>
> SET @.PrecioMin = 0
> SET @.PrecioMax = 999999
> IF @.Precio = 1
> BEGIN
> SET @.PrecioMin = 0
> SET @.PrecioMax = 300
> END
> ELSE IF @.Precio = 2
> BEGIN
> SET @.PrecioMin = 301
> SET @.PrecioMax = 600
> END
> ELSE IF @.Precio = 3
> BEGIN
> SET @.PrecioMin = 601
> SET @.PrecioMax = 900
> END
> ELSE IF @.Precio = 4
> BEGIN
> SET @.PrecioMin = 901
> SET @.PrecioMax = 1200
> END
> ELSE IF @.Precio = 5
> BEGIN
> SET @.PrecioMin = 1201
> SET @.PrecioMax = 999999
> END
>
> DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
>decimal(9,0),
> @.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
>@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
> DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
>@.IDduracion int
> DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
>
> set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
>int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
>decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
> exec (@.TablaAcrear)
>
> -- CIUDADES
>
> -- DECLARE Cursor1 CURSOR FOR
>
> SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
>PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
>TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
>TGENERAL.IDduracion as IDduracion,
> TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
>Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
>Multimedias.alto as alto
> FROM (
> SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
>TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
> MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
>AS Ciudad
> FROM (
> SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
>MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
> MIX.IDduracion, MIX.nombreIDPVSRC AS
>nombreIDPVSRC, MIX.nombre AS Ciudad
> FROM (
> SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
>PVSRC.IDvuelo,
> PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
>Ciudades.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
>INNER JOIN
> Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
> Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
> CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
> WHERE
> (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
>(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AN
D
>(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
>(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
>(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
>@.Campania)
> AND (Ciudades.IDCIUDAD = @.Ciudad)
> GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
> PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
> )
> MIX
> INNER JOIN
> (
> SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
>PreciosXFechasPVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <=
>CONVERT(DATETIME, @.FechaHasta, 102)) AND
> (PreciosXFechasPVSRC.fechaBaja >=
>CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
>@.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> )
> TABLAPVSRC
> ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
>TABLAPVSRC.nombre
> GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
>MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
> ORDER BY MIX.PVP
> )
> MIXG
> INNER JOIN
> (
> SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
>PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
> PVSRC.IDpaquete
> FROM PreciosXFechasPVSRC INNER JOIN
> PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
>102)) AND
> (PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
>102)) AND
> (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
>PVSRC.IDpaquete
> )
> TABLAPVP
> ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
> )
> TGENERAL
> INNER JOIN PVSRC
> ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
> INNER JOIN
> .Multimedias ON .PVSRC.IDmultimedia =
>.Multimedias.IDmultimedia
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
>TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
>Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> ORDER BY TGENERAL.PVP
>
> END
>
>RETURN @.@.ROWCOUNT
>GO
>
>|||Thanks a lot Steve but with those changes it is worse than before.
I modified de "sp_" , added WITH RECOMPILE, added local parameters and I
modified the query
at the end with
WHERE (.Multimedias.IDtipoMultimedia = 0)
in stead of
HAVING (.Multimedias.IDtipoMultimedia = 0)
but now it spend 80 seconds so if you have more ideas...
now the store procedure is:
CREATE PROCEDURE BuscardorOfertasmejorCIUDADES
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
DROP TABLE BORRAR4
SET @.NombreTablaTemporal = '_tempBusc' + LTRIM(RTRIM(@.Pais)) +
LTRIM(RTRIM(@.Ciudad)) + LTRIM(RTRIM(STR(@.Campania))) +
LTRIM(RTRIM(@.FechaDesde)) + LTRIM(RTRIM(@.FechaHasta)) +
LTRIM(RTRIM(STR(@.Precio)))
SET @.NombreTablaTemporal = LTRIM(RTRIM( replace(@.NombreTablaTemporal, '-',
'_') ))
SET @.NombreTablaTemporal = 'BORRAR4'
if NOT exists (select * from sysobjects where name = @.NombreTablaTemporal )
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGINSERT = 'INSERT INTO ' + @.NombreTablaTemporal + ' (
IDPVSRC, nombreIDPVSRC , CIUDAD, IDPVP, PVP, URLMULTIMEDIA , Ancho, Alto) '
SET @.STRINGINSERT = @.STRINGINSERT + 'VALUES (' +
RTRIM(LTRIM(STR(@.IDPVSRC))) + ', '
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNombre)) + '" ,'
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNomCiudad)) + '" , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(STR(@.IDPVP)))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.PVP))) + ' , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(@.IDPVSRCUrl))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAncho)))
+ ' , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAlto)))
SET @.STRINGINSERT = @.STRINGINSERT + ' ) '
-- @.STRINGINSERT
EXEC (@.STRINGINSERT)
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
END
CLOSE Cursor1
DEALLOCATE Cursor1
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
RETURN @.@.ROWCOUNT
END
ELSE
BEGIN
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
END
GO
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:ejJalkYlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Ral,
> First of all, you must change the name of your stored procedure.
> Procedures named sp_* are treated differently by the query
> processor. This name is used to indicate that there is a system
> stored procedure in the master database available from all databases,
> so the processor must first search the objects in master, find no
> system procedure, then look for your procedure in your database.
> If this doesn't solve the problem, consider adding WITH RECOMPILE
> to the definition of the stored procedure. What you may be seeing is
> the result of a cached plan for the stored procedure that is optimal
> for one set of parameters and very bad for another. When you run
> this query outside a procedure, the plan will not be cached, but
> will be recompiled for the particular variable values.
> If that still doesn't work, try declaring local variables for your
> parameters and reassigning them inside the procedure:
> ...
> declare @.Campania2 int,
> @.FechaDesde2 nchar(10),
> @.FechaHasta2 nchar(10),
> @.Pais2 nchar(5),
> @.Ciudad2 nchar(5),
> @.Duracion2 int,
> @.Precio2 int
> select
> @.Campania2 = @.Campania,
> @.FechaDesde2 = @.FechaHasta,
> ...
> This sometimes improves the choice of plan with or
> without adding WITH RECOMPILE. In your case, it
> might also be worthwhile to use local datetime variables for
> the result of CONVERT(DATETIME, @.FechaHasta, 102),
> CONVERT(DATETIME, @.FechaDesde, 102), and
> CONVERT(DATETIME, GETDATE(), 102)
> Queries this complicated with many parameters can be difficult
> to optimize, particularly if the optimal query plan depends on
> the particular parameter values.
> Finally, I didn't look closely, but I don't quite understand the
> purpose of
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
> TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
> Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> instead of just putting .Multimedias.IDtipoMultimedia = 0 into
> the where clause before grouping. I'm also not sure why the
> extra . before Multimedias.
>
> Steve Kass
> Drew University
>
> Ral Martn wrote:
>
seconds
the
2)
2)
(IDPVSRC
smallint)'
,
MIXG.Ciudad
IDPVSRC,
AS
PreciosXFechasPVSRC.IDPVSRC
AND
MIX.IDvuelo,
IDPVP,
@.FechaHasta,
TABLAPVP.PVP|||Sorry, the correct store procedure is like that
(without cursors)
CREATE PROCEDURE BuscardorOfertasmejorCIUDADESsincursor
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
RETURN @.@.ROWCOUNT
END
GO
Monday, February 20, 2012
my output parameter is being treated as an input parameter...why
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems...
ASP Code...
<asp:SqlDataSourceID="SqlDS_Form"runat="server"ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>"SelectCommand="PTN_sp_getFormDD"SelectCommandType="StoredProcedure"OldValuesParameterFormatString="original_{0}"UpdateCommand="PTN_sp_Form_Update"UpdateCommandType="StoredProcedure"OnUpdated="SqlDS_Form_Updated"OnUpdating="SqlDS_Form_Updating"DeleteCommand="PTN_sp_Form_Del"DeleteCommandType="StoredProcedure"OnDeleting="SqlDS_Form_Updating"OnDeleted="SqlDS_Form_Deleted">
<UpdateParameters><asp:ControlParameterControlID="GridView1"Name="DescID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="GridView1"Name="FormNum"PropertyName="SelectedValue"Type="String"/><asp:ParameterName="original_FormNum"Type="String"/><asp:ParameterDirection="InputOutput"size="25"Name="RetVal"Type="String"/></UpdateParameters>
<DeleteParameters>
<asp:ParameterName="original_FormNum"Type="String"/>
<asp:ParameterDirection="InputOutput"Size="1"Name="NewRetVal"Type="Int16"/>
</DeleteParameters>
</asp:SqlDataSource>
Code Behind:
protectedvoid SqlDS_Form_Deleted(object sender,SqlDataSourceStatusEventArgs e)
{
if (e.Exception ==null)
{ string strRetVal = (String)e.Command.Parameters["@.NewRetVal"].Value.ToString();
.....................
Stored Procedure:
CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (@.original_FormNumnvarchar(20), @.NewRetValINT OUTPUT )ASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @.stoptransvarchar(5), @.AvailFoundint, @.AssignedFoundintSet @.stoptrans ='NO'/* ------- Search PART #1 ---------------- */SET @.AvailFound = (SELECTCOUNT(*)FROM dbo.AvailableNumberWHERE dbo.AvailableNumber.FormNum = @.original_FormNum )SET @.AssignedFound = (SELECTCOUNT(*)FROM dbo.AssignedNumberWHERE dbo.AssignedNumber.FormNum=@.original_FormNum )IF @.AvailFound > 0OR @.AssignedFound > 0/* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/--This means the delete can't happen.........BEGINIF @.AssignedFound > 0AND @.AvailFound = 0BEGINSET @.NewRetVal = 1ENDIF @.AssignedFound > 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 2ENDIF @.AssignedFound = 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 3ENDENDELSEBEGINDELETE FROM dbo.FormWHERE dbo.Form.FormNum=@.original_FormNumSET @.NewRetVal = 0--Successful deletionENDGO
------------------
When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
Not sure whether it will help, but try to set the Direction of NewRetVal to Output instead of IntputOutput:
<asp:ParameterDirection="Output"Size="1"Name="NewRetVal"Type="Int16"/>
You may also take a look at this article:
Input and Output Parameters, and Return Values
|||It could also be because the type doesn't match. A SQL int is 32-bits, and you've specified a 16-bit int.
my money cast no longer works in sql 2005
In a SQL 2000 stored proc I have the following statement to return a formatted price without the decimal place and cents
SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money(12,2)),1)
This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid attributes specified for type 'money')
By reading this forum I can see that I can use this instead:
select '$' + parsename(convert(varchar,convert(money,@.price),1),2)
I'm wondering why it doesn't work any more in SQL 2005 - are these differences documented anywhere ?
thanks
Bruce
It is because you are specifying precision and scale for money (money(12,2)) which is incorrect/invalid. Older versions allowed such syntaxes due to bug in parser and this has been corrected now. Do:
SELECT @.strPrice = '$' + convert(varchar(12),cast(@.price as money),1)
|||> This fails syntax checking in SQL 2005 (CAST or CONVERT: invalid> attributes specified for type 'money') Where is there any documentation that states that MONEY has precision or scale? The SQL Server 2005 error message is correct. SQL Server 2000 was a little bit more lenient and just ignored the attributes, but don't blame the tool that you misused. :-) I would MUCH RATHER have this kind of thing break on me, than just ignore my specifications and lull me into believing that it is working correctly, when it is in fact not. DECLARE @.PRICE MONEY SET @.Price = 476.2354 SELECT CAST(@.Price AS MONEY(12,2)) Now, try instead: SELECT CONVERT(MONEY(12,2), @.Price) Hey, they got the error message right for CONVERT, but not for CAST. Server: Msg 291, Level 16, State 1, Line 3 CAST or CONVERT: invalid attributes specified for type 'money'
> By reading this forum I can see that I can use this instead:
> select '$' + parsename(convert(varchar,convert(money,@.price),1),2) Why do you need to convert to money? What is the original data type? If it is decimal or numeric, then the following will work fine: SELECT '$' + RTRIM(CONVERT(DECIMAL(12,2), @.price)) Or, just SELECT @.price And let the client/presentation tier add a dollar sign and round to the correct number of decimal places. I've actually never ended up using MONEY or SMALLMONEY in any database application I've built. Several reasons are listed here: http://www.aspfaq.com/2503
> I'm wondering why it doesn't work any more in SQL 2005 - are these
> differences documented anywhere ? No, that's the problem with having a loose syntax checker in previous versions, that allowed invalid things to work. I'm not even sure that Microsoft is aware of all of these obscure things that just happened to work in previous versions. If Microsoft were to document all of the undocumented behaviors that change, where should they put it? How would people find it? Who is going to tell them about all of the undocumented things that we all know about, and should be documented? There are some other examples, though I know they are aware of these, e.g. CREATE VIEW ... AS SELECT TOP 100 PERCENT ... ... ORDER BY This still passes the syntax checker, and you can still create the view, but now the lazy "SELECT * FROM viewname" no longer guarantees . Since the behavior people relied on was undocumented,