September 22, 2009 at 10:45 am
delete
September 22, 2009 at 11:40 am
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
September 22, 2009 at 12:19 pm
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
September 22, 2009 at 1:28 pm
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
September 22, 2009 at 2:05 pm
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='%%'
END
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
AND tbl_sim_custom_fields.Custom1 like @custom1HasData
AND tbl_sim_custom_fields.Custom2 like @custom2HasData
AND tbl_sim_custom_fields.Custom3 like @custom3HasData
AND tbl_sim_custom_fields.Custom4 like @custom4HasData
AND tbl_sim_custom_fields.Custom5 like @custom5HasData
)
a WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
END
GO
September 22, 2009 at 2:28 pm
I don't understand. Please take a look at the proc I supplied to you.
And please don't post the source to the same proc again, I honestly don't have the time to try and figure out what it is you need by reading a very long stored procedure that you're half way through. Sorry.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply