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.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =
'W27P'
AND tbl_Levels.nvchIncentiveId = 'MPW' AND
tbl_LevelDetail.nvchIncentiveId = 'MPW'
AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' AND
tbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelId
AND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelId
AND tbl_Levels.nvchReportingLevelId =
tbl_LevelDetail_Report.nvchLevelId
AND tbl_LevelDetail.nvchLevelTypeId = 2
AND tbl_LevelDetail_Report.nvchLevelTypeId = 1
AND tbl_Levels.nvchReportingLevelId IN ('a')
GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,
tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelName
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_incmpwfilter_rs
(
@strPeriodID varchar ,
@intLevelDetailID varchar,
@intLevelReportID 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.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =
@strPeriodID
AND tbl_Levels.nvchIncentiveId = 'MPW' AND
tbl_LevelDetail.nvchIncentiveId = 'MPW'
AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' AND
tbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelId
AND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelId
AND tbl_Levels.nvchReportingLevelId =
tbl_LevelDetail_Report.nvchLevelId
AND tbl_LevelDetail.nvchLevelTypeId = @intLevelDetailID
AND tbl_LevelDetail_Report.nvchLevelTypeId = @intLevelReportID
AND tbl_Levels.nvchReportingLevelId IN (@strFilters )
GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,
tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelName
ORDER BY Pos, DataLevelName
then call it by SQL statement:

EXEC usp_incmpwfilter_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.nvchReportingLevelId 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.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =
'W27P'
AND tbl_Levels.nvchIncentiveId = 'MPW' AND
tbl_LevelDetail.nvchIncentiveId = 'MPW'
AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' AND
tbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelId
AND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelId
AND tbl_Levels.nvchReportingLevelId =
tbl_LevelDetail_Report.nvchLevelId
AND tbl_LevelDetail.nvchLevelTypeId = 2
AND tbl_LevelDetail_Report.nvchLevelTypeId = 1
AND tbl_Levels.nvchReportingLevelId IN ('a')
GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,
tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelName
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_incmpwfilter_rs
(
@strPeriodID varchar ,
@intLevelDetailID varchar,
@intLevelReportID 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.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =
@strPeriodID
AND tbl_Levels.nvchIncentiveId = 'MPW' AND
tbl_LevelDetail.nvchIncentiveId = 'MPW'
AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' AND
tbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelId
AND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelId
AND tbl_Levels.nvchReportingLevelId =
tbl_LevelDetail_Report.nvchLevelId
AND tbl_LevelDetail.nvchLevelTypeId = @intLevelDetailID
AND tbl_LevelDetail_Report.nvchLevelTypeId = @intLevelReportID
AND tbl_Levels.nvchReportingLevelId IN (@strFilters )
GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,
tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelName
ORDER BY Pos, DataLevelName

then call it by SQL statement:

EXEC usp_incmpwfilter_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.nvchReportingLevelId 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_incmpwfilter_rs
(
@strPeriodID varchar ,
@intLevelDetailID varchar,
@intLevelReportID varchar,
@strFilters varchar
)
AS
As Gert-Jan said, you need to specify the length, or else you only
get varchar(1).
AND tbl_Levels.nvchReportingLevelId 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.nvchReportingLevelId 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!