Solved

Passing values to run a store procedure

Posted on 2006-10-23
8
212 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
Independent Software Vendors: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using idhttp to login to instagram 2 118
Magic Software info 18 142
Delphi: Connect to running MS Outlook 4 160
Slow Restore if incremental backups using RDiff.exe 4 39
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…

735 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