Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-24 : 17:11:49
|
Martin writes "Hi :) , I need to pass a parameter as 'PAL','RBS','FLA','1LX','1TP' and stuff it in a WHERE described below , if you hardcore the values it does return a recordset , but when passing it as a parameter it fails....any help would be appreciate it !
Create Procedure sp_BYO_IN
As
DECLARE @in char(155);
SET @in = CHAR(39) + 'PAL' + CHAR(39) + ',' + CHAR(39) + 'RBS' + CHAR(39) + ',' + CHAR(39) + 'FLA' + CHAR(39)
--'PAL','RBS','FLA','1LX','1TP'
DECLARE @MDL char(10)
SET @MDL = CHAR(39) + RTRIM('TRXLXA') + CHAR(39)
SELECT RTRIM(BTV01120.OPTN_CD),
BTV01120.OPTN_TYPE_CD,
BTV01120.OPTN_DESC_TX,
BTV01130.RTL_GROSS_PRICE_AM
FROM BTV01130 INNER JOIN BTV01120 ON
BTV01130.MDL_YR_DT = BTV01120.MDL_YR_DT AND
BTV01130.MDL_CD = BTV01120.MDL_CD AND
BTV01130.OPTN_CD = BTV01120.OPTN_CD AND
BTV01130.EFCTV_DT =
(SELECT TOP 1 BTV01130.EFCTV_DT
FROM BTV01130
WHERE BTV01130.MDL_YR_DT = BTV01120.MDL_YR_DT AND
BTV01130.MDL_CD = BTV01120.MDL_CD AND
BTV01130.OPTN_CD = BTV01120.OPTN_CD AND
BTV01130.EFCTV_DT <= CURRENT_TIMESTAMP
ORDER BY BTV01130.EFCTV_DT DESC) AND
(RTRIM(BTV01130.OPTN_CD) IN ( ' + @in + ')) AND
(BTV01120.MDL_CD = 'TRXLXA') AND (BTV01120.MDL_YR_DT = 2001)
-- also tried this but :(
/*
EXEC ('SELECT RTRIM(BTV01120.OPTN_CD),
BTV01120.OPTN_TYPE_CD,
BTV01120.OPTN_DESC_TX,
BTV01130.RTL_GROSS_PRICE_AM
FROM BTV01130 INNER JOIN BTV01120 ON
BTV01130.MDL_YR_DT = BTV01120.MDL_YR_DT AND
BTV01130.MDL_CD = BTV01120.MDL_CD AND
BTV01130.OPTN_CD = BTV01120.OPTN_CD AND
BTV01130.EFCTV_DT =
(SELECT TOP 1 BTV01130.EFCTV_DT
FROM BTV01130
WHERE BTV01130.MDL_YR_DT = BTV01120.MDL_YR_DT AND
BTV01130.MDL_CD = BTV01120.MDL_CD AND
BTV01130.OPTN_CD = BTV01120.OPTN_CD AND
BTV01130.EFCTV_DT <= CURRENT_TIMESTAMP
ORDER BY BTV01130.EFCTV_DT DESC) AND
(RTRIM(BTV01130.OPTN_CD) IN ( ' + @in + ')) AND
(BTV01120.MDL_CD = ' + @MDL + ') AND (BTV01120.MDL_YR_DT = 2001)')
*/" |
|
|
|
|
|
|
|
WHERE IN clause issue :( !!