Although there are a few ways to call a stored procedure, only one way is actually recommended. The best way is to explicitly specify the stored procedure parameters in the Execute statement. You do this because if the order of the parameters were to change, then the procedure would error out (or even worse pass the incorrect values to the parameters). Let’s create a stored procedure as an example: [cc lang=”sql”] CREATE PROCEDURE spReturnFullName ( @FirstName varchar(50), @LastName varchar(50), @Age int, @ClientName varchar(100) OUTPUT ) AS SET NOCOUNT ON BEGIN SELECT @ClientName = @FirstName + @LastName END [/cc] Now that we have our procedure, let’s call the procedure using EXECUTE. We can also use the EXEC statement as a replacement for Execute, it is merely a simplified way to call the same statment. So below we are specifically specifying the parameters of the procedure. Unfortunately, SQL Server allows procedures to be executed without specifying the parameter name. The way we are doing it below is not only self-documenting, but as mentioned, it will not allow the wrong values to be passed to a parameter. [cc lang=”sql”] DECLARE @ClientNameReturn varchar(100) EXEC spReturnFullName @FirstName = ‘Vincent’, @LastName = ‘Vega’, @Age = 40, @ClientName = @ClientNameReturn OUTPUT SELECT @ClientNameReturn [/cc] This procedure also has an output parameter, which is denoted by the OUTPUT reserved word.
Continue reading ...
Concatenation in SQL Server is done by using the plus (+) operator. Let’s setup a table and look at an example by concatenating customer’s first names and last names: [cc lang=”sql”] — Create Customers table CREATE TABLE #Customers ( FirstName varchar(50), LastName varchar(50) ) INSERT INTO #Customers ( FirstName, LastName ) SELECT ‘Vincent’, ‘Vega’ UNION SELECT ‘Marsellus’, ‘Wallace’ UNION SELECT ‘Jules’, NULL — Concatenate FirstName and LastName SELECT FirstName + LastName FROM #Customers [/cc] Now when we run the output we will see something interesting. When we try to concatenate a null value with a string, we are returned with a NULL value. This shows the important need to use the ISNULL function when concatenating values. Let’s use the ISNULL function now to replace the NULL with an empty string: [cc lang=”sql”] SELECT ISNULL(FirstName, ”) + ISNULL(LastName, ”) FROM #Customers [/cc] Now we see that the we are returned with a string rather than a NULL.
There is not standard method for backing up a table in SQL Server. A common request I receive from clients is to only backup specific tables within a database. Well, unfortunately there is no out of the box method for doing this akin to a database backup so we need to roll our own way. It is usually done one of two ways. The first is to use a select into method to copy the table. The other way is to script the table out using the Generate Scripts Tasks. Both methods have positives and negatives. First off, the SELECT.. INTO Method is by far the fastest. It can copy a large number of rows very quickly, the downfall to this however, is that it does not carry over the Keys, Indexes or Constraints. The Generate Scripts method is slow (and I don’t recommend it for very large tables), however it can facilitate copying over any of the other objects associated with the table. Let’s look at the generate scripts task: First, right click on the database that contains the table you want to backup and choose Tasks -> Generate Scripts. The Generate Scripts Wizard Appears. Select “Next” past the splash screen then select the database that contains the table. The next screen that appears is the Script Options. Scroll down on the script options until you see Table/View Options. We want the following to be true: Check Constraints, Script Data, Foreign Keys, Primary Keys, Triggers, & Unique Keys. (Or […]
Continue reading ...
Backing up a database is relatively straight forward using SQL Server Management Studio. First open management studio and connect to the Database Server that contains the database you want to backup. Expand the “Databases” folder and right-click on the database you want to make a backup of. Choose Tasks -> Back Up… (now the Back up database dialog appears) From here, if you simply hit the “OK” button, your database will be backed up in the default location. However, let’s specify a location so we can find it. Select the “Remove” button to remove the current backup location Select the “Add” button and add a new location to backup the database to. Now choose a location on disk and give it a file name. It is good practice to include the date of the backup in the filename. You may also want to save the file name with a .BAK extension You can select “OK” and the backup will begin. You can optionally at this point, select the “Script” button at the top of the dialog in order to retrieve the script that SSMS uses to perform the backup. A new window will open and you will have your script. You can then use this script in a job, or run it yourself.
Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command. Adding a linked Server using the GUI There are two ways to add another SQL Server as a linked server. Using the first method, you need to specify the actual server name as the “linked server name”. What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name. This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server. I like to avoid this method even though it is easier to initially setup. The rest of the steps will guide you through setting up a linked server with a custom name: To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) The “New Linked Server” Dialog appears. (see below). For “Server Type” make sure “Other Data Source” is selected. (The SQL Server option will force you to specify the literal SQL Server Name) Type in a friendly name that describes your linked server (without spaces). I use AccountingServer. Provider – Select “Microsoft OLE DB Provider for SQL Server” Product Name – type: SQLSERVER (with no spaces) Datasource – type the actual […]
Continue reading ...
