help with stored procedures using IN / GROUP BY statements

[chowda]

trying to get to the bottom of this for some time...... eventually to
be used with asp.

heres the problem

the following rather complex SQL statement works fine via query
analyser:

SELECT TOP 100 tbl_LevelDetail .nvchLevelName AS DataLevelName,
MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Pos',
MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '
' END) AS 'AreaName',
MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '
' END) AS 'BDGName',
MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '
' END) AS 'Performance',
MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Qualifier'
FROM tbl_Data, tbl_Levels, tbl_LevelDetail , tbl_LevelDetail AS
tbl_LevelDetail _Report
WHERE tbl_Data.nvchIn centiveId = 'MPW' AND tbl_Data.nvchPe riodId =
'W27P'
AND tbl_Levels.nvch IncentiveId = 'MPW' AND
tbl_LevelDetail .nvchIncentiveI d = 'MPW'
AND tbl_LevelDetail _Report.nvchInc entiveId = 'MPW' AND
tbl_Data.nvchDa taLevelId = tbl_Levels.nvch DataLevelId
AND tbl_Levels.nvch DataLevelId = tbl_LevelDetail .nvchLevelId
AND tbl_Levels.nvch ReportingLevelI d =
tbl_LevelDetail _Report.nvchLev elId
AND tbl_LevelDetail .nvchLevelTypeI d = 2
AND tbl_LevelDetail _Report.nvchLev elTypeId = 1
AND tbl_Levels.nvch ReportingLevelI d IN ('a')
GROUP BY tbl_Levels.nvch ReportingLevelI d, tbl_Levels.nvch DataLevelId,
tbl_LevelDetail .nvchLevelName, tbl_LevelDetail _Report.nvchLev elName
ORDER BY Pos, DataLevelName

returns rows ok no problem
but when trying to convert to a stored procedure i dont get any
results:

CREATE PROCEDURE usp_incmpwfilte r_rs
(
@strPeriodID varchar ,
@intLevelDetail ID varchar,
@intLevelReport ID varchar,
@strFilters varchar
)
AS

set nocount on

SELECT TOP 100 tbl_LevelDetail .nvchLevelName AS DataLevelName,
MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Pos',
MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '
' END) AS 'AreaName',
MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '
' END) AS 'BDGName',
MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '
' END) AS 'Performance',
MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Qualifier'
FROM tbl_Data, tbl_Levels, tbl_LevelDetail , tbl_LevelDetail AS
tbl_LevelDetail _Report
WHERE tbl_Data.nvchIn centiveId = 'MPW' AND tbl_Data.nvchPe riodId =
@strPeriodID
AND tbl_Levels.nvch IncentiveId = 'MPW' AND
tbl_LevelDetail .nvchIncentiveI d = 'MPW'
AND tbl_LevelDetail _Report.nvchInc entiveId = 'MPW' AND
tbl_Data.nvchDa taLevelId = tbl_Levels.nvch DataLevelId
AND tbl_Levels.nvch DataLevelId = tbl_LevelDetail .nvchLevelId
AND tbl_Levels.nvch ReportingLevelI d =
tbl_LevelDetail _Report.nvchLev elId
AND tbl_LevelDetail .nvchLevelTypeI d = @intLevelDetail ID
AND tbl_LevelDetail _Report.nvchLev elTypeId = @intLevelReport ID
AND tbl_Levels.nvch ReportingLevelI d IN (@strFilters )
GROUP BY tbl_Levels.nvch ReportingLevelI d, tbl_Levels.nvch DataLevelId,
tbl_LevelDetail .nvchLevelName, tbl_LevelDetail _Report.nvchLev elName
ORDER BY Pos, DataLevelName
then call it by SQL statement:

EXEC usp_incmpwfilte r_rs 'W27P',2,1,'a'

Returns no rows. This is the initial problem. Also there will be
another issue if i can get the above to work: the @strFilters can
contain multiple data, ie 'a','k'
this works fine in the 1st sql statement ie: AND
tbl_Levels.nvch ReportingLevelI d IN ('a','k') but I dont know how to
pass as a parameter to the stored procedure. I cannot create temporary
tables.

i had not created the intial SQL statement, i am just trying to
convert it to a stored procedure which accepts thos parameters. this
has been a real headache for me, any help as always appreciated
greatly.

[Gert-Jan Strik]

In all your char and varchar definition, you need to specify the size.

So in your CASE expression you could cast to char(3) instead of char.
The immediate problem is probably that currently your parameters are
also non-sized, which (I believe) defaults to varchar(1).

Hope this helps,
Gert-Jan
chowda wrote:

trying to get to the bottom of this for some time...... eventually to
be used with asp.

heres the problem

the following rather complex SQL statement works fine via query
analyser:

SELECT TOP 100 tbl_LevelDetail .nvchLevelName AS DataLevelName,
MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Pos',
MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '
' END) AS 'AreaName',
MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '
' END) AS 'BDGName',
MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '
' END) AS 'Performance',
MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Qualifier'
FROM tbl_Data, tbl_Levels, tbl_LevelDetail , tbl_LevelDetail AS
tbl_LevelDetail _Report
WHERE tbl_Data.nvchIn centiveId = 'MPW' AND tbl_Data.nvchPe riodId =
'W27P'
AND tbl_Levels.nvch IncentiveId = 'MPW' AND
tbl_LevelDetail .nvchIncentiveI d = 'MPW'
AND tbl_LevelDetail _Report.nvchInc entiveId = 'MPW' AND
tbl_Data.nvchDa taLevelId = tbl_Levels.nvch DataLevelId
AND tbl_Levels.nvch DataLevelId = tbl_LevelDetail .nvchLevelId
AND tbl_Levels.nvch ReportingLevelI d =
tbl_LevelDetail _Report.nvchLev elId
AND tbl_LevelDetail .nvchLevelTypeI d = 2
AND tbl_LevelDetail _Report.nvchLev elTypeId = 1
AND tbl_Levels.nvch ReportingLevelI d IN ('a')
GROUP BY tbl_Levels.nvch ReportingLevelI d, tbl_Levels.nvch DataLevelId,
tbl_LevelDetail .nvchLevelName, tbl_LevelDetail _Report.nvchLev elName
ORDER BY Pos, DataLevelName

returns rows ok no problem
but when trying to convert to a stored procedure i dont get any
results:

CREATE PROCEDURE usp_incmpwfilte r_rs
(
@strPeriodID varchar ,
@intLevelDetail ID varchar,
@intLevelReport ID varchar,
@strFilters varchar
)
AS

set nocount on

SELECT TOP 100 tbl_LevelDetail .nvchLevelName AS DataLevelName,
MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Pos',
MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '
' END) AS 'AreaName',
MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '
' END) AS 'BDGName',
MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '
' END) AS 'Performance',
MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0
END) AS 'Qualifier'
FROM tbl_Data, tbl_Levels, tbl_LevelDetail , tbl_LevelDetail AS
tbl_LevelDetail _Report
WHERE tbl_Data.nvchIn centiveId = 'MPW' AND tbl_Data.nvchPe riodId =
@strPeriodID
AND tbl_Levels.nvch IncentiveId = 'MPW' AND
tbl_LevelDetail .nvchIncentiveI d = 'MPW'
AND tbl_LevelDetail _Report.nvchInc entiveId = 'MPW' AND
tbl_Data.nvchDa taLevelId = tbl_Levels.nvch DataLevelId
AND tbl_Levels.nvch DataLevelId = tbl_LevelDetail .nvchLevelId
AND tbl_Levels.nvch ReportingLevelI d =
tbl_LevelDetail _Report.nvchLev elId
AND tbl_LevelDetail .nvchLevelTypeI d = @intLevelDetail ID
AND tbl_LevelDetail _Report.nvchLev elTypeId = @intLevelReport ID
AND tbl_Levels.nvch ReportingLevelI d IN (@strFilters )
GROUP BY tbl_Levels.nvch ReportingLevelI d, tbl_Levels.nvch DataLevelId,
tbl_LevelDetail .nvchLevelName, tbl_LevelDetail _Report.nvchLev elName
ORDER BY Pos, DataLevelName

then call it by SQL statement:

EXEC usp_incmpwfilte r_rs 'W27P',2,1,'a'

Returns no rows. This is the initial problem. Also there will be
another issue if i can get the above to work: the @strFilters can
contain multiple data, ie 'a','k'
this works fine in the 1st sql statement ie: AND
tbl_Levels.nvch ReportingLevelI d IN ('a','k') but I dont know how to
pass as a parameter to the stored procedure. I cannot create temporary
tables.

i had not created the intial SQL statement, i am just trying to
convert it to a stored procedure which accepts thos parameters. this
has been a real headache for me, any help as always appreciated
greatly.

[Erland Sommarskog]

chowda (pa************ *****@yahoo.co. uk) writes:

CREATE PROCEDURE usp_incmpwfilte r_rs
(
@strPeriodID varchar ,
@intLevelDetail ID varchar,
@intLevelReport ID varchar,
@strFilters varchar
)
AS
As Gert-Jan said, you need to specify the length, or else you only
get varchar(1).
AND tbl_Levels.nvch ReportingLevelI d IN (@strFilters )

Returns no rows. This is the initial problem. Also there will be
another issue if i can get the above to work: the @strFilters can
contain multiple data, ie 'a','k'
this works fine in the 1st sql statement ie: AND
tbl_Levels.nvch ReportingLevelI d IN ('a','k') but I dont know how to
pass as a parameter to the stored procedure. I cannot create temporary
tables.

Look at
http://www.algonet.se/~sommar/arrays...ist-of-strings.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[chowda]

thats great Gert-Jan, i should have picked up the missing size params
as I have placed them in my other stored procedures!, and many thanks
for your article Erland - i have the comma delimited string working
now! Now i've got to build my asp and i'm flying!