Solved

Passing values to run a store procedure

Posted on 2006-10-23
8
208 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

20 Experts available now in Live!

Get 1:1 Help Now