When I had originally written this article, I was very excited that this would be an answer to a long-standing wish that I have hope SQL Server would support. I wished I could call a stored procedure without knowing what was in the result set and dump it to a table (or temp table). Currently in order to trap the result set from executing a procedure we need to perform work arounds – all because we do not dynamically know the columns and data types the procedure is returning. If there was a way SQL could facilitate this without a work-around, it would take SQL to a new level of abstraction and modularity. The WITH RESULT SETS feature does allow us to change the column names and datatypes of the result set a stored procedure returns, however it does not allow us to define a subset of columns to return. Therefore, if a new column is added to the result set of a procedure that is called using WITH RESULT SETS, an error will occur. The SQL Server Denali CTP can be downloaded here. Let’s take a look at this new feature, first we’ll create a sample stored procedure: [cc lang=”sql”] CREATE PROCEDURE spSalesOverAmt ( @Amount money ) AS BEGIN SELECT SalesOrderID, OrderQty, UnitPrice FROM Sales.SalesOrderDetail WHERE UnitPrice > @Amount END [/cc] Now let’s change the column names and the data types: [cc lang=”sql”] — Execute the procedure with a parameter EXEC spSalesOverAmt @Amount = 1200 WITH RESULT SETS ( […]
Continue reading ...
Often times you have a need to export a table from SQL Server into another format. The most common of these is Excel, CSV (Comma Separated Value), Access, or even to another SQL Server. Luckily, there is one tool native to SQL Server that can export to all these formats, the SQL Server Import and Export wizard. To start this wizard, simply right-click on the database that contains the table you want to export within Management Studio, then select Tasks -> Export Data. What pops up next is the SQL Server Import/Export Wizard. Select next to get past the splash screen then choose the data source. This is going to be the same data source as the database you selected to export the table from. Once you select the ServerName and Database name, select next. Next you want to choose the destination. This is where multiple export options come in. You can choose any of the options that appear in the destination drop down box. Once you select the export file type and file location, you will then be prompted to select the tables to output, along with additional options. This will assist you in outputting many different export formats for your table.
Continue reading ...
In order to create a view, use the “Create View” statement using the following convention: [cc lang=”sql”] CREATE VIEW dbo.vStoreAccountNum AS SELECT AccountNumber FROM Sales.Customer WHERE CustomerType = ‘S’ [/cc] Views are essentially stored SELECT Statements. They do not offer any speed advantage over regular SELECT statements. And they are not faster than a SELECT statement executed from within a stored procedure. They are mostly good for hiding complicated logic, or for providing an additional layer of abstraction from your source tables as a security measure. Along with a good indexing strategy, views can be used to simplify highly normalized tables. The more joins within a view, and the more columns it returns back, the slower it is going to be. If you use views, try to make them only perform one function and only return back a finite number of columns. You don’t want views that return back a lot of columns when the majority of your queries only use one or two columns. Here are some Do’s and Don’ts: Do use views if you want Data Analysts to access common queries. Do make sure all the join columns in a view contain indexes. Do Not call views from within views. Try not to return a lot of columns within a view if the view is referenced a lot. Do Not use SELECT * within a view. Specify the columns names explicitly. Indexed (or Materialized Views) A materialized view is a view that has been indexed and has essentially […]
Continue reading ...
The SQL Replace function replaces a value within a string with another value. The final output is the original string with the replaced value. There are three parameters to the Replace function. The first is the original string. The next parameter is the string to replace. And the last parameter is the value to replace the string with. Here is an example: [cc lang=”sql”] SELECT REPLACE ( ‘original string’, — Original String ‘string’, — String to replace ‘replaced’ — What to replace with ) [/cc] The output from this is: “original replaced”. One thing you need to watch out for is that if any of the parameters in the replace statement are NULL, then the string that is returned will also be NULL. So if passing parameters dynamically and there is a chance of NULL, make sure to use the ISNULL function and turn it into an empty string. You can also sometimes get away with using REPLACE rather than concatenating strings. I’ve done this before when you have a templated piece of text that needs values injected into it. [cc lang=”sql”] DECLARE @ResponseMessage varchar(max); SET @ResponseMessage = ‘Dear [firstname], thank you for signing up for [subscription].’; SELECT @ResponseMessage = REPLACE ( @ResponseMessage, ‘[firstname]’, pc.FirstName ) FROM Person.Contact pc WHERE ContactID = 1; SELECT @ResponseMessage; [/cc] Though it may not seem like much of a big deal, when dealing with many lines of concatenated text, it comes out much cleaner.
Continue reading ...
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 ...
