Проблема с передачей параметра в запрос

← →
Kley (2011-06-10 10:51) [0]

Здравствуйте!есть AdoQuery (KonvQuery) со статическим запросом указанным ниже и параметром MSP, данные в параметр передаются на
событие нажатия кнопки вот так
dm.KonvQuery.Parameters.ParamByName("MSP").Value:=form25.sComboEdit1.Text;
dm.KonvQuery.Close;
dm.KonvQuery.Open;
свойства параметра Datatype-ftString,
генерируется ошибка: символьные или двоичные данные могут быть усечены.
Подскажите плиз как правильно передать параметр в данный запрос?
БД MS SQL server 2005/

Declare @post int, @msp varchar(20), @lcat int
Set @msp=(Select A_COD from ppr_serv where A_ID=:MSP)

DECLARE
@TMP_PID_PB_SERV_CAT TABLE
(
PERSID INT,
NUMPB VARCHAR(20),
SERV VARCHAR (255),
CAT VARCHAR (255),
A_AMOUNT FLOAT,
DATESTART datetime,
DATELAST datetime,
KodMsp INT,
sposob VARCHAR (255),
shet VARCHAR (255),
OSB VARCHAR (255),
LgotID INT
)
DECLARE @TMP_PERSON_INFO TABLE
(
PERSID INT,
IPD varchar (50),
FAM VARCHAR (255),
NAM VARCHAR (255),
OTH VARCHAR (255),
BDATE DATETIME,
DOCTYPE VARCHAR(50),
DOCSER VARCHAR (20),
DOCNUM VARCHAR (20),
DOCDATE DATETIME,
ADDRESS VARCHAR (255),
Sity VARCHAR (255),
Street VARCHAR (255),
kv VARCHAR (255),
Dom VARCHAR (255),
fromid INT
)

INSERT INTO @TMP_PID_PB_SERV_CAT(PERSID, NUMPB, SERV, CAT, A_AMOUNT, DATESTART,DATELAST,KodMsp,sposob,shet,OSB,LgotID)
SELECT
ESS.A_PERSONOUID AS PERSID,
PAYBOOK.A_NUMPB AS NUMPB,
SERV.A_NAME AS SERV,
CAT.A_NAME AS CAT,
WMSPA.A_AMOUNT AS A_AMOUNT,
WMSPA.A_DATESTART AS DATESTART,
WMSPA.A_DATELAST AS DATELAST,
SERV.A_ID as KodMsp,
pay.Deliveryway as sposob,
pay.ACCOUNTINGCOUNT AS SHET,
Pay.Bank as OSB,
CAT.A_ID as LgotID
FROM SPR_NPD_MSP_CAT SNMC
INNER JOIN ESRN_SERV_SERV ESS ON SNMC.A_ID=ESS.A_SERV
LEFT JOIN PPR_SERV SERV ON SNMC.A_MSP=SERV.A_ID
LEFT JOIN PPR_CAT CAT ON SNMC.A_CATEGORY = CAT.A_ID
INNER JOIN WM_PAYMENT_BOOK PAYBOOK ON ESS.A_PAYMENTBOOK=PAYBOOK.OUID
INNER JOIN WM_PAYMENT PAY ON PAYBOOK.A_ACTREQUISIT=PAY.OUID
INNER JOIN WM_PERSONAL_CARD WPC ON ESS.A_PERSONOUID=WPC.OUID
INNER JOIN WM_SERV_AMOUNT WMSPA ON ESS.OUID=WMSPA.A_MSP
WHERE SERV.A_COD =@msp
AND ESS.A_STATUSPRIVELEGE=13
and WMSPA.A_STATUS=10

and WMSPA.A_STATUS=10

and WPC.OUID=any
(
Select WPC.OUID
FROM WM_ADDRESS ADR
INNER JOIN WM_PERSONAL_CARD WPC ON ADR.OUID=WPC.A_REGFLAT
)

insert into @TMP_PERSON_INFO(PERSID,IPD, FAM, NAM, OTH, BDATE, DOCTYPE, DOCSER, DOCNUM, DOCDATE, ADDRESS,sity,Street,kv,Dom,FromId)
select
wpc.ouid as persid,
wpc.a_spunid as IPD,
sfs.a_name as FAM,
SFN.A_NAME AS NAM,
SFSEC.A_NAME AS OTH,
WPC.BIRTHDATE AS BDATE,
PPRDOC.A_NAME AS DOCTYPE,
DOC.DOCUMENTSERIES AS DOCSER,
DOC.DOCUMENTSNUMBER AS DOCNUM,
DOC.ISSUEEXTENSIONSDATE AS DOCDATE,
Waddr.A_ADRTITLE as ADDRESS,
sity.A_Name as Sity,
Street.A_Name as Street,
Waddr.A_Housenumber as Dom,
Waddr.A_FlatNumber as Kv,
wrgp.A_FROMID as fromid

FROM WM_PERSONAL_CARD WPC
LEFT JOIN SPR_FIO_SURNAME SFS ON WPC.SURNAME=SFS.OUID
LEFT JOIN SPR_FIO_NAME SFN ON WPC.A_NAME=SFN.OUID
LEFT JOIN SPR_FIO_SECONDNAME SFSEC ON WPC.A_SECONDNAME=SFSEC.OUID
INNER JOIN WM_ACTDOCUMENTS DOC ON WPC.OUID=DOC.PERSONOUID
INNER JOIN PPR_DOC PPRDOC ON DOC.DOCUMENTSTYPE=PPRDOC.A_ID
LEFT JOIN WM_ADDRESS Waddr on WPC.A_REGFLAT=Waddr.OUID
Left JOIN spr_Town sity on Waddr.A_town=sity.OUID
Left JOIN spr_Street Street on Waddr.A_Street=Street.OUID
LEFT JOIN WM_RELGROUPPC wrgp ON wrgp.A_TOID = wpc.OUID AND wpc.A_STATUS=10
LEFT JOIN WM_GROUP_INFO wgi ON wrgp.A_FROMID = wgi.OUID

WHERE WPC.OUID=ANY(SELECT PERSID FROM @TMP_PID_PB_SERV_CAT) AND PPRDOC.A_ISIDENTITYCARD=1

SELECT distinct
T1.persid,
T1.fromid,
T1.FAM AS fam,
T1.NAM AS nam,
T1.OTH as oth,
T1.BDATE as bDate,
T1.sity as Sity,
T1.Street as Street,
T1.kv as dom,
t1.dom as kv,
T2.NUMPB as bookNumber,
T2.SERV as mspType,
T2.CAT as lkType,
T2.KodMsp as KodMSP,
T2.sposob as sVipl,
T2.shet as shet,
T2.OSB as OSB,
T2.LgotId
from @TMP_PID_PB_SERV_CAT T2
inner join @TMP_PERSON_INFO T1 on T2.persid=T1.persid

← →
Ega23 © (2011-06-10 10:59) [1]

Хранимки и вьюхи - это для трусов. Впрочем ладно.

@msp varchar(20) - не наводит на размышления?

← →
Kley (2011-06-10 11:13) [2]

//@msp varchar(20) - не наводит на размышления?
вы имеете ввиду размер?

← →
Kley (2011-06-10 12:06) [3]

пасибо всем!
ошибка была в запросе
@TMP_PID_PB_SERV_CAT TABLE
(
PERSID INT,
NUMPB VARCHAR(20), -здесь нужно было увеличить длинну

← →
Кщд (2011-06-10 19:24) [4]

для чего вместо одного запроса - три?
какова цель?

Проблема с передачей параметра в запрос Найти похожие ветки