Showing posts with label int. Show all posts
Showing posts with label int. Show all posts

Monday, March 26, 2012

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

Monday, March 19, 2012

How to use OLE DB Command to update a derived column

Hi,

In my SSIS package,

1. I read from a source namely SOURCE. SOURCE is defined like

CREATE TABLE SOURCE

( f1 int, f2 int, f3 int, id int)

, where f1 is unique and id is always null after reading.

2. pass it to a Slow Change Dimension control to separate the new records and changed records

Then for new records,

3. link to a Derived Column control to add in some new columns

4. link to a OLE DB Command control to update field id before write to destination

In Step 4, I use the command :

INSERT INTO global_id_pool

SELECT MAX(id) + 1

FROM global_id_pool

UPDATE SOURCE

SET id =

(SELECT MAX(id) FROM global_id_pool)

WHERE f1 = ?

Using the command, the parameter can't be recognised with an error reported in BIDS. So eventually I had to change the query to the following to make it work

UPDATE SOURCE

SET id =

(SELECT MAX(id) + 1 FROM global_id_pool)

INSERT INTO global_id_pool

SELECT MAX(id) + 1

FROM global_id_pool

I don't mind the parameter can't be added. But after running, I found id field in the table after the OLE DB Command control is still NULL while the field in the SOURCE table in database is updated. So it seems the OLE DB Command worked on database but the data in memory cache wasn't affected.

So I just wonder if there is way to UPDATE the cache in OLE DB Command control. Many thanks for any help.

When you update the database after you've already selected from it, you can't expect that updated data to show up in the data flow stream. (It's technically in a different transaction's scope)

Can't you just send the new records to a staging table? Then in a second data flow you read from that staging table, while joining to the global_id_pool table (in the SQL source statement), and then insert into the destination table?

Wednesday, March 7, 2012

how to use convert data type from int to char in select statement

when i insert data from outside
i use insert into ....select...
can i use IDENTITY ( seed ,increment ) in my statement.
for example i have table
aaaaa AAA
bbbbb BBB
ccccc CCC

can i insert into new table with data

1 aaaaa AAA
2 bbbbb BBB
3 ccccc CCC
thanks

You can use cast (or convert)

create table test
(
integerValue int
)
insert into test
select 1
union all
select 232525
union all
select 2325
union all
select 525
union all
select 99232525

select integerValue, cast(integerValue as varchar(10)) as varcharValue
from test

integerValue varcharValue

1 1

232525 232525

2325 2325

525 525

99232525 99232525

|||

Well, first, don't change the meaning of a thread...I already answered it as it was originally asked Smile


create table test
(
value char(5),
otherValue char(3)
)
insert into test
select 'aaaaa','AAA'
union all
select 'bbbbb','BBB'
union all
select 'ccccc','CCC'

You can do this:


select identity(int,1,1) as test2Id,
value,
otherValue
into test2
from test

Or if you just wanted numbers, you can do this (in 2005) to get control over the values.


select row_number() over (order by value) as test3Id,
value,
otherValue
into test3
from test
go

|||sorry, i have change my question. can you answer me.