Query Problem

Hi All,
I am having one problem with the query. Please see below the query.
Declare @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000), --67330000, 67000000
Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
'67330000, 67000000'
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in (@.p_code_type_cd)
and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
When this query is executed it produces no result. I analyzed and found
that the query which is being executed is converted as mentioned
below:-
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in (3599,6023)
and code_val in (67330000, 67000000) and lang_cd = 39
i think it is missing few single quotes value. I am just how
to add these quotes in the query so that it becomes like..
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in ('3599','6023')
and code_val in ('67330000','67000000')and lang_cd = '39'Hi
You can not use an array in this way without using dynamic SQL see
http://www.sommarskog.se/arrays-in-sql.html
John
"neeju" wrote:

> Hi All,
> I am having one problem with the query. Please see below the query.
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000), --67330000, 67000000
> Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
> '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (@.p_code_type_cd)
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> When this query is executed it produces no result. I analyzed and found
> that the query which is being executed is converted as mentioned
> below:-
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (3599,6023)
> and code_val in (67330000, 67000000) and lang_cd = 39
> i think it is missing few single quotes value. I am just how
> to add these quotes in the query so that it becomes like..
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in ('3599','6023')
> and code_val in ('67330000','67000000')and lang_cd = '39'
>|||You can also do this with table variables, with only a few minor changes to
your code:
Declare @.p_lang_cd varchar (10)
/* Original variable assignment replaced with DECLARE ... TABLE
Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
'67330000, 67000000'
*/
SELECT @.p_lang_cd = '39'
DECLARE @.p_code_type_cd TABLE ( p_code_type_cd INT )
INSERT INTO @.p_code_type_cd VALUES ( '3599' )
INSERT INTO @.p_code_type_cd VALUES ( '6023' )
DECLARE @.p_code_val TABLE ( p_code_val INT )
INSERT INTO @.p_code_val VALUES ( '67330000' )
INSERT INTO @.p_code_val VALUES ( '67000000' )
/* Original query
select code_type_cd, code_val, desc_text
from code_tbl
where code_type_cd in (@.p_code_type_cd)
and code_val in (@.p_code_val)
and lang_cd = @.p_lang_cd
*/
-- Revised query
select code_type_cd, code_val, desc_text
from code_tbl
where code_type_cd in (SELECT * FROM @.p_code_type_cd)
and code_val in (SELECT * FROM @.p_code_val)
and lang_cd = @.p_lang_cd
-- Or this might be better
select code_type_cd, code_val, desc_text
from code_tbl c
INNER JOIN @.p_code_type_cd t ON c.code_type_cd = t.p_code_type_cd
INNER JOIN @.p_code_val v ON c.code_val = v.p_code_val
WHERE lang_cd = @.p_lang_cd
Untested! Let me know how you get on.
Damien
"neeju" wrote:

> Hi All,
> I am having one problem with the query. Please see below the query.
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000), --67330000, 67000000
> Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
> '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (@.p_code_type_cd)
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> When this query is executed it produces no result. I analyzed and found
> that the query which is being executed is converted as mentioned
> below:-
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (3599,6023)
> and code_val in (67330000, 67000000) and lang_cd = 39
> i think it is missing few single quotes value. I am just how
> to add these quotes in the query so that it becomes like..
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in ('3599','6023')
> and code_val in ('67330000','67000000')and lang_cd = '39'
>|||Thanks Damien,
Your code executes without any problem...
We have also updated the query and used temp table for storing the
values. But we were just looking for some logic
to change the values in IN operator using some TSQL function. We also
tried Replace function and replaces ',' with quotes in start and end of
both the values But that didn't produced the result.. We used like:-
Declare @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000),
@.delimeter char(1) --67330000, 67000000
-- @.test varchar (200)
Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
'39', @.p_code_val = '67330000, 67000000'
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in
(''''+replace(@.p_code_type_cd,@.delimeter
,'',''))
and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
Please let me know if any other thoughts,
Thanks,
NJ|||Hi
You can not do this without resorting to dynamic SQL. See
http://www.sommarskog.se/arrays-in-sql.html
DECLARE @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000),
@.delimeter char(1) --67330000, 67000000
DECLARE @.sqlstmt nvarchar(4000)
-- @.test varchar (200)
Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
'39', @.p_code_val = '67330000, 67000000'
SET @.sqlstmt = 'select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in
(' + @.p_code_type_cd + ')
and code_val in (' + @.p_code_val + ')and lang_cd = @.p_lang_cd'
SELECT @.sqlstmt
EXEC sp_executesql @.sqlstmt
John
"neeju" wrote:

> Thanks Damien,
> Your code executes without any problem...
> We have also updated the query and used temp table for storing the
> values. But we were just looking for some logic
> to change the values in IN operator using some TSQL function. We also
> tried Replace function and replaces ',' with quotes in start and end of
> both the values But that didn't produced the result.. We used like:-
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000),
> @.delimeter char(1) --67330000, 67000000
> -- @.test varchar (200)
> Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
> '39', @.p_code_val = '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in
> (''''+replace(@.p_code_type_cd,@.delimeter
,'',''))
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> Please let me know if any other thoughts,
> Thanks,
> NJ
>|||Yes John,
It appears so.. We tried no. of things but of no use..
I argued with the developers for not using dynamic sql and promised to
give some alternative statement withoug dynamic sql but nothing works.
Anyway thanks for directing to such a good article.
Thanks,
NJ|||Actually, you are missing a proper data model. Data element names like
"code_type_cd" are absurd. A code and a type are different kinds of
attributes, so you should have names like "postal_code" or
"blood_type" instead of a list of adjectives looking for a noun. Same
problem with "code_val"
What kind of code is in "code_tbl"? This name implies that you are
dealing with furniture. It must be ONE AND ONE KIND of code to be a
valid table. There is no such thing as a "Magical, Universal Does
Everything" code table in an RDBMS. Surely you have not mixed data and
metadata in a schema to build a OTLT or MUCK? Google those words and
start your research.
You also do not seem to know that SQL is compiled, so passing a string
is not like writing code on the fly in an interpreter. Without DDL and
sensible data element names, nobody can really help you. But based on
past experience, when the schema is bad, the kludge is usually dynamic
SQL.
The reason people give you that kludge is that it gets rid of you
faster than actually soving the root problems. That could take more
time and effort than we want to give away for free in a newsgroup.
Please get some real help somewhere else.|||Hi
This is where a CLR udt in SQL 2005 would probably be useful. The article
does show some methods that avoid using dynamic SQL.
John
"neeju" wrote:

> Yes John,
> It appears so.. We tried no. of things but of no use..
> I argued with the developers for not using dynamic sql and promised to
> give some alternative statement withoug dynamic sql but nothing works.
> Anyway thanks for directing to such a good article.
> Thanks,
> NJ
>sql