Diferencia entre fechas (sin fines de semana)

Opción 1

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkDayDiff]’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function [dbo].[WorkDayDiff]
GO

CREATE FUNCTION WorkDayDiff(@BegDate datetime,@EndDate datetime)
RETURNS int
BEGIN

DECLARE @NumWeek int
SET @BegDate = IsNull(@BegDate,0)
SET @EndDate = IsNull(@EndDate,0)
SET @NumWeek = DateDiff(ww,@BegDate,@EndDate)
RETURN(datediff(dd,@BegDate,@EndDate)-(@NumWeek*2))
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

———————-

Opción 2

DECLARE @t TABLE(id INT,StartDt DATETIME,EndDt DATETIME);
INSERT INTO @t VALUES(1,'20100728 13:00', '20100729 10:00');
INSERT INTO @t VALUES(2,'20100728 13:00', '20100729 17:00');

DECLARE @Hrs TABLE(Id INT,DayStart DATETIME, DayEnd DATETIME);
INSERT INTO @Hrs VALUES(1,'19000101 07:00','19000101 17:30');
INSERT INTO @Hrs VALUES(2,'19000101 08:00','19000101 17:00');

SELECT 
 CASE 
  WHEN DATEADD(DAY,DATEDIFF(DAY,0,StartDt),0) < DATEADD(DAY,DATEDIFF(DAY,0,EndDt),0)
  THEN 
   DATEDIFF(MINUTE,DATEADD(DAY,-DATEDIFF(DAY,0,StartDt),StartDt),DayEnd) +
   (DATEDIFF(DAY,StartDt,EndDt)-1) * 1440 +
   DATEDIFF(MINUTE,[DayStart],DATEADD(DAY,-DATEDIFF(DAY,0,EndDt),EndDt))
 ELSE
  DATEDIFF(MINUTE,StartDt,EndDt)
 END / 60.0
FROM @t t
INNER JOIN @Hrs hrs ON t.Id = hrs.Id
Anuncios

SQL: Procedimiento almacenado con parámetros opcionales en búsquedas

Esta forma de crear un procedimiento almacenado con parámetros opcionales me funciono muy bien:

CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT
*
FROM
TestTable
WHERE
((@Param1 IS NULL) OR (col1 = @Param1)) AND
((@Param2 IS NULL) OR (col2 = @Param2)) AND
((@Param3 IS NULL) OR (col3 = @Param3))

Para mayor información visiten el URL del creador del Post:

  1. http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx

También, pueden revisar este otro método que puede serles de utilidad:

  1. http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure
  2. http://hardtyco.blogspot.com/2011/02/procedimiento-almacenado-con-parametros.html

SQL: Buscar objetos en la Base de datos

CREATE PROCEDURE [dbo].[FindTextInDatabase]
    -- Add the parameters for the stored procedure here
@text varchar(200)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
declare @newString varchar(205)
Set @newString = '%' + @text + '%'
    -- Insert statements for procedure here
    select name from sysobjects where id in 
(select id from syscomments where text like @newString)
order by name
END

SQL: Busqueda por nombre y contenido de un SP

Por nombre  o parametros del SP:

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100)
AS
SET @StringToSearch = ‘%’ +@StringToSearch + ‘%’
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = ‘P’
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
GO

Por contenido de un SP:

CREATE PROCEDURE Find_SPName_With_Text
@StringToSearch varchar(100)
AS
SET @StringToSearch = ‘%’ + @StringToSearch + ‘%’
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = ‘P’
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
GO