未加星标

Working with TSQL Variables in Stored Procedures

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

I recently put together a lesson on how to work with TSQL variables. It is part of my Stored Procedures Unpacked course . When you create stored procedures, you can encapsulate logic securely, and part of this process is using TSQL variables to temporarily store and manipulate values.

After watching this video you’ll be able to declare TSQL variables and understand how they are used in expressions.You’ll find out isn’t as hard as everyone makes them out to be and you’ll have one of the skills a junior DBA should know .

You can learn more about this this video, it’s transcript follows:

If you want to learn more about how to define tsql variables, why not take my course Stored Procedures Unpacked ? Use coupon code BLOGPOST-201 to get it at a significant discount.


Working with TSQL Variables in Stored Procedures
Stored Procedures Unpacked Defining TSQL Variables

Now it’s time to start working with tsql variables.

What is a tsql variable? A tsql variable can hold a value of a specific type. A type is a date or an integer, like a whole number or a decimal. TSQL variables can be used to work in mathematical expressions like adding values together or to hold text and combine text.

They can be used as part of the equations. Variables are used to store information to participate in calculations. Variables are also part of our SQL and used instored procedures to be part of our parameters.

In order to use a TSQL variable we need to declare it. We use the declare command. Here we have a couple examples. We put an @ sign in front of the variable. Here I have “declare@countint” and then “;”. So I’m saying declare the variable count as an integer. I can also say, declare city as a varchar 40 and then give it an initial value of Ann Arbor.

This is a handy way of allowing you to not only declare the variable, but also set it up with a value right off the bat. Varchar again is a data type that allows you to have multiple characters in one string.

Again, I’m assuming that you understand what data types are. They’re part of an introductory SQL class. If you don’t understand datatypes, I do have information on that at my website. I have a reference to thats in this lesson.

To assign a value to a variable, you could do that in the DECLARE statement or you can also use the set command. If I had a variable called height and I wanted to set it to a value of 10.5, I could say set height equal 10.5. Or I could even use an expression like I want to set weight to a base weight and then multiply it by a value of 1.05. Here I have an instance where I’m setting my height to a literal value. In another case I am setting my variable to an expression.

There’s a couple of ways I can use variables in SQL. One way we’ve seen is just to create a simple expression where I’m multiplying some values. I can use also variables within an SQL select statement. But here I have a variable called PersonID and it’s participating in the SQL statement as part of the query. In fact, it’s part of the criteria where it’s going to be the business entity ID. So we’re gonna say we are the business entity ID at the person table is going to equal the contents of this person ID variable.

That becomes very powerful, especially when we’re working with stored procedures and parameters and we’re bringing in values and those values are in the variable. Then we can make our SQL a little more dynamic.

Let’s go look at some quick examples of how we use variables to further show you how this is done.

If you want to learn more about how to create stored procedures, why not take my course Stored Procedures Unpacked ? Use coupon code BLOGPOST-201 to get it at a significant discount.


Working with TSQL Variables in Stored Procedures
Stored Procedures Unpacked

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Working with TSQL Variables in Stored Procedures
本站链接:https://www.codesec.net/view/610952.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(92)