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
 General SQL Server Forums
 New to SQL Server Programming
 multi selection list

Author  Topic 

atlachar123456
Starting Member

33 Posts

Posted - 2011-11-17 : 12:52:19
Hi,
Can anyone correct this code..

I have an input parameter supervisorId as INTEGER;here i have to change to varchar(20) because in the report its there as principal
with name so that is a string,string in the text field where clause is not a good practice so we kept that as integer in procedure
now for this parameter i have to select multi selection list so that i have changed in the report from single to multi select

here is the code for deleting single selected list

IF(@supervisorId IS NOT NULL) AND (@supervisorId <> 0)
BEGIN
DELETE FROM #Rpt22036WorkTable
WHERE ISNULL(supervisorId,0) <> @supervisorId
END

can anyone tell me how to delete multiple lists instead of this single....the above code is for handling ALL we cant select ALL
with another list right?...

atlaaaaaaaa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 13:04:49
i think what you're after is string parsing function like below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

atlachar123456
Starting Member

33 Posts

Posted - 2011-11-17 : 13:28:30
Hi,
Actually here i think no need to write a parsing string
IF(@supervisorId IS NOT NULL) AND (@supervisorId <> 0)
BEGIN
DELETE FROM #Rpt22036WorkTable
WHERE ISNULL(supervisorId,0) <> @supervisorId
END

from the above code i am handling with ALL and deleting single selected id instead of this i want to delete multiple ids for this

can i use this statement as
WHERE ISNULL(supervisorId,0) IN (@supervisorId)
instead of <> can i use IN ?

atlaaaaaaaa
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-17 : 13:36:55
In the future please do not start multiple threads on the same topic. This is the third one on the same topic that I ran across. It makes it hard for people to help you when you do that. Additionally, we don't know what version of SQL you are actually using when you post in sub-forums for different versions of SQL.

Here are some of the other duplicate thread I came across:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168040
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168039
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-17 : 13:38:52
quote:
Originally posted by atlachar123456

Hi,
Actually here i think no need to write a parsing string
IF(@supervisorId IS NOT NULL) AND (@supervisorId <> 0)
BEGIN
DELETE FROM #Rpt22036WorkTable
WHERE ISNULL(supervisorId,0) <> @supervisorId
END

from the above code i am handling with ALL and deleting single selected id instead of this i want to delete multiple ids for this

can i use this statement as
WHERE ISNULL(supervisorId,0) IN (@supervisorId)
instead of <> can i use IN ?

atlaaaaaaaa

Is SupervisorID a list or a sinlge value? If so then sure you can uss IN. But <> and IN are not the same thing, so I'm confused.
Go to Top of Page

atlachar123456
Starting Member

33 Posts

Posted - 2011-11-17 : 13:57:29
supervisorId is a list it has many values in that list like sdgfh,jdudj,lsiwd,jshhdd etc....<> is used here because it was where ISNULL(supervisorId,0) here if supervisorid is null then we are substituting 0 means ALL to that; that is <> @supervisorId so its deleting single value from the list;if i want to delete multiple values can i use IN
option here

atlaaaaaaaa
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:19:25
[code]
CREATE FUNCTION [dbo].[udf_Table](@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE
(Parameter varchar(255))

AS

/*
SELECT * FROM dbo.udf_Table( 'a|~|b|~|c', '|~|')
*/

BEGIN
DECLARE @x int, @Parameter varchar(255)

WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
BEGIN
INSERT INTO @table(Parameter)
SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)


SELECT @ParmList = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+LEN(@Delim), LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
END
INSERT INTO @table(Parameter) SELECT @ParmList
RETURN
END
GO



[/code]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:23:08
[code]
SELECT *
FROM dbo.udf_Table( @supervisorId, ',') t1
LEFT JOIN #Rpt22036WorkTable t2
ON t1.Parameter = t2.upervisorId
[/code]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:25:19
quote:
Originally posted by atlachar123456
here if supervisorid is null then we are substituting 0 means ALL


You do know that makes no sense at all..right?

Do you mean @supervisorid = 0 then you want to delete all?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-17 : 15:15:30
A couple of things. It's a good idea to post DDL, DML and expected output. Here is a link that can help with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

From the other thread you started, here is a link to many ways of handling arrays in SQL:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168039

If you want a realy fast parse/split function, here is a link to one of the fastest ones to date:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

Fianlly, what version of SQL are you using and what is your reporting technology you are using?
Go to Top of Page

atlachar123456
Starting Member

33 Posts

Posted - 2011-11-17 : 15:16:07
Hi,
thanks for replying,but what i am expecting here is that
IF(@supervisorId IS NOT NULL) AND (@supervisorId <> ALL)
BEGIN
DELETE FROM #Rpt22036WorkTable
WHERE ISNULL(supervisorId,ALL) <> @supervisorId
END
here in the above code its deleting single selected ;if the procedure is executed for single @supervisorid then its deleting others in the list
so my question is if i want to delete multiple in that list instead of single..can i use
else
begin
set @supervisorId = '1,2,3,4' or set @supervisorId =','
set @supervisorId = "" + @supervisorId+ ""
select Replace(@supervisorId,',',"','")
select * from #Rpt22036WorkTable
where supervisorId IN (@supervisorId)

instead of writing function i am just replacing comma with codes.....is this way correct?

atlaaaaaaaa
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 15:45:18
I give up

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-17 : 17:10:37
No. that's not right at all. See all the split functions (which you DO need) that Brett nicely wrote for you. (not to mention you have assorted other syntax errors like @supervisorId <> ALL)


--
Gail Shaw
SQL Server MVP
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 00:49:19
quote:
Originally posted by atlachar123456

Hi,
thanks for replying,but what i am expecting here is that
IF(@supervisorId IS NOT NULL) AND (@supervisorId <> ALL)
BEGIN
DELETE FROM #Rpt22036WorkTable
WHERE ISNULL(supervisorId,ALL) <> @supervisorId
END
here in the above code its deleting single selected ;if the procedure is executed for single @supervisorid then its deleting others in the list
so my question is if i want to delete multiple in that list instead of single..can i use
else
begin
set @supervisorId = '1,2,3,4' or set @supervisorId =','
set @supervisorId = "" + @supervisorId+ ""
select Replace(@supervisorId,',',"','")
select * from #Rpt22036WorkTable
where supervisorId IN (@supervisorId)

instead of writing function i am just replacing comma with codes.....is this way correct?

atlaaaaaaaa



Really...do you want fries with that?


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 00:53:05
I gotta ask....its says you are from seeatle washington...where is you consulting group from and do they fly you in? or are you off shore? And what did you do before?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -