SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hardik Doshi / T-SQL / Latest Posts

RE: T-SQL

michael.kaufmann — Wed, 19 Jan 2011 05:10:49 GMT

Hello Hugo,as it's obvious, I haven't had too many chances using variables (except for variables carrying exactly one distinct value)--hence I was under the impression the contents of the variable is used.Thank you very much for your kind and precise explanation.Regards,Michael

RE: T-SQL

Hugo Kornelis — Wed, 19 Jan 2011 03:02:23 GMT

[quote][b]michael.kaufmann (1/18/2011)[/b][hr]I don't understand why the following code is not working (using SQL Server 2005; pasted just my amended query--you'll need the original one, too):[code="plain"]--Select 3SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''select @inputValue -- added to view the output of the variableSELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working[/code][/quote]It is working, just not as you expect it. If you look at the code above, you'll see that the IN list consists of exactly one member - the variable @inputValue. SQL Server will not care or interpret the content of that variable, but simply search the @student table for rows with a StudentName that matches that value - so you get all rows returned for students whose name is equal to [font="Courier New"]'Hardy', 'Rocky'[/font] (and I hope for their sake that no student has been given that name!)To get SQL Server to find both Hardy and Rocky, you need to supply two arguments, seperated by a comma. The comma has to be in the IN list, not in the contents of the variables.[code="plain"]--Select 3SET @inputValue1 = 'Hardy';SET @inputValue2 = 'Rocky';--select @inputValue -- added to view the output of the variableSELECT * FROM @student WHERE StudentName IN (@inputValue1, @inputValue2);[/code]

RE: T-SQL

michael.kaufmann — Tue, 18 Jan 2011 03:49:15 GMT

Thanks for the question.It may sound stupid to the more experienced users, but I don't understand why the following code is not working (using SQL Server 2005; pasted just my amended query--you'll need the original one, too):[code="plain"]--Select 3SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''select @inputValue -- added to view the output of the variableSELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working[/code]Because if you write out the query without the variable, it will return the expected 2 records.[code="plain"]SELECT * FROM @student WHERE StudentName IN ('Hardy', 'Rocky') -- working OK[/code]Any hints / explanations are welcome.Thanks in advance,Michael

RE: T-SQL

Hardy21 — Sun, 16 Jan 2011 21:37:57 GMT

[quote][b]amit_adarsh (1/14/2011)[/b][hr]I think this part is not correct INSERT INTO @student VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99) it will throw error.[/quote]This is SQL Server 2008 syntax.If you want to execute it in SQL 2005 then use the following:[code="sql"]INSERT INTO @student VALUES ( 'Hardy', 100)INSERT INTO @student VALUES ('Rocky', 98)INSERT INTO @student VALUES ('Panky', 99) [/code]

RE: T-SQL

amit_adarsh — Fri, 14 Jan 2011 03:55:59 GMT

I think this part is not correct INSERT INTO @student VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99) it will throw error.

RE: T-SQL

dr.kusnadi — Tue, 11 Jan 2011 19:27:54 GMT

the question should include SQL versions. the following does not work prior to 2008.INSERT INTO @student VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

RE: T-SQL

sfellner — Mon, 10 Jan 2011 08:05:50 GMT

it is because in the sample the delimiter became now comma and space.both possibilities will work:[code="sql"]SET @inputValue = 'Hardy, Rocky, ardy'SELECT * FROM @student WHERE CHARINDEX(', '+StudentName+',', ', '+@inputValue+',') > 0SET @inputValue = 'Hardy,Rocky,ardy'SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0[/code]HTH Stefan

RE: T-SQL

Hardy21 — Mon, 10 Jan 2011 07:49:31 GMT

@sfellner : If you modify Select 2 as follows:[code="sql"]--Select 2SET @inputValue = 'Hardy, Rocky, ardy'SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0[/code]It will only return 'Hardy'.

RE: T-SQL

sfellner — Mon, 10 Jan 2011 03:16:11 GMT

How about adding delimiters? Avoiding ardy and ocky.[code="sql"]DECLARE @inputValue NVARCHAR(20)DECLARE @student TABLE(Id INT PRIMARY KEY IDENTITY(1,1),StudentName NVARCHAR(50),StudentResult INT)INSERT INTO @studentVALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('ardy', 97), ('ocky', 96)--Select 1SET @inputValue = 'Hardy'SELECT * FROM @student WHERE StudentName IN (@inputValue)--Select 2SET @inputValue = 'Hardy,Rocky'SELECT * FROM @student WHERE CHARINDEX(','+StudentName+',', ','+@inputValue+',') > 0[/code]

RE: T-SQL

Alberto dbLearner — Wed, 05 Jan 2011 10:41:40 GMT

[quote][b]UMG Developer (1/5/2011)[/b][hr][quote][b]Alberto IT (1/5/2011)[/b][hr]To complicated. Yo can solve it using CHARINDEX:[/quote]As long as you don't mind incorrect results:[code="sql"]DECLARE @inputValue NVARCHAR(20)DECLARE @student TABLE(Id INT PRIMARY KEY IDENTITY(1,1),StudentName NVARCHAR(50),StudentResult INT)INSERT INTO @studentVALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('Ardy', 99)--Select 1SET @inputValue = 'Hardy'SELECT * FROM @student WHERE StudentName IN (@inputValue)--Select 2SET @inputValue = 'Hardy,Rocky'SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0[/code]Ardy shouldn't be returned but it is.[/quote]Good point! CHARINDEX should work only with short and controled set of data.

RE: T-SQL

UMG Developer — Wed, 05 Jan 2011 09:57:04 GMT

[quote][b]Alberto IT (1/5/2011)[/b][hr]To complicated. Yo can solve it using CHARINDEX:[/quote]As long as you don't mind incorrect results:[code="sql"]DECLARE @inputValue NVARCHAR(20)DECLARE @student TABLE(Id INT PRIMARY KEY IDENTITY(1,1),StudentName NVARCHAR(50),StudentResult INT)INSERT INTO @studentVALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('Ardy', 99)--Select 1SET @inputValue = 'Hardy'SELECT * FROM @student WHERE StudentName IN (@inputValue)--Select 2SET @inputValue = 'Hardy,Rocky'SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0[/code]Ardy shouldn't be returned but it is.

RE: T-SQL

Alberto dbLearner — Wed, 05 Jan 2011 06:16:16 GMT

To complicated. Yo can solve it using CHARINDEX:DECLARE @inputValue NVARCHAR(20)DECLARE @student TABLE(Id INT PRIMARY KEY IDENTITY(1,1),StudentName NVARCHAR(50),StudentResult INT)INSERT INTO @studentVALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)--Select 1SET @inputValue = 'Hardy'SELECT * FROM @student WHERE StudentName IN (@inputValue)--Select 2SET @inputValue = 'Hardy,Rocky'SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0Saluti

RE: T-SQL

Hardy21 — Tue, 04 Jan 2011 21:22:25 GMT

[quote][b]Toreador (1/4/2011)[/b][hr][quote][b]WayneS (1/4/2011)[/b][hr][quote]I think that it's upon the QotD authors to [i]attempt[/i] to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.[/quote]I can't see any point in going back before SQL2005, as that's the oldest supported version. How many of us even have 2000 installed any more? I don't so couldn't test against it even if I wanted to :-)And no, I haven't submited a QotD yet either - but did write half of one before someone else got there first with a much better effort![/quote]As a author, I also assume SQL 2008 because I read in one post from Steve that we should assume SQL 2008 if it is not mentioned. I used new syntax to insert the records for those who don't know the new syntax and they can have idea about that. Still its better to mention the SQL Server version when you post the QofD/article, next time I will take care :-)

RE: T-SQL

Toreador — Tue, 04 Jan 2011 07:49:31 GMT

[quote][b]WayneS (1/4/2011)[/b][hr][quote]I think that it's upon the QotD authors to [i]attempt[/i] to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.[/quote]I can't see any point in going back before SQL2005, as that's the oldest supported version. How many of us even have 2000 installed any more? I don't so couldn't test against it even if I wanted to :-)And no, I haven't submited a QotD yet either - but did write half of one before someone else got there first with a much better effort!

RE: T-SQL

OCTom — Tue, 04 Jan 2011 07:42:53 GMT

[quote][b]WayneS (1/4/2011)[/b][hr][quote][b]Hugo Kornelis (1/3/2011)[/b][hr][quote][b]john.moreno (1/3/2011)[/b][hr][quote][b]Hugo Kornelis (1/3/2011)[/b][hr]After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005[/quote]Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.[/quote]Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.[/quote]And this gets into what I didn't like about this question - it was needlessly tied to a specific version of SQL. One simple change (replace the insert values (),(),() with separate insert statements) would have made this question applicable to versions back to SQL 2000. A second set of minor changes (replace the table variable with a temporary table; replace the nvarchar with varchar) would have made this question applicable to versions back to SQL 6.5 - if not even earlier. (And yes - I did just try this code on versions 6.5 and 2000!) It wouldn't have compromised the point that the question was making at all.I think that it's upon the QotD authors to [i]attempt[/i] to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.[/quote]Didn't Steve make a comment last year that we should assume SQL Server 2008 unless stated otherwise by the author? I though I remember reading that.If you want questions to be backward compatible to 2000, why not earlier than that? It would be nice, though, for authors to state on what version they ran their code.

RE: T-SQL

WayneS — Tue, 04 Jan 2011 07:06:18 GMT

[quote][b]Hugo Kornelis (1/3/2011)[/b][hr][quote][b]john.moreno (1/3/2011)[/b][hr][quote][b]Hugo Kornelis (1/3/2011)[/b][hr]After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005[/quote]Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.[/quote]Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.[/quote]And this gets into what I didn't like about this question - it was needlessly tied to a specific version of SQL. One simple change (replace the insert values (),(),() with separate insert statements) would have made this question applicable to versions back to SQL 2000. A second set of minor changes (replace the table variable with a temporary table; replace the nvarchar with varchar) would have made this question applicable to versions back to SQL 6.5 - if not even earlier. (And yes - I did just try this code on versions 6.5 and 2000!) It wouldn't have compromised the point that the question was making at all.I think that it's upon the QotD authors to [i]attempt[/i] to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.

RE: T-SQL

sathishmcc — Tue, 04 Jan 2011 01:24:04 GMT

Thanks for your reply sir

RE: T-SQL

Koen Verbeeck — Tue, 04 Jan 2011 00:59:31 GMT

[quote][b]sathishmcc (1/4/2011)[/b][hr]SQL 2000[/quote]It probably has something to do with the ancient version that you are using :-)However, I've set the compatability level to 80 on my test database and the query still worked with me. Maybe because I'm using a newer client tool?

RE: T-SQL

sathishmcc — Tue, 04 Jan 2011 00:36:21 GMT

SQL 2000

RE: T-SQL

Koen Verbeeck — Tue, 04 Jan 2011 00:16:38 GMT

[quote][b]sathishmcc (1/4/2011)[/b][hr]Im getting the error while running the queryServer: Msg 8101, Level 16, State 1, Line 7An explicit value for the identity column in table '@student' can only be specified when a column list is used and IDENTITY_INSERT is ON.[/quote]Interesting. Did you just copy/paste the code or did you change anything?What version of SQL Server are you running?

RE: T-SQL

sathishmcc — Tue, 04 Jan 2011 00:08:50 GMT

Im getting the error while running the queryServer: Msg 8101, Level 16, State 1, Line 7An explicit value for the identity column in table '@student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

RE: T-SQL

Hardy21 — Mon, 03 Jan 2011 23:19:45 GMT

RE: T-SQL

Hardy21 — Mon, 03 Jan 2011 23:14:21 GMT

[quote][b]rejipr1982 (1/3/2011)[/b][hr]Thanks.While working with this type of comma seperated string, first we need to split the string with the help of table valued function.We can pass the comma deliminated string as an argument to the table valued function.Also thanks for the insert statement provoded.I never used this mode of insert statement. :-)[/quote] It's good to know that you learn some thing new. :-)

RE: T-SQL

Reji PR — Mon, 03 Jan 2011 12:58:44 GMT

Thanks.While working with this type of comma seperated string, first we need to split the string with the help of table valued function.We can pass the comma deliminated string as an argument to the table valued function.Also thanks for the insert statement provoded.I never used this mode of insert statement. :-)

RE: T-SQL

Don Avery — Mon, 03 Jan 2011 12:28:05 GMT

You are correct - I'm an idiot!I guess I never tried inserting without column names being explicitly stated, but it does indeed work.don

RE: T-SQL

Hugo Kornelis — Mon, 03 Jan 2011 12:25:11 GMT

[quote][b]john.moreno (1/3/2011)[/b][hr][quote][b]Hugo Kornelis (1/3/2011)[/b][hr]After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005[/quote]Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.[/quote]Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.

RE: T-SQL

john.moreno — Mon, 03 Jan 2011 12:12:40 GMT

[quote][b]Hugo Kornelis (1/3/2011)[/b][hr]After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005[/quote]Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.

RE: T-SQL

UMG Developer — Mon, 03 Jan 2011 11:23:32 GMT

Thanks for the question!

RE: T-SQL

Hugo Kornelis — Mon, 03 Jan 2011 11:04:03 GMT

[quote][b]Don Avery (1/3/2011)[/b][hr]Actually, I'm right - this query will return 0 rows for both SELECT statements as it will never run - you cannot insert into a table with and Identity PK without specifying the column names on the INSERT query.[/quote]Actually, you are wrong. The script will run. You don't need to specify column names when inserting into a table with an IDENTITY column.After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005 - I replaced the single INSERT with three INSERT statements:[code="sql"]INSERT INTO @studentVALUES( 'Hardy', 100);INSERT INTO @studentVALUES ('Rocky', 98);INSERT INTO @studentVALUES ('Panky', 99);[/code]I then hit the F5 button to execute, and I got the expected results.

RE: T-SQL

Don Avery — Mon, 03 Jan 2011 10:13:47 GMT

Actually, I'm right - this query will return 0 rows for both SELECT statements as it will never run - you cannot insert into a table with and Identity PK without specifying the column names on the INSERT query.The script should have read:INSERT INTO @student (StudentName, StudentResult) VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

RE: T-SQL

SQLRNNR — Mon, 03 Jan 2011 08:55:34 GMT

Thanks for the question.

RE: T-SQL

hebahmahmoud — Mon, 03 Jan 2011 07:06:39 GMT

it is avery helpful Question ..regards all

RE: T-SQL

Hugo Kornelis — Mon, 03 Jan 2011 05:20:18 GMT

Great question!I see so many people posting to forums or newsgroups, asking why they can't use IN (@variable) in this way with a comma-delimited list in the variable, that it's good to enforce this knowledge with a question.And I'm glad to see that as of now, 80% of the people who answered have it right - I'm becoming a bit more optimistic about my fellow people now ;-)

RE: T-SQL

Nakul Vachhrajani — Mon, 03 Jan 2011 03:13:24 GMT

Very good, basic question, Hardik!This QotD reminds us to be cautious when working with the IN keyword, especially in dynamic SQL.Have a Happy New Year ahead!

RE: T-SQL

Mayank Parmar — Mon, 03 Jan 2011 02:50:27 GMT

Thanks Wayne :)I was aware of this issue since long time, but didn't had any solution. Thanks for sharing wonderful information with us.Regards,

RE: T-SQL

WayneS — Sun, 02 Jan 2011 20:44:56 GMT

Excellent question, thanks!The only thing that is missing is how to solve this type of problem - you need to split the items in the variable into their separate items, and return them as a table that can be joined or cross-applied to. Jeff Moden wrote (and several people here have contributed to) a "DelimitedSplit8K" function; a quick google search [url=http://www.google.com/search?q=DelimitedSplit8k+site%3Asqlservercentral.com][u]shows many posts where it's been used/referenced[/u][/url].

RE: T-SQL

Koen Verbeeck — Sun, 02 Jan 2011 05:16:32 GMT

Thanks, great question.

RE: T-SQL

bitbucket-25253 — Sat, 01 Jan 2011 16:45:14 GMT

Nice question, got it right on a hunch .... but still learned something .. thanks

T-SQL

Hardy21 — Sat, 01 Jan 2011 15:43:12 GMT

Comments posted to this topic are about the item [B]T-SQL[/B]