Post reply

T-SQL

  • January 3, 2011 at 12:58 pm

    #1268648

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

    Thanks.

    Reji PR,
    Bangalore
    😀

  • January 3, 2011 at 11:14 pm

    #1268749

    rejipr1982 (1/3/2011)

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

    It's good to know that you learn some thing new. 🙂

    Thanks

  • January 3, 2011 at 11:19 pm

    #1268750

    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.

    Thanks Hugo for covering me. I assume SQL 2008 still its better to mention SQL Server version when we post any query, article or QofD. Next time I will take care 🙂

    Thanks

  • January 4, 2011 at 12:08 am

    #1268763

    Im getting the error while running the query

    Server: Msg 8101, Level 16, State 1, Line 7

    An explicit value for the identity column in table '@student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  • January 4, 2011 at 12:16 am

    #1268770

    sathishmcc (1/4/2011)

    Im getting the error while running the query

    Server: Msg 8101, Level 16, State 1, Line 7

    An explicit value for the identity column in table '@student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Interesting. Did you just copy/paste the code or did you change anything?

    What version of SQL Server are you running?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 4, 2011 at 12:36 am

    #1268777

    SQL 2000

  • January 4, 2011 at 12:59 am

    #1268793

    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?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 4, 2011 at 1:24 am

    #1268801

    Thanks for your reply sir

  • January 4, 2011 at 7:06 am

    #1268922

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 4, 2011 at 7:42 am

    #1268960

    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.

  • January 4, 2011 at 7:49 am

    #1268967

    WayneS (1/4/2011)

    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!

  • January 4, 2011 at 9:22 pm

    #1269297

    Toreador (1/4/2011)

    WayneS (1/4/2011)

    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

  • January 5, 2011 at 6:16 am

    #1269445

    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

    Alberto De Rossi
    Microsoft Certified Solutions Associate - SQL Server
    Microsoft MVP - Data Platform
    Power BI User Group Lima - Community Leader

  • January 5, 2011 at 9:57 am

    #1269608

    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.

  • January 5, 2011 at 10:41 am

    #1269639

    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.

    Alberto De Rossi
    Microsoft Certified Solutions Associate - SQL Server
    Microsoft MVP - Data Platform
    Power BI User Group Lima - Community Leader

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply