| 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 procedurenow for this parameter i have to select multi selection list so that i have changed in the report from single to multi selecthere 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 ENDcan anyone tell me how to delete multiple lists instead of this single....the above code is for handling ALL we cant select ALLwith another list right?...atlaaaaaaaa |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
atlachar123456
Starting Member
33 Posts |
Posted - 2011-11-17 : 13:28:30
|
| Hi, Actually here i think no need to write a parsing stringIF(@supervisorId IS NOT NULL) AND (@supervisorId <> 0) BEGIN DELETE FROM #Rpt22036WorkTable WHERE ISNULL(supervisorId,0) <> @supervisorId ENDfrom 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 |
 |
|
|
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=168040http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168039 |
|
|
|
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 stringIF(@supervisorId IS NOT NULL) AND (@supervisorId <> 0) BEGIN DELETE FROM #Rpt22036WorkTable WHERE ISNULL(supervisorId,0) <> @supervisorId ENDfrom 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. |
|
|
|
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 INoption hereatlaaaaaaaa |
|
|
|
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 RETURNENDGO[/code]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
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.aspxFrom 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=168039If 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? |
|
|
|
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 ENDhere in the above code its deleting single selected ;if the procedure is executed for single @supervisorid then its deleting others in the listso my question is if i want to delete multiple in that list instead of single..can i useelsebeginset @supervisorId = '1,2,3,4' or set @supervisorId =','set @supervisorId = "" + @supervisorId+ ""select Replace(@supervisorId,',',"','")select * from #Rpt22036WorkTablewhere supervisorId IN (@supervisorId)instead of writing function i am just replacing comma with codes.....is this way correct?atlaaaaaaaa |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
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 ShawSQL Server MVP |
|
|
|
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 ENDhere in the above code its deleting single selected ;if the procedure is executed for single @supervisorid then its deleting others in the listso my question is if i want to delete multiple in that list instead of single..can i useelsebeginset @supervisorId = '1,2,3,4' or set @supervisorId =','set @supervisorId = "" + @supervisorId+ ""select Replace(@supervisorId,',',"','")select * from #Rpt22036WorkTablewhere 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?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
multi selection list