T-SQL

  • Hardy21

    SSCrazy Eights

    Points: 9708

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

    Thanks

  • Ron McCullough

    SSC Guru

    Points: 63877

    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]

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Thanks, great question.

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

  • WayneS

    SSC Guru

    Points: 95386

    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

  • Mayank Parmar

    Ten Centuries

    Points: 1019

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

  • Nakul Vachhrajani

    SSChampion

    Points: 10220

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • hebahmahmoud

    Grasshopper

    Points: 24

    it is avery helpful Question ..

    regards all

    Eng.Heba Mahmoud

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Don Avery

    SSC Enthusiast

    Points: 138

    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)

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question!

  • john.moreno

    Default port

    Points: 1485

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Don Avery

    SSC Enthusiast

    Points: 138

    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 39 total)

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