itortu
asked on
use stored procedure from inside program
I have a very long sql query that i made into a stored procedure.
ii don't know how to call the stored procedure. i need to pass 3 parameters to it.
this is how the query was run from the program:
strSql = ""
strSql = strSql & " " & _
" SELECT 1 level , " & _
" ord.order_id , " & _
" ord.client , " & _
" ord.line_no , " & _
" ' ' dispatch_date , " & _
" -1 item_owner , " & _
" ' ' stock_location , " & _
" ' ' return_action , " & _
" ' ' item_class " & _
" FROM asodetail ord " & _
" WHERE ord.order_id = '" & m_strOrderNumber & "' " & _
" AND ord.client = '" & m_strClient & "' " & _
" AND ord.order_id NOT IN ( SELECT order_id FROM bretheader WHERE client = '" & m_strClient & "' ) "
strSql = strSql & " UNION ALL " & _
" SELECT 2 level , " & _
" ord.order_id , " & _
" ord.client , " & _
" ord.line_no , " & _
" ' ' return_action , " & _
" ' ' item_class " & _
" FROM asodetail ord LEFT JOIN algrelvalue fly ON ( ord.article_id = fly.article_id " & _
" AND ord.client = fly.client " & _
" AND fly.rel_attr_id = 'V7' ) " & _
" LEFT JOIN bhjpackageviewhighjump hj ON ( ord.order_id = hj.order_id " & _
" AND ord.client = hj.client " & _
" AND hj.job_id = " & m_lngHighJumpToAgressoJobI d & ") "
strSql = strSql & " " & _
" LEFT JOIN algudispatch dis ON ( ord.order_id = dis.order_id " & _
" AND ord.article_id = dis.article_id " & _
" AND dis.status = 4 ) " & _
" WHERE ( ( COALESCE ( CASE WHEN fly.rel_value = ' ' THEN 'NA' ELSE fly.rel_value END , 'NA' ) = 'NA' " & _
" AND ord.kit_type = '2' ) " & _
" OR ( ord.kit_type = '0' ) ) " & _
" AND ord.order_id = '" & m_strOrderNumber & "' " & _
" AND ord.client = '" & m_strClient & "' "
Set rstSelect = m_cnnAgresso.Execute(strSq l)
the name of the procedure and its parameters is:
proc_get_order_details ( @order_id int, @client char(2), @job_id int)
ii don't know how to call the stored procedure. i need to pass 3 parameters to it.
this is how the query was run from the program:
strSql = ""
strSql = strSql & " " & _
" SELECT 1 level , " & _
" ord.order_id , " & _
" ord.client , " & _
" ord.line_no , " & _
" ' ' dispatch_date , " & _
" -1 item_owner , " & _
" ' ' stock_location , " & _
" ' ' return_action , " & _
" ' ' item_class " & _
" FROM asodetail ord " & _
" WHERE ord.order_id = '" & m_strOrderNumber & "' " & _
" AND ord.client = '" & m_strClient & "' " & _
" AND ord.order_id NOT IN ( SELECT order_id FROM bretheader WHERE client = '" & m_strClient & "' ) "
strSql = strSql & " UNION ALL " & _
" SELECT 2 level , " & _
" ord.order_id , " & _
" ord.client , " & _
" ord.line_no , " & _
" ' ' return_action , " & _
" ' ' item_class " & _
" FROM asodetail ord LEFT JOIN algrelvalue fly ON ( ord.article_id = fly.article_id " & _
" AND ord.client = fly.client " & _
" AND fly.rel_attr_id = 'V7' ) " & _
" LEFT JOIN bhjpackageviewhighjump hj ON ( ord.order_id = hj.order_id " & _
" AND ord.client = hj.client " & _
" AND hj.job_id = " & m_lngHighJumpToAgressoJobI
strSql = strSql & " " & _
" LEFT JOIN algudispatch dis ON ( ord.order_id = dis.order_id " & _
" AND ord.article_id = dis.article_id " & _
" AND dis.status = 4 ) " & _
" WHERE ( ( COALESCE ( CASE WHEN fly.rel_value = ' ' THEN 'NA' ELSE fly.rel_value END , 'NA' ) = 'NA' " & _
" AND ord.kit_type = '2' ) " & _
" OR ( ord.kit_type = '0' ) ) " & _
" AND ord.order_id = '" & m_strOrderNumber & "' " & _
" AND ord.client = '" & m_strClient & "' "
Set rstSelect = m_cnnAgresso.Execute(strSq
the name of the procedure and its parameters is:
proc_get_order_details ( @order_id int, @client char(2), @job_id int)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it to work doing it like this:
strSql = "proc_get_order_details '" & m_strOrderNumber & "', '" & m_strClient & "', " & m_lngHighJumpToAgressoJobI d
Set rstSelect = m_cnnAgresso.Execute(strSq l)
I was formating the string incorrectly.
strSql = "proc_get_order_details '" & m_strOrderNumber & "', '" & m_strClient & "', " & m_lngHighJumpToAgressoJobI
Set rstSelect = m_cnnAgresso.Execute(strSq
I was formating the string incorrectly.
Glad it worked.
NG,
NG,
ASKER
strSql = ""
strSql = "Call proc_get_order_details ('" & m_strOrderNumber & "','" & m_strClient & "','" & m_lngHighJumpToAgressoJobI
Set rstSelect = m_cnnAgresso.Execute(strSq
does it look correct to you?