Post reply

T-SQL

  • January 1, 2011 at 3:43 pm

    #230004

    Comments posted to this topic are about the item T-SQL

    Thanks

  • January 1, 2011 at 4:45 pm

    #1268275

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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 2, 2011 at 5:16 am

    #1268293

    Thanks, great question.

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

  • January 2, 2011 at 8:44 pm

    #1268352

    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 shows many posts where it's been used/referenced.

    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 3, 2011 at 2:50 am

    #1268385

    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,

    --------------------------------------------------------------------------------
    Mayank Parmar
    Software Engineer

    Clarion Technologies
    SEI CMMI Level 3 Company

    8th Floor, 803, GNFC info Tower,
    SG Highway, Ahmedabad - 380 054,
    Gujarat, India.
    www.clariontechnologies.co.in

    Email: mayank.parmar@clariontechnologies.co.in
    MSN : mayank.parmar@clariontechnologies.co.in
    Mobile: +91 9727748789
    --------------------------------------------------------------------------------

  • January 3, 2011 at 3:13 am

    #1268390

    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!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • January 3, 2011 at 5:20 am

    #1268407

    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 😉

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 3, 2011 at 7:06 am

    #1268464

    it is avery helpful Question ..

    regards all

    Eng.Heba Mahmoud

  • January 3, 2011 at 8:55 am

    #1268509

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 3, 2011 at 10:13 am

    #1268557

    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)

  • January 3, 2011 at 11:04 am

    #1268573

    Don Avery (1/3/2011)

    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.

    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:

    INSERT INTO @student

    VALUES( 'Hardy', 100);

    INSERT INTO @student

    VALUES ('Rocky', 98);

    INSERT INTO @student

    VALUES ('Panky', 99);

    I then hit the F5 button to execute, and I got the expected results.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 3, 2011 at 11:23 am

    #1268582

    Thanks for the question!

  • January 3, 2011 at 12:12 pm

    #1268621

    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.

  • January 3, 2011 at 12:25 pm

    #1268632

    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.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 3, 2011 at 12:28 pm

    #1268634

    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

Viewing 15 posts - 1 through 15 (of 38 total)

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