Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Why do you use variables in SQL?

    I use them in VB to loop through collections or set in VB with a message box to query, but in SQL why would you use a variable? I see people declaring and setting variables all the time but it doesn't seem like it's all that powerful of a technique in SQL.

    If there was a way to declare and then set the variable to a collection which would by default only find the unique values in the collection that would be pretty nice. But the variables are usually set to a static number or string.

    Can you use counters and loops to cycle through a variable in SQL?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by VLOOKUP
    Can you use counters and loops to cycle through a variable in SQL?
    Yes, but don't.

    You use variables in T-SQL for many of the same reasons you use variables in other languages. For example:
    [ol][li]When you have a value that shows up in many places in a query, or a set of queries.[/li]
    [li]To pass parameters to a stored procedure.[/li]
    [li]To pass values to a command like BACKUP DATABASE[/li][/ol]

    These are just a few of the uses of variables. They are a tool, just like any other. They may not be useful in every situation, but you need to know when they are useful, and use them responsibly.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I'm obviously not a super user nor have I had the luxury of being trained by experts. A lot of what I have learned is the product of a community college course, MS SQL book and 2 years of experience.

    Thanks for the examples, the first one is the only one I have used them for in SQL.

    I feel like the people in here are more like 5+ years of experience.

    Anyway sorry if the question was stupid.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    By definition, the only stupid question around DBForums is the one that you don't ask.

    I've spent several years trying to build DBForums into a place where people with character and experience hang out and help people with character but lacking in experience. For the most part, that's worked pretty well. With one notable exception (Joe Celko) who I've loved arguing with for years and so will tolerate his eccentricities, we pretty much make people behave themselves.

    I guess that I'm the "old man" around DBForums these days, but we all have a lot of both education and experience. For people like you who are trying to learn fast, that experience can be a gold mine that can shave years off the time it takes you to develop experience because you can profit from our mistakes and make much better choices than you would have made if you had to repeat our mistakes!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat thanks for the kind words and managing this forum.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Sorry if I came across as condescending, or anything. You are right that a lot of the regulars here are over 5 years experience. Even that whippersnapper GVee. Still, there's no minimum experience level to answering questions here, so feel free to chime in as well. It will seem intimidating at first, but it's generally worth it.

  7. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Just for the record I have used variables on a couple of large exception queries that had dates in there. It was far more user friendly to just change what the variable was set to, to another date for instance before kicking on the query.

    I use this for union all and union queries in a CTE or building temp tables.

    @MC no you didn't come off brash, I can take it no worries. I just realize the level of knowledge in here and I struggle to aggregate data sometimes in complex situations lol.

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I feel like the people in here are more like 5+ years of experience.
    Over 25 years , but you are missing something. SQL is a declarative (also known as functional programming) language. This family of languages does not use local variables. Read FUNCTIONAL PROGRAMMING THROUGH LAMBDA CALCULUS by Greg Michaelson for too much information.

    The goal is to solve the problem in one statement/expression. That is why we have nesting, CTE, orthogonality and strong typing in SQL.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    They were a lot meaner to me when I was new to this board (gulp, what I have done with my life) 11 years ago. Where is Mr. Lindman these days anyways?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Thrasymachus View Post
    They were a lot meaner to me when I was new to this board (gulp, what I have done with my life) 11 years ago. Where is Mr. Lindman these days anyways?
    Shut up and get back in your box, boy!

























    George
    Home | Blog

  11. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    It was far more user friendly to just change what the variable was set to, to another date for instance before kicking on the query.
    But that is a parameter and not a local variable. Subtle difference here because T-SQL's @ notation makes them look alike.

  12. #12
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Quote Originally Posted by Celko View Post
    But that is a parameter and not a local variable. Subtle difference here because T-SQL's @ notation makes them look alike.
    Parameters! Noted!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •