Need help on filters hard question. Easy to do with addhoc, but storeproc not sure. I also give another example but it takes to long to run.

Question

Post reply

Need help on filters hard question. Easy to do with addhoc, but storeproc not sure. I also give another example but it takes to long to run.

peerless

Ten Centuries

Points: 1054
More actions
September 22, 2009 at 10:45 am

#156244

delete

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 1

I must admit, i haven't read your post. But here's a proc I use to perform a similar function



CREATE PROCEDURE [dbo].[stproc_WebPlatform_ReturnDataRows]

  @TableName varchar(256),

  @offset integer = 0,

  @limit integer = 25,

  @orderby varchar(256) = '',

  @filterSQL varchar(512) = ''

AS

DECLARE @SQL nvarchar(MAX)

-- work out if we need a default order

IF ISNULL(@orderby, '') = ''

BEGIN

  -- first - do we have an identity for this table?

  SELECT @orderby = name

    FROM [sys].[columns] WITH (NOLOCK)

   WHERE [object_id] = OBJECT_ID(@TableName)

     AND COLUMNPROPERTY(OBJECT_ID, name, 'IsIdentity') = 1

  -- if we're still NULL, just get the first column

  IF ISNULL(@orderby, '') = ''

  BEGIN

    SELECT TOP 1 @orderby = name

      FROM [sys].[columns] WITH (NOLOCK)

     WHERE [object_id] = OBJECT_ID(@TableName)

     ORDER BY [column_id]

  END

END

-- work out if we need any filtering SQL

IF ISNULL(@filterSQL, '')  ''

BEGIN

  SET @filterSQL = ' WHERE ' + @filterSQL

END

-- Build the statement

SET @SQL =

'SELECT * FROM (SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER ( ORDER BY ' + @orderby + ') as __RN

    FROM ' + @TableName + ' WITH (NOLOCK) ' + @filterSQL + ' ORDER BY ' + @orderby + ') tbl2

   WHERE __RN BETWEEN @RowNumberOffset and @RowNumberLimit'

-- and run it

EXECUTE sp_executesql @stmt = @SQL,

                      @params = N'@RowNumberOffset int, @RowNumberLimit int',

                      @RowNumberOffset = @offset,

                      @RowNumberLimit = @limit

GO

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

peerless Ten Centuries Points: 1054 More actions · Answer 2

Can you help me get started I would appreciate it.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sprGetCompanySIMCustomFieldsByNullFilter]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sprGetCompanySIMCustomFieldsByNullFilter]

GO

CREATE PROCEDURE [dbo].[sprGetCompanySIMCustomFieldsByNullFilter]

@Custom1HasData bit,

@Custom2HasData bit,

@Custom3HasData bit,

@Custom4HasData bit,

@Custom5HasData bit,

@startRowIndex int,

@maximumRows int,

@sortExpression varchar(500),

@sortOrder varchar(500),

@enterpriseId int

AS

BEGIN

SET NOCOUNT ON;

SELECT * FROM (

SELECT tbl_company.EnterpriseID,

tbl_company.Company,

tbl_sims.SIMID AS SIMID,

tbl_sims.SIMNumber AS SIMNumber,

tbl_sims.MSISDN AS MSISDN,

tbl_sim_custom_fields.Custom1 AS Custom1,

tbl_sim_custom_fields.Custom2 AS Custom2,

tbl_sim_custom_fields.Custom3 AS Custom3,

tbl_sim_custom_fields.Custom4 AS Custom4,

tbl_sim_custom_fields.Custom5 AS Custom5,

--***************************Paging begins**************************************

ROW_NUMBER() OVER(

ORDER BY

CASE WHEN @sortExpression like 'Company' AND @sortOrder like 'DESC' THEN tbl_company.Company END DESC,

CASE WHEN @sortExpression like 'Company' AND @sortOrder like 'ASC' THEN tbl_company.Company END ASC,

CASE WHEN @sortExpression like 'SIMNumber' AND @sortOrder like 'DESC' THEN tbl_sims.SIMNumber END DESC,

CASE WHEN @sortExpression like 'SIMNumber' AND @sortOrder like 'ASC' THEN tbl_sims.SIMNumber END ASC,

CASE WHEN @sortExpression like 'MSISDN' AND @sortOrder like 'DESC' THEN tbl_sims.MSISDN END DESC,

CASE WHEN @sortExpression like 'MSISDN' AND @sortOrder like 'ASC' THEN tbl_sims.MSISDN END ASC,

CASE WHEN @sortExpression like 'Custom1' AND @sortOrder like 'DESC' THEN tbl_sim_custom_fields.Custom1 END DESC,

CASE WHEN @sortExpression like 'Custom1' AND @sortOrder like 'ASC' THEN tbl_sim_custom_fields.Custom1 END ASC,

CASE WHEN @sortExpression like 'Custom2' AND @sortOrder like 'DESC' THEN tbl_sim_custom_fields.Custom2 END DESC,

CASE WHEN @sortExpression like 'Custom2' AND @sortOrder like 'ASC' THEN tbl_sim_custom_fields.Custom2 END ASC,

CASE WHEN @sortExpression like 'Custom3' AND @sortOrder like 'DESC' THEN tbl_sim_custom_fields.Custom3 END DESC,

CASE WHEN @sortExpression like 'Custom3' AND @sortOrder like 'ASC' THEN tbl_sim_custom_fields.Custom3 END ASC,

CASE WHEN @sortExpression like 'Custom4' AND @sortOrder like 'DESC' THEN tbl_sim_custom_fields.Custom4 END DESC,

CASE WHEN @sortExpression like 'Custom4' AND @sortOrder like 'ASC' THEN tbl_sim_custom_fields.Custom4 END ASC,

CASE WHEN @sortExpression like 'Custom5' AND @sortOrder like 'DESC' THEN tbl_sim_custom_fields.Custom5 END DESC,

CASE WHEN @sortExpression like 'Custom5' AND @sortOrder like 'ASC' THEN tbl_sim_custom_fields.Custom5 END ASC

) as RowNum,count(*) over() as totalrows

--**************************Paging ends***************************************************

FROM tbl_sims JOIN tbl_sim_custom_fields on tbl_sims.SIMID = tbl_sim_custom_fields.SIMID

JOIN tbl_locations ON tbl_sims.LocationID = tbl_locations.LocationID

JOIN tbl_company ON tbl_locations.EnterpriseID = tbl_company.EnterpriseID

WHERE tbl_company.EnterpriseID = @enterpriseId

--------- THIS IS WHERE I AM STUCK

IF (@Custom1HasData = 0)

BEGIN

--stick a whole paged sql statement here

--with this in the where clause tbl_sim_custom_fields.Custom1 IS NULL

END

IF (@Custom1HasData = 1)

BEGIN

--stick the other whole paged sql statement here

--with this in the where clause tbl_sim_custom_fields.Custom1 IS NOT NULL

END

)

a WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

END

GO

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 3

mathieu_cupryk (9/22/2009)

Can you help me get started I would appreciate it.

I can try, but I really don't get what it is you're trying to achieve?

Why don't you create the proc whose code I listed, and have a play with it, then see if it gives you any ideas...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

peerless Ten Centuries Points: 1054 More actions · Answer 4

sprGetCompanySIMCustomFieldsByCompanyID 'IS NULL','IS NULL','IS NULL','IS NULL','IS NULL',1,500,'Company','ASC',368

sprGetCompanySIMCustomFieldsByCompanyID '','','','','',1,500,'Company','ASC',368

AND tbl_sim_custom_fields.Custom1 is null

will return me all the rows

Note in my table all the values for

Custom1=null

Custom2=null

Custom3=null

Custom4=null

Custom5=null

What should I do to test this?

Returns me no rows

Note

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sprGetCompanySIMCustomFieldsByCompanyID]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sprGetCompanySIMCustomFieldsByCompanyID]

GO

CREATE PROCEDURE [dbo].[sprGetCompanySIMCustomFieldsByCompanyID]

@custom1HasData varchar(500),

@custom2HasData varchar(500),

@custom3HasData varchar(500),

@custom4HasData varchar(500),

@custom5HasData varchar(500),

@startRowIndex int,

@maximumRows int,

@sortExpression varchar(500),

@sortOrder varchar(500),

@enterpriseId int

AS

BEGIN

SET NOCOUNT ON;

IF LEN(@custom1HasData)=0

BEGIN

SET @custom1HasData='%%'

END

IF LEN(@custom2HasData)=0

BEGIN

SET @custom2HasData='%%'

END

IF LEN(@custom3HasData)=0

BEGIN

SET @custom3HasData='%%'

END

IF LEN(@custom4HasData)=0

BEGIN

SET @custom4HasData='%%'

END

IF LEN(@custom5HasData)=0

BEGIN

SET @custom5HasData='%%'