How to Use Varchar with Int column

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,
MaheshIf 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|||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|||The method I suggested will work fine; all I meant to say was that
others might have a better suggestion than I. :)

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

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

Thanks,
Mahesh|||Mahesh (gaware@.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-...ist-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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Stu (stuart.ainsworth@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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|||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|||Mahesh (gaware@.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-...st-of-integers-

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

How to use TOP in DELETE

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

thk.

Hi,

1. not sure about this one...
2. delete top(100) [tablename] where ........

James Steele

|||

One approach for each question:

Q1:

In SQL Server 2005, we can use ROW_NUMBER() function

SELECT ROW_NUMBER() OVER(ORDER BY id ) AS Row_Number, * FROM yourTable WHERE Row_Number<=100 -- first 100

-----

WHERE Row_Number>100 and Row_Number<=200 --100-200

Q2:

DELETE

tbl_test1WHERE IDIN(

SELECT

TOP 100 IDFROM tbl_test1ORDERBY id)|||

To select the next 100 records, try using ROW_NUMBER(). You can get details from Books Online, or the article below shows how to use it for custom paging.

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

|||

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

New in SQL Server 2005, you can use TOP along with DELETE.

DELETE TOP (100) FROM yourTable

works!

But in SQL Server 2000, this one will not work.

In SQL Server 2000, use this instead:

DELETE yourTable FROM (SELECT TOP 10 * FROM yourTable) AS t1 WHERE yourTable.id=t1.id