condicional en SP

[maralbust]

hola a todos, tengo un gran drama con un gran SP, lo que pasa es que tengo un SP bastante grande y dentro de este son obligatorios 3 campos @strCasino Char(8), @strFamilia Char(8), @IdDefinicionPedido, pero me estan pidiendo que @strFamilia Char(8), sea optativo, como puedo hacer:

If @strFamilia <> "" Then
agrego la condicion where
end if

si necesitan ver el SP me dicen.

Saludos

[Andres95]

Código:

Create procedure miproceso(
    ...
    @strCasino char(8),
    @IdDefinicionPedido tipodedato,	
    @strFamilia char(8) = ''
AS
 ....

[maralbust]

perdon, pero la verdad no se nada de SP, podrian explicarme que es eso?



saludos

[Andres95]

significa que si le asignas un valor por default a tu parametro del SP entonces se convierte en opcional, ya que cuando no se lo envies tomara el valor que le asignaste en la creacion del SP...

por ejemplo, se podria llamar al sp de la siguiente forma....(suponiendo que el id es entero)

execute miproceso 'par1', 1, 'fam'

ó

execute miproceso 'par1', 1

en esta seguna llamada el tercer parametro tomara el valor de cadena vacía '', ya que no se le esta enviando nada como tercer parametro...

[maralbust]

dejame ver si funciona, lo que pasa es que despues en el select que esta dentro del SP, usa este parametro como clausula de WHERE, entonces la idea es que si va vacio que no ponga esa linea del where para que me liste todos los productos


saludos

[Andres95]

ok, de hecho se podria hacer algo como esto:


where @strFamilia = case when @strFamilia = '' Then @strFamilia Else campofamilia End

y ya no requeririas de un where dinamico...osea agregando o no filtros..

con el case, cuando @strFamilia sea igual a vacio '' entonces no se aplicara filtro, pero cuando sea diferente de vacio se comparará @strFamilia contra el campo a familia, filtrando solo aquellos donde el parametro coincida con el campo...

como ves??

[Andres95]

Te anexo un ejemplillo, que utiliza la bd pubs que viene de ejemplo en SQL.
espero te sirva...
un saludo

Código:

USE PUBS
GO
-- Elimina si existe el procedimiento
If 0 < (Select count(1) From dbo.SysObjects where Id = Object_Id(N'[dbo].[SelectStores]') and ObjectProperty(Id, N'IsProcedure') = 1)
Drop Procedure [dbo].[SelectStores]
Go

-- Genera stored procedure
Create procedure dbo.SelectStores
   @stor_id char(4),
   @city    varchar(20),
   @state   char(2),
   @zip     char(5)
As

Select * 
From   stores 
Where  @stor_id = (case when @stor_id = '' Then @stor_id Else stor_id End) And
       @city    = (case when @city    = '' Then @city    Else city    End) And
       @state   = (case when @state   = '' Then @state   Else state   End) And
       @zip     = (case when @zip     = '' Then @zip     Else zip     End) 
GO
-------------------------------------------------------------------
-- Ejemplos de llamadas.
-------------------------------------------------------------------
Execute dbo.SelectStores '','','',''            -- No se aplicará ningun filtro
Execute dbo.SelectStores '6380','','',''        -- Se filtrará por stor_id
Execute dbo.SelectStores '','Seattle','',''     -- Se filtrará por city
Execute dbo.SelectStores '','','WA',''          -- Se filtrará por State
Execute dbo.SelectStores '','','','98056'       -- Se filtrará por Zip
Execute dbo.SelectStores '','Seattle','WA',''   -- Se filtrará por City and State  

-- y asi sucesivamente, solo se filtraran por los parametros que tengan valor.

[maralbust]

'AND DetDefPed.CodFamilia = @pstrFamilia ' +

esa es la condicion del where, tonces quedaria

'AND DetDefPed.CodFamilia = @strFamilia = case when @strFamilia = '' Then @strFamilia Else campofamilia End

saludos

[Andres95]

esa cadena la estas armando dentro del SP ?? es sql dinamico???
con el ejemplo que te envíe podras observar que no se requiere sql dinamico...
como ves??

[maralbust]

sorry por haber dejado botado el post, pero me pidieron hacer otra cosa y dejar esto de lado, pero ya lo retomo.

esto esta dentro del SP, pero este SP es medio complicado, y la verdad me la esta ganando.

trate de implementer el codigo que me dio andre pero no funco :p


saludos

[maralbust]

me podrian explicar por que esto

Código:

CREATE PROCEDURE dbo.spGetProductosPedNormal @strCasino Char(8),  @strFamilia Char(8), @IdDefinicionPedido int

ok ahí define los datos de entrada cierto?

Código:

SET @strSQLParam = N'@pIdDefinicionPedido INT, @pstrCasino CHAR(8), @pstrFamilia Char(8), @pdpFecini DATETIME, @pdpFecfin DATETIME, @pstrCentralDeCompra CHAR(2)  '

aca no entiendo por que define nuevamente los datos?

Código:

'WHERE ' + 
	' DefPed.IdDefinicion = @pIdDefinicionPedido ' +
	'AND	DefPed.IdDefinicion  = DetDefPed.IdDefinicionPedido ' +
	'AND	DetDefPed.CodFamilia = @pstrFamilia ' +

y esta es la linea del where que deberia ser dinami dependiendo si @pstrFamilia viene con datos ( en realidad vendria con -1 )


ayuaaaaaaaaaaaaaaaaaaaaaaaa

[maralbust]

mmmm alguien me dice por que esto no funciona???

IF @strFamilia IS NOT NULL
SELECT @strSQL = @strSQL + ' AND DetDefPed.CodFamilia = @pstrFamilia ' +

a @pstrFamilia, he tratado pasarle valores como NULL, '', '-1' y no devuelve valores

saludos

[Andres95]

Código:

If @strFamilia IS NOT NULL
   SELECT @strSQL = @strSQL + ' AND DetDefPed.CodFamilia = ''' + @pstrFamilia  + ''''

[maralbust]

mmm nop, no funciona, ya que @pstrFamilia, no esta declarada, se declara solo cuando se crea la consulta, miren este es el SP

Código:

CREATE PROCEDURE dbo.spGetProductosPedNormal @strCasino Char(8),  @strFamilia Char(8), @IdDefinicionPedido int
AS

declare @dpFecini as datetime, @dpFecfin as datetime

DECLARE @strSQL AS NVARCHAR(4000)
DECLARE @strSQLParam AS NVARCHAR(4000)
DECLARE @intReglaNegocio AS NUMERIC
DECLARE @intRestriccionRutas AS NUMERIC
DECLARE @strCasinoCentralDeCompra AS CHAR(2)

SELECT @strCasinoCentralDeCompra = CentralDeCompra 
FROM s_Clientes
WHERE Codigo = @strCasino


SET @strSQL = N''
SET @strSQLParam = N'@pIdDefinicionPedido INT, @pstrCasino CHAR(8), @pstrFamilia Char(8), @pdpFecini DATETIME, @pdpFecfin DATETIME, @pstrCentralDeCompra CHAR(2)  '


/*************************************************************************************************************
  Obtiene regla de negocio asociada al Casino, la validación de la existencia de un RN
  es realizada en el sitio.
  Valores restricción de Rutas:
	1 = Con rutas
	2 = Sin rutas
	3 = Con y sin rutas
*************************************************************************************************************/

SELECT @intReglaNegocio = RN.rn_codigo, @intRestriccionRutas = RN.rn_tipo_ruta
FROM s_RNCasino RNC, s_ReglasDeNegocios RN
WHERE RNC.codigo_casino = @strCasino 
AND RN.rn_codigo = RNC.rn_codigo

select @dpFecini=b.periododesde, @dpFecfin=b.periodohasta
from s_definicionpedidos a, s_calendario b, s_DetalleDefinicionPedidos c
where a.centraldecompra=b.centralcompra
and a.yearmes=b.anomes
and a.semana=b.semana
and a.iddefinicion=c.IdDefinicionPedido
and c.codfamilia=b.categoria
and a.iddefinicion=@IdDefinicionPedido and c.codFamilia=@strFamilia


/*--- 1=>Con rutas / 3=>Con y sin rutas  ---*/
IF ( @intRestriccionRutas IN ( 1, 3 ) )
BEGIN
	-- Productos en Rutas del Casino
	SET @strSQL = N'SELECT DISTINCT Prod.Codigo, ' +
	'Prod.Descripcion, ' +
	'Prod.Cantidad, ' +
	'Prod.Unidad, ' +
	'ReDe.Ruta,  ' +
	'CONVERT( VARCHAR(10), ReDe.FechaDespacho, 103 ) AS FechaDespacho, ' +
	'ReDe.FechaDespacho AS DateOrderBy, ' +
	'CONVERT( VARCHAR(10), Prod.Vigencia, 103 ) AS ProdVigencia ' + 
	'FROM ' + 
	's_DefinicionPedidos AS DefPed, ' + 
	's_DetalleDefinicionPedidos AS DetDefPed, ' +
	'Productos_View AS Prod, ' +
	's_ProductosCentral AS ProdCC, ' +
	's_Recorrido_Despacho AS ReDe, ' +
	's_Recorrido_Despacho_Casino AS ReDeCa, ' +
	's_Recorrido_Productos AS ReDePro ' +
	'WHERE ' + 
	' DefPed.IdDefinicion = @pIdDefinicionPedido ' +
	'AND	DefPed.IdDefinicion  = DetDefPed.IdDefinicionPedido ' 
	IF @strFamilia  IS NOT NULL
	SELECT @strSQL = @strSQL + ' AND DetDefPed.CodFamilia =  @pstrFamilia ' +
	'AND	Prod.Categoria = DetDefPed.CodFamilia ' +
	'AND     ProdCC.pce_codpro = Prod.Codigo ' +
	'AND     ProdCC.pce_codcen = @pstrCentralDeCompra ' +
	'AND     Prod.ProdCantCentralDeCompra = ProdCC.pce_codcen ' +
	'AND	ReDePro.Producto = Prod.Codigo ' + 
	'AND	ISNULL( Prod.Vigencia, dbo.fcTransDate( GetDate() ) + 1 ) >= @pdpFecIni ' +
	'AND	ReDePro.Recorrido = ReDe.Ruta ' +
	'AND	ReDe.FechaDespacho BETWEEN @pdpFecIni AND @pdpFecFin ' +
	'AND	ReDeCa.Casino = @pstrCasino ' +
	'AND	ReDeCa.Ruta = ReDe.Ruta ' +
	'AND	ReDeCa.FechaDespacho = ReDe.FechaDespacho ' +

	-- Bloqueo de FAMILIAS --
	'AND Prod.Categoria  NOT IN ( ' +
	'SELECT RNF.Codigo_Familia ' + 
	'FROM s_RNCasino RNC, s_RNFamilias RNF ' + 
	'WHERE RNC.codigo_casino = @pstrCasino ' + 
	'AND RNF.rn_codigo = RNC.rn_codigo ' + 
	'AND RNF.rnf_pn = ''S'' )  ' +

	-- Bloqueo de PRODUCTOS --
	'AND Prod.Codigo  NOT IN ( ' +
	'SELECT RNP.Codigo_Producto ' + 
	'FROM s_RNCasino RNC, s_RNProductos RNP ' + 
	'WHERE RNC.codigo_casino = @pstrCasino ' + 
	'AND RNP.rn_codigo = RNC.rn_codigo ' + 
	'AND RNP.rnp_pn = ''S'' )  ' 

END /*---  IF ( @intRestriccionRutas IN ( 1, 3 ) ) ---*/