SQL IN problem

[venkat]

Can anybody help me
i written one stored procedure which its working integer values if i
go to strig its not working
(Multiple select) i read some articles i dont understand i will put my
code here just check it.

Here Problem is @OPN_GROUP its actually as a string value i should
passing here.
how to solve this problem Using 'IN' in stored procedure.


ALTER PROCEDURE [dbo].[SAG_SAG0001_SP](
@RUNDATE nvarchar(200),
@REPORT_NAME nvarchar(200),
@GROUP_NAME nvarchar(200),
@PKG_GROUP nvarchar(200),
@OPN_GROUP nvarchar(200),
@OPN_CODE nvarchar(200))

AS

SET NOCOUNT ON;
DECLARE @Str nvarchar(1000)
DECLARE @Flag int

SET @Str=' WHERE'


IF(@RUNDATE IS NULL AND @REPORT_NAME IS NULL AND @GROUP_NAME IS NULL
AND @PKG_GROUP IS NULL AND @OPN_GROUP IS NULL AND @OPN_CODE IS NULL)
OR
(@RUNDATE IS NOT NULL AND @REPORT_NAME=' ALL' AND @GROUP_NAME=' ALL'
AND
@PKG_GROUP=' ALL' AND @OPN_CODE=' ALL')

EXEC('SELECT GROUP_NAME, PKG_GROUP, OPN_CODE,OPN_GROUP, OPN, EOH,
EOH_R, DD0, DD1, DD2, DD3, DD4, DD5, DD6, WTD, PTD, WTD_DELTA,
PTD_DELTA,
TARGET_INV, O_SCH, R_SCH FROM
SAG_DAILY_PERFORMANCE_HIREL_T')

ELSE
BEGIN
IF(@RUNDATE is not null) and (@RUNDATE<>'')
BEGIN
SET @Str = @Str + ' RUNDATE = ' + QUOTENAME(@RUNDATE, '''')
SET @Flag=1

END

IF(@REPORT_NAME is not null) and (@REPORT_NAME <>'') AND i_NAME<>'
ALL')
BEGIN
SET @Str = @Str + ' AND REPORT_NAME = '+ QUOTENAME(@REPORT_NAME,
'''')

END

IF (@GROUP_NAME IS NOT NULL) AND (@GROUP_NAME<>'') AND (@GROUP_NAME<>'
ALL')
BEGIN
SET @Str = @Str + ' AND GROUP_NAME = '+ QUOTENAMEGROUP_NAME, '''')
END

IF(@PKG_GROUP IS NOT NULL) AND (@PKG_GROUP<>'') AND _GROUP<>' ALL')
BEGIN
SET @Str = @Str + ' AND PKG_GROUP= '+ QUOTENAME(@PKG_GROUP,
'''')
END

IF(@OPN_GROUP IS NOT NULL) AND (@OPN_GROUP<>'') AND (@OPN_GROUP <> '
ALL')
BEGIN
SET @Str = @Str + ' AND [OPN_GROUP] IN'+
QUOTENAME(@OPN_GROUP, '''')

END

IF(@OPN_CODE IS NOT NULL) AND (@OPN_CODE<>'') AND (@OPN_CODE<>' ALL')
BEGIN
SET @Str = @Str + ' AND OPN_CODE IN '+ '('+ @OPN_CODE + ')'
END

EXEC('SELECT GROUP_NAME, PKG_GROUP, OPN_CODE,OPN_GROUP, OPN, EOH,
EOH_R, DD0, DD1, DD2, DD3, DD4, DD5, DD6, WTD, PTD, WTD_DELTA,
PTD_DELTA,
TARGET_INV, O_SCH, R_SCH
FROM SAG_DAILY_PERFORMANCE_HIREL_T' + @Str)

END

 

[Otis Mukinfus]

Wrong group my friend. Try a group dedicated to SQL.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com