Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
sathishmcc
sathishmcc
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 197
SQL 2000
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
sathishmcc (1/4/2011)
SQL 2000


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?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sathishmcc
sathishmcc
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 197
Thanks for your reply sir
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
Hugo Kornelis (1/3/2011)
john.moreno (1/3/2011)
Hugo Kornelis (1/3/2011)

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


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.

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.


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 attempt 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.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 4152
WayneS (1/4/2011)
Hugo Kornelis (1/3/2011)
john.moreno (1/3/2011)
Hugo Kornelis (1/3/2011)

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


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.

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.


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 attempt 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.


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.
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2413 Visits: 8062
WayneS (1/4/2011)
[quote]I think that it's upon the QotD authors to attempt 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.


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!
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
Toreador (1/4/2011)
WayneS (1/4/2011)
[quote]I think that it's upon the QotD authors to attempt 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.


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!

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 :-)

Thanks
Alberto dbLearner
Alberto dbLearner
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 227
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 @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0

Saluti
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Alberto IT (1/5/2011)
To complicated. Yo can solve it using CHARINDEX:


As long as you don't mind incorrect results:

DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('Ardy', 99)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0



Ardy shouldn't be returned but it is.
Alberto dbLearner
Alberto dbLearner
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 227
UMG Developer (1/5/2011)
Alberto IT (1/5/2011)
To complicated. Yo can solve it using CHARINDEX:


As long as you don't mind incorrect results:

DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('Ardy', 99)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0



Ardy shouldn't be returned but it is.


Good point! CHARINDEX should work only with short and controled set of data.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search