Nugs
asked on
DataTable (xsd) select and wildcard parameters
Is it possible to pass a wildcart into a select statements required parameter to return any values found?
SELECT FLD_LOCID, FLD_NAME
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1)
And pass "*" as Param1? (* does not work by the way :))
Nugs
SELECT FLD_LOCID, FLD_NAME
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1)
And pass "*" as Param1? (* does not work by the way :))
Nugs
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OR are you saying you want to generate the where clause on the fly determined by previous logic.
Andrew
Andrew
ASKER
well not really, my code allow the data to be return by either the FLD_LOCID or FLD_NAME or both
SELECT FLD_LOCID, FLD_NAME
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1) AND (FLD_NAME = @Param2)
I need a way to pass a wildcard value into either Param1 or Param2 essentially only return records based on the other param (the one that is not wild)
I do not think there is a way of doing what i want here, i have never heard of wildcard values in a select statement (other the SELECT * FROM...) but thought it was worth a try to ask..
Nugs
SELECT FLD_LOCID, FLD_NAME
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1) AND (FLD_NAME = @Param2)
I need a way to pass a wildcard value into either Param1 or Param2 essentially only return records based on the other param (the one that is not wild)
I do not think there is a way of doing what i want here, i have never heard of wildcard values in a select statement (other the SELECT * FROM...) but thought it was worth a try to ask..
Nugs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
REA_ANDREW: that would be an option but i am using XSD datasets and tableadapters generated by VS2005... I don't have much control over the SQL statement from my codebehind (or at least i don't think i do)
ASKER
NickUpson: Trying that now, but what if the Params are int types??
ASKER
yeah it does not like % as it's parameter, FLD_LOCID is looking for a int value
I would suggest you to build the query at run time to inlcude the where clauses only if the values are not *
ASKER
I was hoping someone would not say that :(
Thanks,
Nugs
Thanks,
Nugs
SELECT FLD_LOCID, FLD_NAME
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1 or FLD_LOCID is null)
AND (FLD_NAME like @Param2or FLD_LOCID is null)
set the param to null when you want to have it effectively ignored
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1 or FLD_LOCID is null)
AND (FLD_NAME like @Param2or FLD_LOCID is null)
set the param to null when you want to have it effectively ignored
ASKER
there is no way to set a int type to null. int are value types and require a number value. Also you can not pass 'null' as the barameter as it is looking for a int, i get a "cannot convert from '<null>' to 'int'" error.
you can certainly define a column in a table as a nullable integer, so I assume that there is some way to use that inside delphi
ASKER
The actual select statement looks something like this:
SELECT FLD_LOCID, FLD_NAME
FROM TBL_LOCATIONS
WHERE (FLD_LOCID = @Param1) AND (FLD_NAME = @Param2)
now i my code there may be a senario where i want to get the results by using param2 and NOT param one... So essentially i want to be able to do this when calling the xsd tableadapter:
TBL_LocationAdapter.GetDat
or
TBL_LocationAdapter.GetDat
or
TBL_LocationAdapter.GetDat
Essentially i want to be able to pass a wildcard value inot the param1 or param2 that will return all record or essentially skip that perticular filter...
Possible?
Nugs