How to Use Varchar with Int column

[Mahesh]

Hi,
I am writing a SP in MSSQL. One of the parameted is VARCHAR type
which have comma seperated INT vlaues. I want to use this varible in
WHERE clause against INT type coloum. how do I achive this.

eg. DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs = '1,3,2'

Currently I am using it as
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)

CompanyTypeID is of INT type.
But I want to replace the consts with the Parameter passed as below

SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
(@CompanyTypeIDs) - but this is not giving any result.

Please let me know the solution.

Many Thanks,
Mahesh

[Stu]

If you want to continue along this path, then you need to construct
your entire SQL statement as a variable, and exec that, like the
following:

DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs = '1,3,2'

DECLARE @SQL nvarchar (4000)
SET @SQL = 'SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN ('
+ @CompanyTypeIDs + ') '

PRINT @SQL

EXEC(@SQL)

However, this might not be the best solution to your problem, as I'm
sure someone else on this group will point out.

Stu

[Mahesh]

Stu,
Thanks a lot for your promt response. Then what way do u suggest to
achive this. Actually I am getting @CompanyTypeIDs as aparameter to
SP. So, please suggest accordingly.

My PS is quite big and above is one of the statement. I am suppose to
insert the data recieived from above statement into #temp TABLE.

Thanks,
Mahesh

[Stu]

The method I suggested will work fine; all I meant to say was that
others might have a better suggestion than I. :)

Stu

[Mahesh]

Thanks Stu,
yeah, currently I am using method suggested by you and is working great
for me.

Mahesh

[Mahesh]

Curious to know if anyone have better way of achieving the above task.

Thanks,
Mahesh

[Erland Sommarskog]

Mahesh (ga****@gmail.com) writes:

I am writing a SP in MSSQL. One of the parameted is VARCHAR type
which have comma seperated INT vlaues. I want to use this varible in
WHERE clause against INT type coloum. how do I achive this.

eg. DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs = '1,3,2'

Currently I am using it as
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)

CompanyTypeID is of INT type.
But I want to replace the consts with the Parameter passed as below

SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
(@CompanyTypeIDs) - but this is not giving any result.

Using dynamic SQL as suggested by Stu is a possible solution, but is
one of the poorest ones.

Look at http://www.sommarskog.se/arrays-in-s...st-of-integers
for a quick solution. If you want to explore other solutions, read the
entire article (which is quite long).

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Stu (st**************@gmail.com) writes:

If you want to continue along this path, then you need to construct
your entire SQL statement as a variable,

NO!!!!!

This is a possible solution, but there is no "need" to use it. It is
in fact one of the poorest solutions there is.

Please look at http://www.sommarskog.se/arrays-in-sql.html for a whole
range of alternatives, and why dynamic SQL is so bad.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Stu]

OK, simmer down.....

I figured there was a better way, and I also figured you or some of the
other posters would have a solution. Note that I prefaced my
suggestion with the tentative "if you want to continue down this
path...", implying that I understood that there were some weaknesses
with it.

Stu

[Mahesh]

Yeah, Stu had mentioned it earlier about the performance of dynamic
SQL. Can anyone of you explain why Dynamic SQL is poor in performance?

Erland, actually my query consists for two such IN statements with AND
condition.

i.e WHERE StateTypeID in ('1,2,3,4.....') AND UserTypeID IN
('2,3,4.....')
StateTypeID and UserTypeID are of INT type. Could please let me know
the best way to perform the above query in SP.
Do I need to go for nested WHILE loop? what u feel is best way of doing
it

Thanks,
Mahesh

[Erland Sommarskog]

Mahesh (ga****@gmail.com) writes:

Yeah, Stu had mentioned it earlier about the performance of dynamic
SQL. Can anyone of you explain why Dynamic SQL is poor in performance?
When the list is long, it takes the optimizer very long time to build
the execution plan.
Erland, actually my query consists for two such IN statements with AND
condition.

i.e WHERE StateTypeID in ('1,2,3,4.....') AND UserTypeID IN
('2,3,4.....')
StateTypeID and UserTypeID are of INT type. Could please let me know
the best way to perform the above query in SP.
Do I need to go for nested WHILE loop? what u feel is best way of doing
it

Did you look at the link I posted? That should take you a long way.
http://www.sommarskog.se/arrays-in-s...t-of-integers-
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp