As I wrote in my last post, I’ve been working closely with Microsoft SQL server for a while now. Since then I’ve found some more tricks that made my life easier.
I’ve come to realize in the past 2 months that the documentation is pretty thorough. So in this post I’ll include them in the relevant parts. I don’t plan on explaining these parts in depth, the docs do it better than I would. I’d rather share some more code that made my work simpler.
Validate data before running a costly query
In the folowing snipped I run a validation code and print a meaningful message, if it fails.
declare id int = 42; if exists(select * from str_table where try_convert(float, Cost) is null and Cost is not null) begin raiserror( N'Failed to convert a Cost to number for ID: %d (for more details run: select * from huge_str_table where try_convert(decimal(18, 4), DetailLineAmt) is null and DetailLineAmt is not null)' -- Message text ,16 -- Severity ,1 -- State ,@id -- Message Parameters ); end
Here I used the raiserror statements. In the it’s stated, that new applications should use throw instead of it.
throw does not support custom messages, but the variables that I set up with formatmessage show up as empty messages in my database query tool during development.
N'' prefix at the start of the message indicates that it’s an nvarchar, so it can contain unicode characters.
The exists statement checks whether a given statement has any results.
if statement can run a block of code that starts with
begin and ends with
end. It can only have a single
If you need more elif branches you have to nest
if-else constucts in the proper branches.
You can set a severity for your messages with raiserror, throw will set it automatically to 16, except if you re-throw an exception in a catch statement.
State is useful if the same user-defined error is raised at multiple locations. It can be between 1-255. It can help find which section of code is raising the errors.
The error codes under 50000 are reserved for system errors.
Throw can not send these system messages, while
During this few months I’ve been investigating many questions about the data, its validity, and extracted information to business-related questions. Since we were in a hurry I often found myself running the same queries all over again. When I needed to type the same joins for at least 5 times, and they got lost in my console I stopped and tried to automate them, because I was fairly confident that they will come up again later.
These constructs were really helpful, their concepts were not new to me. I just want to keep their syntax over here for a quick reference and what point out what makes them different.
When I created these views, functions, and procedures SSMS set me these values. I’ll exclude them from my examples, but I used them.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
When I did not need any dynamism views were my go-to choice. You can think of views as saved sql statements.
create or alter view ActiveCustomers AS select * from dbo.Customers where isDeleted <> 0 ; GO -- select * from ActiveCustomers;
or alter part helped me during development, because I did not need to drop and recreate the views every time.
User defined functions (UDF) are similar to views, but they have 2 main differences that were relevant for me.
- they can receive parameters
- they can return scalar values instead of tabular data
create or alter function fnCheckBalanceAbove ( @lowerBound float ) returns @table table ( CalcTime datetime2(7) ,CompanyID int ) as begin insert into @table ( CalcTime ,CompanyID ) select getutcdate() [CalcTime] CompanyID from Company where Balance >= @lowerBound ; return; end ; go -- select * from fnCheckBalanceAbove(50000)
The function can return inline valued table by just wrapping the select into the
but I find it valuable to define the columns that are going to be returned for better readability.
You can use multiple queries to fill the defined result table.
Do not forget that the function must end with a
The huge benefit of funtions, is that their results can be joined to other queries, thus potentially simplify and shorten their code significantly.
When you need to run many operations, you can use stored procedures.
You can use parameters just like for functions, but you can do even more things:
- use a larger set of constructs like
- call other stored procedures
- return multiple resultsets
You can not use them inside queries, you need to call them with
create or alter proc GetTopNCustomers ( @limit int ) as begin select top(@limit) c.ID ,c.Balance from Customer c order by Balance desc ; end ; GO -- exec GetTopNCustomers @limit = 15;
I advise agains returning multiple result sets if you’d like to store the results somewhere and not just debugging data, because it’s a bit tricky to store the results from a stored procedure call with
You need to have a table that you query the data into.
See this code for example from:
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255), Login VARCHAR(255),HostName VARCHAR(255), BlkBy VARCHAR(255),DBName VARCHAR(255), Command VARCHAR(255),CPUTime INT, DiskIO INT,LastBatch VARCHAR(255), ProgramName VARCHAR(255),SPID2 INT, REQUESTID INT) INSERT INTO #sp_who2 EXEC sp_who2 SELECT * FROM #sp_who2 -- Add any filtering of the results here : WHERE 1=1 and DBName <> 'master' and HostName like '%' -- Add any sorting of the results here : ORDER BY DBName ASC DROP TABLE #sp_who2
I hope you found this collection as useful as I did.