Utilizar solo ciertos parámetros en un SP
Me llamo Jorge Gonzalez y tengo poco tiempo programando con BD de Sql Server.
Tengo en un proyecto de VB 6.0 varios catálogos y estoy preparando un formulario de búsqueda que pueda ser utilizado por todos estos
catálogos. Esta forma contiene varios combos que despliegan una lista de los campos de la tabla correspondiente y al lado de cada combo
un textbox en el que se captura el valor a buscar en el campo escogido en el combo. Puesto que el usuario solo va escoger ciertos campos
en su criterio de búsqueda, no se llegan a ocupar todos los parámetros que declare en el Stored Procedure de búsqueda para esta tabla, y si
por ejemplo el usuario no escogió el campo RFC en su criterio de búsqueda me envía un error que dice:
"El procedimiento 'SpCliente_XTodo' precisa el parámetro '@RFC' que no se ha especificado"
Este es el código de VB que inserta los datos de un solo combo en los parámetros de SQL
Dim qdfRes As New ADODB.Recordset
With deSioJorge.Commands("SPCliente_XTodo")
.Parameters("@" & cboBus1.Text).Value = txtBus1
Set qdfRes = .Execute
End With
Este es el Stored Procedure
CREATE PROC SpCliente_XTodo
@Cliente varchar(50),
@RFC varchar(20),
@Contactos varchar(100),
@Telefonos varchar(100),
@Faxs varchar(100),
@Emails varchar(100),
@Dir1 varchar(100),
@Dir2 varchar(100),
@Poblacion varchar(50),
@Estado varchar(50),
@Pais varchar(50),
@CP varchar(20)
AS
Begin
Select *
From Cliente
where Cliente=@Cliente
and RFC=@RFC
and Contactos=@Contactos
and Telefonos=@Telefonos
and Faxs=@Faxs
and Emails=@Emails
and Dir1=@Dir1
and Dir2=@Dir2
and Poblacion=@Poblacion
and Estado=@Estado
and Pais=@Pais
and CP=@CP
END
Go
Donde cboBus1 es el combo que escoge el campo en el que hay que buscar
txtBus1 es el textbox en el que se teclea el valor a buscar
y qdfRes es el recordset en donde voy a almacenar los resultados.
¿Cómo hago para usar solo ciertos parámetros de entre todos los que declare en el Stored Procedure? ¿Hay otra forma mejor de
resolver mi caso?
Tengo en un proyecto de VB 6.0 varios catálogos y estoy preparando un formulario de búsqueda que pueda ser utilizado por todos estos
catálogos. Esta forma contiene varios combos que despliegan una lista de los campos de la tabla correspondiente y al lado de cada combo
un textbox en el que se captura el valor a buscar en el campo escogido en el combo. Puesto que el usuario solo va escoger ciertos campos
en su criterio de búsqueda, no se llegan a ocupar todos los parámetros que declare en el Stored Procedure de búsqueda para esta tabla, y si
por ejemplo el usuario no escogió el campo RFC en su criterio de búsqueda me envía un error que dice:
"El procedimiento 'SpCliente_XTodo' precisa el parámetro '@RFC' que no se ha especificado"
Este es el código de VB que inserta los datos de un solo combo en los parámetros de SQL
Dim qdfRes As New ADODB.Recordset
With deSioJorge.Commands("SPCliente_XTodo")
.Parameters("@" & cboBus1.Text).Value = txtBus1
Set qdfRes = .Execute
End With
Este es el Stored Procedure
CREATE PROC SpCliente_XTodo
@Cliente varchar(50),
@RFC varchar(20),
@Contactos varchar(100),
@Telefonos varchar(100),
@Faxs varchar(100),
@Emails varchar(100),
@Dir1 varchar(100),
@Dir2 varchar(100),
@Poblacion varchar(50),
@Estado varchar(50),
@Pais varchar(50),
@CP varchar(20)
AS
Begin
Select *
From Cliente
where Cliente=@Cliente
and RFC=@RFC
and Contactos=@Contactos
and Telefonos=@Telefonos
and Faxs=@Faxs
and Emails=@Emails
and Dir1=@Dir1
and Dir2=@Dir2
and Poblacion=@Poblacion
and Estado=@Estado
and Pais=@Pais
and CP=@CP
END
Go
Donde cboBus1 es el combo que escoge el campo en el que hay que buscar
txtBus1 es el textbox en el que se teclea el valor a buscar
y qdfRes es el recordset en donde voy a almacenar los resultados.
¿Cómo hago para usar solo ciertos parámetros de entre todos los que declare en el Stored Procedure? ¿Hay otra forma mejor de
resolver mi caso?
Respuesta de dcanete
1
Utiliza parámetros opcionales en los SP's. Si no se pasan estos parámetros opcionales no pasa nada, en otro caso, salta un error. Un parámetro es opcional si tiene un valor por defecto.
Nosotros teníamos el mismo problema y al final lo solucionamos así: ponemos un valor por defecto que no se va a dar en explotación, si viene ese valor, ignoramos el parámetro. Te pego un SP nuestro por si te sirve de ejemplo:
CREATE PROCEDURE dbo.spGetAreas
@idArea INT = -999999999,
@dbNombre DescripcionBreve = '////',
@dlDescripcion DescripcionLarga = '////',
@nEjercicio NUMERIC(10) = -999999999,
@idUsuario INT = -999999999,
@idOficinaContable INT = -999999999,
@lConBloqueo BIT = 0
AS
if @idUsuario = -999999999
BEGIN
DECLARE @strSQL VARCHAR(8000)
SET @strSQL = '
SELECT Areas.idArea, Areas.dbNombre, Areas.dlDescripcion, Areas.nEjercicio,Areas.idOficinaContable,OficinasContables.dcNombre as dcOficinaContable
FROM Areas left outer join OficinasContables on Areas.idOficinaContable = OficinasContables.idOficinaContable
'
SET @strSQL = @strSQL + ' WHERE Areas.lBorrado = 0'
IF(@idArea IS NULL)
SET @strSQL = @strSQL + ' AND Areas.idArea IS NULL '
IF(@idArea <> -999999999)
SET @strSQL = @strSQL + ' AND Areas.idArea = ' + CAST(@idArea AS VARCHAR(16))
IF(@idOficinaContable IS NULL)
SET @strSQL = @strSQL + ' AND Areas.idOficinaContable IS NULL '
IF(@idOficinaContable <> -999999999)
SET @strSQL = @strSQL + ' AND Areas.idOficinaContable = ' + CAST(@idOficinaContable AS VARCHAR(16))
IF(@nEjercicio IS NULL)
SET @strSQL = @strSQL + ' AND Areas.nEjercicio IS NULL '
IF(@nEjercicio <> -999999999)
SET @strSQL = @strSQL + ' AND Areas.nEjercicio = ' + CAST(@nEjercicio AS VARCHAR(16))
IF(@dbNombre <> '////')
SET @strSQL = @strSQL + ' AND Areas.dbNombre = ''' + dbo.rep(@dbNombre) + ''''
EXEC(@strSQL)
END
else
select distinct Areas.idArea, Areas.dbNombre , case when AreasUsuario.idAreaUsuario is null then 0 else 1 end asignado,Areas.idOficinaContable,OficinasContables.dcNombre as dcOficinaContable
FROM Areas
INNER JOIN OficinasContables ON Areas.idOficinaContable = OficinasContables.idOficinaContable
INNER JOIN Usuarios ON OficinasContables.idOficinaContable = Usuarios.idOficinaContable
LEFT OUTER JOIN AreasUsuario ON Areas.idArea = AreasUsuario.idArea AND Usuarios.idUsuario = AreasUsuario.idUsuario
where areas.lborrado=0 and areas.nejercicio = @nejercicio and Usuarios.idusuario = @idUsuario
GO
Nosotros teníamos el mismo problema y al final lo solucionamos así: ponemos un valor por defecto que no se va a dar en explotación, si viene ese valor, ignoramos el parámetro. Te pego un SP nuestro por si te sirve de ejemplo:
CREATE PROCEDURE dbo.spGetAreas
@idArea INT = -999999999,
@dbNombre DescripcionBreve = '////',
@dlDescripcion DescripcionLarga = '////',
@nEjercicio NUMERIC(10) = -999999999,
@idUsuario INT = -999999999,
@idOficinaContable INT = -999999999,
@lConBloqueo BIT = 0
AS
if @idUsuario = -999999999
BEGIN
DECLARE @strSQL VARCHAR(8000)
SET @strSQL = '
SELECT Areas.idArea, Areas.dbNombre, Areas.dlDescripcion, Areas.nEjercicio,Areas.idOficinaContable,OficinasContables.dcNombre as dcOficinaContable
FROM Areas left outer join OficinasContables on Areas.idOficinaContable = OficinasContables.idOficinaContable
'
SET @strSQL = @strSQL + ' WHERE Areas.lBorrado = 0'
IF(@idArea IS NULL)
SET @strSQL = @strSQL + ' AND Areas.idArea IS NULL '
IF(@idArea <> -999999999)
SET @strSQL = @strSQL + ' AND Areas.idArea = ' + CAST(@idArea AS VARCHAR(16))
IF(@idOficinaContable IS NULL)
SET @strSQL = @strSQL + ' AND Areas.idOficinaContable IS NULL '
IF(@idOficinaContable <> -999999999)
SET @strSQL = @strSQL + ' AND Areas.idOficinaContable = ' + CAST(@idOficinaContable AS VARCHAR(16))
IF(@nEjercicio IS NULL)
SET @strSQL = @strSQL + ' AND Areas.nEjercicio IS NULL '
IF(@nEjercicio <> -999999999)
SET @strSQL = @strSQL + ' AND Areas.nEjercicio = ' + CAST(@nEjercicio AS VARCHAR(16))
IF(@dbNombre <> '////')
SET @strSQL = @strSQL + ' AND Areas.dbNombre = ''' + dbo.rep(@dbNombre) + ''''
EXEC(@strSQL)
END
else
select distinct Areas.idArea, Areas.dbNombre , case when AreasUsuario.idAreaUsuario is null then 0 else 1 end asignado,Areas.idOficinaContable,OficinasContables.dcNombre as dcOficinaContable
FROM Areas
INNER JOIN OficinasContables ON Areas.idOficinaContable = OficinasContables.idOficinaContable
INNER JOIN Usuarios ON OficinasContables.idOficinaContable = Usuarios.idOficinaContable
LEFT OUTER JOIN AreasUsuario ON Areas.idArea = AreasUsuario.idArea AND Usuarios.idUsuario = AreasUsuario.idUsuario
where areas.lborrado=0 and areas.nejercicio = @nejercicio and Usuarios.idusuario = @idUsuario
GO
Gracias por contestarme, tu respuesta me sirvió mucho. Inmediatamente después que me contestaste te envíe la respuesta, pero creo que hubo algún error en el envío. Me mandaron un mail que decía que tenia preguntas que no había puntuado, pero yo no sabia. Ahora que abrí mis preguntas en espera me doy cuenta que aquí todavía estaba pendiente. Disculpa las molestias.
