Solved

Passing values to run a store procedure

Posted on 2006-10-23
8
209 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now