Solved

Passing values to run a store procedure

Posted on 2006-10-23
8
215 Views
Last Modified: 2010-04-05
Hello guys,

I have a store procedure that will be run retrieving me a query. But before it retrieves me a query, some operation it will run and after the query is created. This query must be passed by parameter to be assemble.

I am getting error to pass this query,

This is the query:

'SELECT * FROM (
SELECT Contas, Descricao, Totaliza,
TOTAL =
IsNull(A.sete,0) ,
IsNull(A.sete,0) as ''setembro''
FROM ( Select *,
''sete''= Isnull((select Sum(ContSalContValor) from contsaldoanalitico WHERE
SubString(ContSalContCodigo,1,Len(Contas)) = Contas and
SubString(Convert(char(10),ContSalContData,112),1,6) = ''200609''),0)
From #TbRel) A
) B
WHERE B.TOTAL<>0
ORDER BY B.CONTAS'


 Here I try to pass the query
  ADOStoredProc1.Parameters.ParamByName('@Sql').Value := Str;
  ADOStoredProc1.Open;
0
Comment
Question by:hidrau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:cobi100
ID: 17789995
what is the error?
0
 
LVL 13

Accepted Solution

by:
BlackTigerX earned 500 total points
ID: 17790042
first of all, you mention a stored procedure but all I see is a query, so perhaps you are trying to run a query as stored procedure

you just need something like
ADOQuery myQuery;
...
myQuery.SQL.Text = 'SELECT * FROM (
SELECT Contas, Descricao, Totaliza,
TOTAL =
IsNull(A.sete,0) ,
IsNull(A.sete,0) as ''setembro''
FROM ( Select *,
''sete''= Isnull((select Sum(ContSalContValor) from contsaldoanalitico WHERE
SubString(ContSalContCodigo,1,Len(Contas)) = Contas and
SubString(Convert(char(10),ContSalContData,112),1,6) = ''200609''),0)
From #TbRel) A
) B
WHERE B.TOTAL<>0
ORDER BY B.CONTAS';

myQuery.Open();
0
 
LVL 1

Author Comment

by:hidrau
ID: 17790049
It don't run.

Sorry, but I had to give up this thread.

I need to close it

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:hidrau
ID: 17790059
BlackTigerX , it is a storeprocedure, I should pass by parameter this query.

but I had to give up :(
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 17792382
you are passing the text of a query as a parameter to a stored procedure?

not a good idea, you might as well just execute the query directly on your code
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 17817517
I agree with BlackTigerX here. Passing SQL code to a stored procedure is one of the worst developing errors that you can make in my opinion. Whomever came up with that idea should be [edit], [edit] and finally [edit] and never be allowed near any computer again...
0
 
LVL 1

Author Comment

by:hidrau
ID: 17818513
Ok Workshop_Alex , I changed my mind about it.

Thanks
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 17819074
You're welcome. Glad I could help. :-) But BlackTigetX deserves the credits...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question