Can anyone get this running? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can anyone get this running?

Author

Topic

Tks
Starting Member

48 Posts

Posted - 2010-11-21 : 14:22:27

Hi i have a small problem:


Declare @pdata nvarchar(1000);

Set @pdata= '125,256'

  Select top 1 pid,p1,p2,p3,p4 from PiecesClustered where P1 not in (@pdata)
  
Go

How can i overcome this problem?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-21 : 14:56:50

are the values int or varchar

Declare @pdata table (pdata int)
insert @pdata select '256'
insert @pdata select '125'

Select top 1 pid,p1,p2,p3,p4 from PiecesClustered where P1 not in (select pdata from @pdata)

or with your variable you could
where ',' + @pdata + ',' not like '%,' + convert(varchar(10),P1)+ ',%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-21 : 15:02:30

The values are int i'm still strugling on the bigger sql script.
I need to work with cursors...

the problem i have is that because of the fact i use a variable which is '' then sql says it cant convert from it.
In the debugger it says that @pdata = 123,345 but it gives the same error if i would change @pdata for '123,345'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-21 : 15:19:26

Did you read my post?
Try to understand datatypes.
You have a varchar and I suspect you are trying to compare it with an int. int's take precedence over varchars so the varchar will be converted to an int. As 123,345 con't be converted to an int it will give an error. The two options I gave are ways of doing this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-21 : 16:13:10

Offcourse i read everything you write for me. And i really appreciate your help.
What i try to do is to build a ',' sepp. list and then feed it into an NOT IN statement.

But because of the fact a string likes to start with a ' and ending with one.
the statement fails. saying that it cant convert a ',' list with starts with a ' and end with one.

[CODE]
Go
Declare @intflag int;
Declare @pdata varchar(1000);
Declare @SQL varchar(1500);

Set @pdata= '65,256'

Set @intflag = 0

While (@intFlag < 5)
Begin
print @intflag

Set @SQL = 'Select top 1 pid,p1,p2,p3,p4 from PiecesClustered where P1 not in (' + @pdata+ ')'

Exec(@sql)

Set @pdata= '65,256,255'

Set @intflag = @intflag + 1

End
Go
[/CODE]

in the code above you see my latest archievement.. now i need to implement this in a cursor.

then if i have my script we could look at making it with T-SQL.
saying that it only needs to run once!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-21 : 16:22:52

to repeat
Declare @pdata table (pdata int)
insert @pdata select '256'
insert @pdata select '125'

Select top 1 pid,p1,p2,p3,p4 from PiecesClustered where P1 not in (select pdata from @pdata)

or with your variable you could
where ',' + @pdata + ',' not like '%,' + convert(varchar(10),P1)+ ',%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-21 : 16:47:17

The OP would doesn't realize (apparently, IMHO) that the variable is being read as a string '125,256' NOT a comma separated list of integers.

The OP query as written is the same as saying Select... NOT IN ('125,246')

EDIT: appears he figured that out, but the answer isn't a while loop with dynamic SQL---I would imagine there are lots of better ways.

Poor planning on your part does not constitute an emergency on my part.

Tks
Starting Member

48 Posts

Posted - 2010-11-22 : 06:36:54

One should consider that my question is regarding getting this (eventually bad method) to work.
My problem is that i have not the knowledge to come up with a better method.
To be able to untherstand your method it looks like i need have more information regarding that method...

Nigel i will have to look in your method to see if it does the same and how it works.
On my first look i cant find the P1 column, except if your trick is to cast the column to nvarchar and compare it to my varchar list.

So, i cant dynamicly create a comma separated list of integer?
Isn't there such a thing that i can undo the comma from sql?
When i build my query dynamicly using another nvarchar var.. and the + symbol it does work!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 06:56:44

>> So, i cant dynamicly create a comma separated list of integer?
Yes you can and my query shows how it can be used.
You can't use a csv string for comparing to an integer - you either have to split the string in to integer values (see the table variable version) or compare it with a string.

For the string comparison you will need to convert the column values to strings and also include the delimitters.

so
'123,456,765'
if in the olumn you have the integer value 456

where 456 in ('123,456,765')
gives an error because '123,456,765' can't be conveted to an integer

where '123,456,765' like '%456%'
is close but due to no delimiters will only work if all values are 3 digits
to include the delimiters
where ',123,456,765,' like '%,456,%'

Now to build that up from your data
(remember 456 represents the column value P1 and 123,456,765 the csv string @pdata)
where ',' + @pdata + ',' like '%,' + convert(varchar(10),P1)+ ',%'
and you want a not in so
where ',' + @pdata + ',' not like '%,' + convert(varchar(10),P1)+ ',%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-22 : 11:27:42

SQL is a compiled language, and it uses scalar values for parameters. Now, read these two articles on how to do this.

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Tks
Starting Member

48 Posts

Posted - 2010-11-22 : 13:22:19

@nigel You are spot on!
I fully untherstand now what you are doing.

What will be faster?converting each column value to nvarchar and compare it with a stringlist?
Or the dynamic table approach?
In reality we have 4 p's and not one.

For now i go with the dynamic memory table (right???). I read that mem table is faster as real table.
Would the real table be faster if i put an index on the compare parameter?(which you cant on a mem table)?

Tks
Starting Member

48 Posts

Posted - 2010-11-22 : 14:09:41

Wow!
Joe thanks for those posts!

Tks
Starting Member

48 Posts

Posted - 2010-11-22 : 15:25:01

Guys,

i get an error on this line:

Set @sdata = @sdata + ',' + CAST(@Pid as nvarchar(20))

Msg 8115, Level 16, State 2, Line 88
Arithmetic overflow error converting expression to data type nvarchar.

But its delcared as nvarchar(2500). so space enough one would say.
it fails after 1,42854,84222 is succesful written. i gues the next digit is large (6 numbers).

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 15:32:49

It's an arithmetic overflow.

Are you sure it is that line? It doesn't seem to be doing much.
I would say it was because @pid was longer than 20 digits.
Why are you using nvarchar rather than varchar?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-23 : 15:40:46

stupid error... will use varchar but code still fails...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources