|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > HELP!! Sql parameter IN clause error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
HELP!! Sql parameter IN clause error
|
|
|
| SELECT Country_Ext_Code FROM Countries_Extensions WHERE=20
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM=20
rates_phone_zones WHERE Rate_Group_Phone_Code =3D ?)))=20
doesn't works.
SELECT Country_Ext_Code FROM Countries_Extensions WHERE=20
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM=20
rates_phone_zones WHERE Rate_Group_Phone_Code =3D 1))) works=20
fine.
* Note the change "?" x "1"
? is the parameter.
Generally, if you try the sql sentence with the ? in the=20
SQL Manager, the parameter windows should be appears=20
waiting input a value (ej. 1), but with the descripted=20
query (with ?), SQL generate an error; with 1 value works=20
fine. See TABLES DEFINITION
If you try only "SELECT country_ext_code FROM=20
rates_phone_zones WHERE Rate_Group_Phone_Code =3D ?)" this=20
works fine in the query analizer.
The question is -Can be used the IN (SELECT ... clause=20
with a parameter ?-
With the ? the SQL engine say: Syntax error or access=20
violation =20
Invalid descriptor index
TABLES DEFINITION
TABLE Countries_Extensions
country_ext_code(PK) country_name
1 USA
297 ARUBA
54 ARGENTINA
TABLE Rates_Phone_Zones
country_ext_code(FK) Rate_Group_Phone_Code
1 1
297 1
The query should be show:
Countries_Extensions.country_ext_code 54
in this case the ? is the Rate_Group_Phone_Code value from=20
a user input (ej. Rate_Group_Phone_Code 1).
I need to show all the countries extensions codes(really=20
just the countries names) that does=B4t exists in the=20
Rates_Phone_Zones.
| |
| Mikhail Berlyant 2002-10-05, 9:12 pm |
| > If you try only "SELECT country_ext_code FROM
> rates_phone_zones WHERE Rate_Group_Phone_Code = ?)" this
> works fine in the query analizer.
what are you talking about?!
what do you mean by "works fine in the query analizer"?
--
Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience http://launch.yahoo.com
Brainbench MVP for Visual Basic www.brainbench.com
"toki" <pedorro77@hotmail.com> wrote in message
news:418d01c260ff$f2f51470$2ae
2c90a@phx.gbl...
SELECT Country_Ext_Code FROM Countries_Extensions WHERE
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = ?)))
doesn't works.
SELECT Country_Ext_Code FROM Countries_Extensions WHERE
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = 1))) works
fine.
* Note the change "?" x "1"
? is the parameter.
Generally, if you try the sql sentence with the ? in the
SQL Manager, the parameter windows should be appears
waiting input a value (ej. 1), but with the descripted
query (with ?), SQL generate an error; with 1 value works
fine. See TABLES DEFINITION
If you try only "SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = ?)" this
works fine in the query analizer.
The question is -Can be used the IN (SELECT ... clause
with a parameter ?-
With the ? the SQL engine say: Syntax error or access
violation
Invalid descriptor index
TABLES DEFINITION
TABLE Countries_Extensions
country_ext_code(PK) country_name
1 USA
297 ARUBA
54 ARGENTINA
TABLE Rates_Phone_Zones
country_ext_code(FK) Rate_Group_Phone_Code
1 1
297 1
The query should be show:
Countries_Extensions.country_ext_code 54
in this case the ? is the Rate_Group_Phone_Code value from
a user input (ej. Rate_Group_Phone_Code 1).
I need to show all the countries extensions codes(really
just the countries names) that does´t exists in the
Rates_Phone_Zones.
| |
| mountain man 2002-10-05, 9:12 pm |
| You seek the general sql parameter
Try @Question_Mark
as the parameter
or @param
or @whatever
Best wishes,
--
Farmer Brown
Falls Creek, OZ
http://www.mountainman.com.au/software
"toki" <pedorro77@hotmail.com> wrote in message
news:418d01c260ff$f2f51470$2ae
2c90a@phx.gbl...
SELECT Country_Ext_Code FROM Countries_Extensions WHERE
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = ?)))
doesn't works.
SELECT Country_Ext_Code FROM Countries_Extensions WHERE
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = 1))) works
fine.
* Note the change "?" x "1"
? is the parameter.
Generally, if you try the sql sentence with the ? in the
SQL Manager, the parameter windows should be appears
waiting input a value (ej. 1), but with the descripted
query (with ?), SQL generate an error; with 1 value works
fine. See TABLES DEFINITION
If you try only "SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = ?)" this
works fine in the query analizer.
The question is -Can be used the IN (SELECT ... clause
with a parameter ?-
With the ? the SQL engine say: Syntax error or access
violation
Invalid descriptor index
TABLES DEFINITION
TABLE Countries_Extensions
country_ext_code(PK) country_name
1 USA
297 ARUBA
54 ARGENTINA
TABLE Rates_Phone_Zones
country_ext_code(FK) Rate_Group_Phone_Code
1 1
297 1
The query should be show:
Countries_Extensions.country_ext_code 54
in this case the ? is the Rate_Group_Phone_Code value from
a user input (ej. Rate_Group_Phone_Code 1).
I need to show all the countries extensions codes(really
just the countries names) that does´t exists in the
Rates_Phone_Zones.
| |
| Wayne Snyder 2002-10-05, 9:12 pm |
| Just use a normal SQL parameter... ie
declare @code int
select @code = 1
SELECT Country_Ext_Code FROM Countries_Extensions WHERE
(NOT (Country_Ext_Code IN (SELECT country_ext_code FROM
rates_phone_zones WHERE Rate_Group_Phone_Code = @code)))
You may wish to put the query in a stored procedure and pass the parameter
in.
--
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
I support the Professional Association for SQL Server (PASS) and its user
community of SQL Server Professionals.
www.sqlpass.org
|
|
|
|
|