Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

SQL and Parameter Sets

Greetings,

I have a table with three numeric fields and I am trying to return a result set based specific values of one of these fields.

e.g.

Table

FIELD1
FIELD2
FIELD3

SQL

SELECT * FROM table
WHERE (CAST(FIELD3 AS CHAR(2))IN :Parameter)

I can pass through a single value to the :Parameter looking like this (10) and it works fine. If I pass through (10,20) I get an empty result set. I have tried all the various forms of quotes and nothing works.

Please help. This is urgent.

Thanks,

Robert Lancaster
0
RLancaster
Asked:
RLancaster
  • 3
  • 2
  • 2
  • +4
1 Solution
 
ShadowFaxCommented:
Hi Robert,

Why not just define the parameter as a numeric and use the following statement:
SELECT * FROM TABLE WHERE FIELD3 = :Parameter
0
 
kretzschmarCommented:
hi rlancester,

you can't use a set as parameter,
you've to build the query with or(s) in your where clause for each value un the set.

meikl
0
 
ITugayCommented:
Hi all,
meikl;)

Yes, it doesn't work, Meikl is right, I tried it before with all variants, without any success. There are only way to do is:

Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('select * from table where field in ('+**your set**+')';
Query1.Open;
....
----
Igor.
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!

 
geobulCommented:
Hi,

  You can use empty SQL property at design time.
  When you need to call the query simply build SQL statement at run time instead of using parameters:

var
  Parameter : String;
begin
  ...
  Parameter := '(10,20)';
  ...
  Query1.SQL.Clear;
  Query1.SQL.Add('SELECT * FROM table WHERE (CAST(FIELD3 AS CHAR(2))IN');
  Query1.SQL.Add(Parameter);
  Query1.Open;
  ...
end;

Regards, Geo
0
 
geobulCommented:
Well done Igor ;)  and faster :(
0
 
kretzschmarCommented:
yup of course,

if you build up the statement on the fly
you can do it as igor commented.

meikl
0
 
FelixinCommented:
Well, hello

What I use to do is to construct the skeleton of the SQL Query like a format string like this

MyQueryString = 'SELECT ListFields
FROM MyTable
WHERE Id IN %s';

When I want to run the query I make this

MyQuery.SQL.Text := Format (MyQueryString, [MyParameter]);

In my parameter you can make a call to a function converting your set into a string

function SetToString (MySet : TMySet) : string;

returning each member of the set separated by comma and the whole thing between parenthesis.
0
 
kubeerjaCommented:
try this if you are using MSSQL

put this in your query sql string :

declare @S varchar(50)
set @s= :xParam
set @s='SELECT * FROM table WHERE FIELD3 IN ' + @s
exec(@s)

define your parameter xParam as input string in the query params

then run your code

procedure TForm1.Button1Click(Sender: TObject);
begin
 with Query1 do
 begin
  ParamByName('xParam').AsString:='(10,20)' ;
  open ;
 end ;
end;
0
 
RLancasterAuthor Commented:
thanks!
0
 
ITugayCommented:
Felixin,
Good idea to use text formatting route.
-----
Igor.
0
 
FelixinCommented:
Thank you, Igor

It's not mine. I've learnt it from a consultant here in my company.

Sometimes it helps to make the code easier to read.

Felixin
0
 
ITugayCommented:
I'll going to use it. What about 20 points for you?
----
Igor.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now