sqlserver procedure to vb

[rajivg]

hi, I have to convert a sqlserver procedure to vb which is using ODBC Microsoft Paradox driver which does not recognise procedures in sqlserver. So i have to convert into vb which paradox can understand. this is the code in sqlserver , i'll appreciate if you show me the code in vb for the same procedure. thankyou.


CREATE PROC hierprop_setbounded
@hier_id INT
, @hier_prop_id INT
, @bounded BIT
AS


BEGIN TRANSACTION _hierprop_setbounded

DECLARE @current_state BIT

SELECT @current_state=is_bounded
FROM hier_prop
WHERE hier_id=@hier_id
AND id=@hier_prop_id

IF @@ROWCOUNT=0 OR @@ERROR<>0
BEGIN
RAISERROR('Error : No such property_defintion hier_id=%d, hierprop_id=%d',16,1,@hier_id,@hier_prop_id)
ROLLBACK TRANSACTION _hierprop_setbounded
RETURN -1
END

IF @current_state=@bounded
RETURN 0

UPDATE hier_prop
SET is_bounded=@bounded
WHERE hier_id=@hier_id
AND id=@hier_prop_id

IF @@ROWCOUNT=0 OR @@ERROR<>0
BEGIN
RAISERROR('Error : Unable to update the hier_prop table',16,1)
ROLLBACK TRANSACTION _hierprop_setbounded
RETURN -2
END


IF @bounded=1
BEGIN
/* populate the hier_prop_values table as the distinct set of values already used for this property */
DELETE
FROM hier_prop_value
WHERE hier_id=@hier_id
AND hier_prop_id=@hier_prop_id

INSERT
INTO hier_prop_value
( hier_id
, hier_prop_id
, value
)
( SELECT DISTINCT hier_id, hier_prop_id, value
FROM term_prop
WHERE hier_id=@hier_id
AND hier_prop_id=@hier_prop_id
AND value IS NOT NULL
)

/* now convert from value to hier_prop_value_id pointers */
UPDATE term_prop
SET hier_prop_value_id=hpv.id
FROM term_prop
LEFT JOIN hier_prop_value hpv
ON hpv.hier_id=@hier_id
AND hpv.hier_prop_id=@hier_prop_id
AND hpv.value=term_prop.value
WHERE term_prop.hier_id=@hier_id
AND term_prop.hier_prop_id=@hier_prop_id

/* clear out the old values */
UPDATE term_prop
SET value = NULL
WHERE term_prop.hier_id=@hier_id
AND term_prop.hier_prop_id=@hier_prop_id

END
ELSE /*@bounded=0*/
BEGIN

/* convert value pointed to by hier_prop_value_id to actual value */
UPDATE term_prop
SET value=hpv.value
FROM term_prop
LEFT JOIN hier_prop_value hpv
ON hpv.hier_id=@hier_id
AND hpv.hier_prop_id=@hier_prop_id
AND hpv.id=term_prop.hier_prop_value_id
WHERE term_prop.hier_id=@hier_id
AND term_prop.hier_prop_id=@hier_prop_id

/* null the hier_prop_value_id col */
UPDATE term_prop
SET hier_prop_value_id=NULL
WHERE term_prop.hier_id=@hier_id
AND term_prop.hier_prop_id=@hier_prop_id

/* remove the values from the hier_prop_value table */
DELETE
FROM hier_prop_value
WHERE hier_id=@hier_id
AND hier_prop_id=@hier_prop_id

END

COMMIT TRANSACTION _hierprop_setbounded


regards
rajiv