How can i run and send parameters to a SP from VB

[skynetosiris]

I want to execute and send parameters to a SP script within visual basic, how is this posible?

 

[TomKane]

This is how we do it:

strSQL = "USP_GET_ACCOUNT_FOR_ACTION" _
& " @INFIXEDDATE='" & dtFixedEndDate & "'," _
& " @INDATE='" & dtLocalWorkDate & "'," _
& " @INTIMEPERIODCODE=" & intLocalTimePeriodId & "," _
& " @INFROMWORKPROCESSCODE=" & lngLocalSelectedWorkProcessFromCode & "," _
& " @INTOWORKPROCESSCODE=" & lngLocalSelectedWorkProcessToCode & "," _
& " @INACCTYPE='" & strAccountTermType & "'," _
& " @INJOBLIST='" & strLocalListJobRoles & "'," _
& " @INFROMJOBTYPECODE='" & strLocalSelectedJobTypeFromCode & "'," _
& " @INTOJOBTYPECODE='" & strLocalSelectedJobTypeToCode & "'"
Set adorsLocal = AccessADO.Execute(strSQL)

And then in the stored procedure we have:

CREATE PROCEDURE USP_GET_ACCOUNT_FOR_ACTION
@INFIXEDDATE CHAR(19), @INDATE CHAR(19), @INTIMEPERIODCODE SMALLINT,
@INFROMJOBTYPECODE CHAR(10), @INTOJOBTYPECODE CHAR(10),
@INFROMWORKPROCESSCODE SMALLINT, @INTOWORKPROCESSCODE SMALLINT,
@INACCTYPE CHAR(3), @INJOBLIST VARCHAR(8000)
AS
BEGIN
SET DATEFORMAT DMY
SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000), @LOCALFROMWORKPROCESSCODE VARCHAR(5), @LOCALTOWORKPROCESSCODE VARCHAR(5), @LOCALTIMEPERIODCODE VARCHAR(5)
EXEC USP_CHECK_UPDATE_BRANCH_TIME_PERIOD @INFIXEDDATE=@INFIXEDDATE, @INDATE=@INDATE

SET @LOCALFROMWORKPROCESSCODE = CONVERT(VARCHAR(5),@INFROMWORKPROCESSCODE)
SET @LOCALTOWORKPROCESSCODE = CONVERT(VARCHAR(5),@INTOWORKPROCESSCODE)
SET @LOCALTIMEPERIODCODE = CONVERT(VARCHAR(5),@INTIMEPERIODCODE)
IF @INJOBLIST = "" SET @INJOBLIST = "''"

SET @SQL = "SELECT a.ID AS a_ID, a.FKBRANCHCODE AS a_FKBRANCHCODE, a.FKPRODUCTCODE AS a_FKPRODUCTCODE, a.FKLEGALENTITYCODE AS a_FKLEGALENTITYCODE," +
" a.ACCOUNTSUBREFERENCE AS a_ACCOUNTSUBREFERENCE, a.FKLIMITID AS a_FKLIMITID, a.FKBUSINESSACTIONCODE AS a_FKBUSINESSACTIONCODE," +
" a.REDUCELIMIT AS a_REDUCELIMIT, a.ENTERINTERESTRATES AS a_ENTERINTERESTRATES, a.SCHEDULEDREPAYMENT AS a_SCHEDULEDREPAYMENT," +
" a.SCHEDULEAMOUNT AS a_SCHEDULEAMOUNT, a.SCHEDDATE AS a_SCHEDDATE, a.FUNDINGREFERENCE AS a_FUNDINGREFERENCE, a.VALUEDATE AS a_VALUEDATE," +
" b.DIRECTION AS b_DIRECTION, c.CODE AS c_CODE, c.NAME AS c_NAME, c.BANKCODE AS c_BANKCODE, c.NSCCODE AS c_NSCCODE," +
" c.CUSACCOUNTNO AS c_CUSACCOUNTNO, c.CONTACT AS c_CONTACT, c.PHONE AS c_PHONE, d.CODE AS d_CODE, d.SWIFTCODE AS d_SWIFTCODE," +
" d.DECIMALPLACES AS d_DECIMALPLACES, e.CODE AS e_CODE, e.TYPE AS e_TYPE, f.CODE AS f_CODE, f.SEQUENCE AS f_SEQUENCE," +
" g.CODE AS g_CODE, g.FKTEAMCODE AS g_FKTEAMCODE, h.CODE AS h_CODE, i.STARTDATE AS i_STARTDATE, i.MATURITYDATE AS i_MATURITYDATE," +
" i.CAPITALAMOUNT AS i_CAPITALAMOUNT, i.INTERESTAMOUNT AS i_INTERESTAMOUNT, i.INTERESTRATE AS i_INTERESTRATE," +
" i.COF AS i_COF, i.MARGIN AS i_MARGIN, i.RAC AS i_RAC, i.AOB AS i_AOB, i.FLOATINGRATE AS i_FLOATINGRATE," +
" i.NARRATIVE AS i_NARRATIVE, k.FIRSTNAME AS k_FIRSTNAME, k.LASTNAME AS k_LASTNAME" +
" FROM ACCOUNT_FOR_ACTION a, JOB_ROLE_ACCOUNT_FOR_ACTION b, CUSTOMER c, CURRENCY_INFO d, WORK_PROCESS e," +
" WORK_PROCESS_STAGE f, JOB_ROLE g, JOB_TYPE h, ACCOUNT_TERMS i, VALID_USER_JOB_ROLE j, VALID_USER k, BRANCH_TIME_PERIOD l" +
" WHERE a.ID = b.FKACTIONID AND b.ENDDATE = '" + @INFIXEDDATE + "' AND a.FKCUSTOMERCODE = c.CODE AND a.FKCURRENCYCODE = d.CODE" +
" AND a.FKWORKPROCESSCODE = e.CODE AND b.FKJOBROLECODE = g.CODE AND g.FKJOBTYPECODE = h.CODE AND h.CODE = f.FKJOBTYPECODE" +
" AND (h.CODE BETWEEN '" + @INFROMJOBTYPECODE + "' AND '" + @INTOJOBTYPECODE + "')" +
" AND (e.CODE BETWEEN " + @LOCALFROMWORKPROCESSCODE + " AND " + @LOCALTOWORKPROCESSCODE + &quot" +
" AND f.FKWORKPROCESSCODE = e.CODE AND i.FKACTIONID = a.ID AND i.TYPE = '" + @INACCTYPE + "' AND j.FKJOBROLECODE = g.CODE" +
" AND j.ENDDATE = '" + @INFIXEDDATE + "' AND j.FKVALIDUSERCODE = k.CODE" +
" AND a.VALUEDATE BETWEEN l.STARTDATE AND l.ENDDATE AND a.FKBRANCHCODE = l.FKBRANCHCODE AND l.FKTIMEPERIODID = " + @LOCALTIMEPERIODCODE +
" AND g.CODE IN (" + @INJOBLIST + &quot"
GOTO x2
-- + " ORDER BY a.FKCUSTOMERCODE, a.FKCURRENCYCODE, a.FKPRODUCTCODE, a.FKLEGALENTITYCODE, a.ACCOUNTSUBREFERENCE"

-- comparison code from TEST - DO NOT USE
-- SET @SQL = "SELECT a.ID AS a_ID, a.FKBRANCHCODE AS a_FKBRANCHCODE, a.FKPRODUCTCODE AS a_FKPRODUCTCODE, a.FKLEGALENTITYCODE AS a_FKLEGALENTITYCODE," +
-- " a.ACCOUNTSUBREFERENCE AS a_ACCOUNTSUBREFERENCE, a.FKLIMITID AS a_FKLIMITID, b.DIRECTION AS b_DIRECTION," +
-- " c.CODE AS c_CODE, c.NAME AS c_NAME, d.CODE AS d_CODE, d.SWIFTCODE AS d_SWIFTCODE, d.DECIMALPLACES AS d_DECIMALPLACES, f.SEQUENCE AS F_SEQUENCE," +
-- " g.CODE AS g_CODE, g.FKTEAMCODE AS g_FKTEAMCODE, h.CODE AS h_CODE, i.STARTDATE AS i_STARTDATE, i.MATURITYDATE AS i_MATURITYDATE," +
-- " i.CAPITALAMOUNT AS i_CAPITALAMOUNT, i.INTERESTAMOUNT AS i_INTERESTAMOUNT, k.FIRSTNAME AS k_FIRSTNAME, k.LASTNAME AS k_LASTNAME," +
-- " m.CODE AS m_CODE, p.VALUEDATE AS p_VALUEDATE, n.CODE AS n_CODE, n.TYPE AS n_TYPE" +
-- " FROM ACCOUNT_FOR_ACTION a, JOB_ROLE_ACCOUNT_FOR_ACTION b, CUSTOMER c, CURRENCY_INFO d, WORK_PROCESS e, WORK_PROCESS_STAGE f," +
-- " JOB_ROLE g, JOB_TYPE h, ACCOUNT_TERMS i, VALID_USER_JOB_ROLE j, VALID_USER k, ACTIVITY m, ACTIVITY_TYPE n, #WORKINGDATE o, ACTIVITY_PHASE p" +
-- " WHERE a.ID = b.FKACTIONID AND b.ENDDATE = '" + @INFIXEDDATE + "' AND a.FKCUSTOMERCODE = c.CODE AND a.FKCURRENCYCODE = d.CODE" +
-- " AND b.FKJOBROLECODE = g.CODE AND g.FKJOBTYPECODE = h.CODE AND h.CODE = f.FKJOBTYPECODE AND h.CODE IN (" + @INJOBTYPESLIST + &quot" +
-- " AND e.CODE = " + @LOCALWORKPROCESSCODE + " AND f.FKWORKPROCESSCODE = e.CODE AND i.FKACTIONID = a.ID AND i.TYPE = '" + @INACCTYPE + "'" +
-- " AND j.FKJOBROLECODE = g.CODE AND j.ENDDATE = '" + @INFIXEDDATE + "' AND j.FKVALIDUSERCODE = k.CODE AND m.FKACTIONID = a.ID" +
-- " AND m.FKACTIVITYCODE = n.CODE AND m.CODE = p.FKACTIVITYCODE AND p.FKWORKPROCESSCODE = " + @LOCALWORKPROCESSCODE +
-- " AND p.VALUEDATE BETWEEN o.STARTWORKDATE AND o.ENDWORKDATE AND a.FKBRANCHCODE = o.BRANCHCODE" +
-- " AND g.CODE IN (" + @INJOBROLESLIST + &quot" +
-- " ORDER BY a.FKCUSTOMERCODE, a.FKCURRENCYCODE, a.FKPRODUCTCODE, a.FKLEGALENTITYCODE, a.ACCOUNTSUBREFERENCE"

-- end new code
-- start old code
SET @SQL = "SELECT a.ID AS a_ID, a.FKBRANCHCODE AS a_FKBRANCHCODE, a.FKPRODUCTCODE AS a_FKPRODUCTCODE, a.FKLEGALENTITYCODE AS a_FKLEGALENTITYCODE," +
" a.ACCOUNTSUBREFERENCE AS a_ACCOUNTSUBREFERENCE, a.FKLIMITID AS a_FKLIMITID, a.FKBUSINESSACTIONCODE AS a_FKBUSINESSACTIONCODE," +
" a.REDUCELIMIT AS a_REDUCELIMIT, a.ENTERINTERESTRATES AS a_ENTERINTERESTRATES, a.SCHEDULEDREPAYMENT AS a_SCHEDULEDREPAYMENT," +
" a.SCHEDULEAMOUNT AS a_SCHEDULEAMOUNT, a.SCHEDDATE AS a_SCHEDDATE, a.FUNDINGREFERENCE AS a_FUNDINGREFERENCE, a.VALUEDATE AS a_VALUEDATE," +
" b.DIRECTION AS b_DIRECTION, c.CODE AS c_CODE, c.NAME AS c_NAME, c.BANKCODE AS c_BANKCODE, c.NSCCODE AS c_NSCCODE," +
" c.CUSACCOUNTNO AS c_CUSACCOUNTNO, c.CONTACT AS c_CONTACT, c.PHONE AS c_PHONE, d.CODE AS d_CODE, d.SWIFTCODE AS d_SWIFTCODE," +
" d.DECIMALPLACES AS d_DECIMALPLACES, e.CODE AS e_CODE, e.TYPE AS e_TYPE, f.CODE AS f_CODE, f.SEQUENCE AS f_SEQUENCE," +
" g.CODE AS g_CODE, g.FKTEAMCODE AS g_FKTEAMCODE, h.CODE AS h_CODE, i.STARTDATE AS i_STARTDATE, i.MATURITYDATE AS i_MATURITYDATE," +
" i.CAPITALAMOUNT AS i_CAPITALAMOUNT, i.INTERESTAMOUNT AS i_INTERESTAMOUNT, i.INTERESTRATE AS i_INTERESTRATE," +
" i.COF AS i_COF, i.MARGIN AS i_MARGIN, i.RAC AS i_RAC, i.AOB AS i_AOB, i.FLOATINGRATE AS i_FLOATINGRATE," +
" i.NARRATIVE AS i_NARRATIVE, k.FIRSTNAME AS k_FIRSTNAME, k.LASTNAME AS k_LASTNAME" +
" FROM ACCOUNT_FOR_ACTION a, JOB_ROLE_ACCOUNT_FOR_ACTION b, CUSTOMER c, CURRENCY_INFO d, WORK_PROCESS e," +
" WORK_PROCESS_STAGE f, JOB_ROLE g, JOB_TYPE h, ACCOUNT_TERMS i, VALID_USER_JOB_ROLE j, VALID_USER k, TIME_PERIOD l" +
" WHERE a.ID = b.FKACTIONID AND b.ENDDATE = '" + @INFIXEDDATE + "' AND a.FKCUSTOMERCODE = c.CODE AND a.FKCURRENCYCODE = d.CODE" +
" AND a.FKWORKPROCESSCODE = e.CODE AND b.FKJOBROLECODE = g.CODE AND g.FKJOBTYPECODE = h.CODE AND h.CODE = f.FKJOBTYPECODE" +
" AND (h.CODE BETWEEN '" + @INFROMJOBTYPECODE + "' AND '" + @INTOJOBTYPECODE + "')" +
" AND (e.CODE BETWEEN " + @LOCALFROMWORKPROCESSCODE + " AND " + @LOCALTOWORKPROCESSCODE + &quot" +
" AND f.FKWORKPROCESSCODE = e.CODE AND i.FKACTIONID = a.ID AND i.TYPE = '" + @INACCTYPE + "' AND j.FKJOBROLECODE = g.CODE" +
" AND j.ENDDATE = '" + @INFIXEDDATE + "' AND j.FKVALIDUSERCODE = k.CODE AND l.ID = " + @LOCALTIMEPERIODCODE +
" AND ((DATEDIFF(D,'" +@INDATE + "',a.VALUEDATE) + (SELECT COUNT(*) FROM CALENDAR m WHERE A.FKBRANCHCODE = m.FKBRANCHCODE AND m.WORKDAY = 0 AND (m.DAYINYEAR between a.VALUEDATE AND '" + @INDATE + "'))" +
"- (SELECT COUNT(*) FROM CALENDAR m WHERE A.FKBRANCHCODE = m.FKBRANCHCODE AND m.WORKDAY = 0 AND (m.DAYINYEAR between '" + @INDATE + "' AND a.VALUEDATE))" +
" BETWEEN l.DISTANCEFROMTODAYSTART AND l.DISTANCEFROMTODAYEND))" +
" AND g.CODE IN (" + @INJOBLIST + &quot"
-- + " ORDER BY a.FKCUSTOMERCODE, a.FKCURRENCYCODE, a.FKPRODUCTCODE, a.FKLEGALENTITYCODE, a.ACCOUNTSUBREFERENCE"
-- end old code
x2:
EXEC (@SQL)
SET NOCOUNT OFF
END
RETURN

GO

 

[skynetosiris]

Ok,

thanks so much for your answer, just want last question (sorry im new on this), The SP is in other file or inside the vb code?

thanks again

 

[TomKane]

The SP is in SQL Server