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.
Continue reading ...
To get the current date in SQL Server use the GETDATE() function. Here is an example: [cc lang=”sql”] SELECT CurrentDate = GETDATE() [/cc] GetDate() returns a datetime data type value. There are also other methods in SQL Server that will return the current date. These are less commonly used. In fact, I have never personally had a need to use any of them except GETDATE(). [cc lang=”sql”] SELECT SYSDATETIME() SELECT SYSDATETIMEOFFSET() SELECT SYSUTCDATETIME() SELECT CURRENT_TIMESTAMP SELECT GETDATE() SELECT GETUTCDATE() [/cc]
Continue reading ...
Browsing along the SQL Server documentation today, I noticed something that scared me. As a spoiled non ANSI compliant SQL Server developer, I do not use a semicolon to terminate my statements. Well, here’s the excerpt that threw me a curveball: Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version. Here is the link: http://msdn.microsoft.com/en-us/library/ms177563.aspx Holy Crap! My first thought was about all the code I have written and how I could automate inserting semicolons to the end of all the statements. While that would be almost futile, I suppose it could be done. I’m sure Microsoft will provide a tool that will append a semicolon for the future release however. If they did not, it would stop people from upgrading, which would cost them money. This realization made me think more about how I code and how much more important it is to enforce ANSI standards into your work. Being shielded from these subtleties in the past has not been a benefit. You can now tell that this SQL Server developer team is really serious about their product. First they got rid of the NON ANSI joins, which was a big achievement in my view, now they are moving onto the semicolon. I can’t say it’s a big surprise because if you have ever used common table expressions, you’ll know that’s where the first indication of requiring a semicolon was put into […]
Continue reading ...
Here is the maximum value for a datetime datatype in SQL Server: 9999-12-31 23:59:59.997 And the minimum: 1753-01-01 00:00:00.000 The maximum precision for a datetime value is 3 milliseconds. This is why the ending milliseconds above are shown as 997 instead of 999. Here’s the proof to get the max datetime in case you are interested. To get the minimum, simply add a negative sign to the increments below. [cc lang=”sql”] DECLARE @datetime DATETIME SET @datetime = GETDATE() WHILE 1 = 1 BEGIN BEGIN TRY — increment by days SET @datetime = DATEADD(dd,1,@datetime) END TRY BEGIN CATCH — cannot increment by days anymore WHILE 1 = 1 BEGIN BEGIN TRY — increment by minutes SET @datetime = DATEADD(mi,1,@datetime) END TRY BEGIN CATCH — cannot increment by minutes anymore BEGIN TRY — increment by seconds SET @datetime = DATEADD(s,1,@datetime) END TRY BEGIN CATCH — cannot increment by seconds anymore BEGIN TRY — increment by milliseconds SET @datetime = DATEADD(ms,3,@datetime) END TRY BEGIN CATCH — cannot increment by seconds anymore SELECT @datetime BREAK END CATCH END CATCH END CATCH END BREAK; END CATCH END [/cc] This took 7 seconds to run on my machine, fyi.
Continue reading ...
