IF EXISTS( SELECT * FROM dbo . sysobjects WHERE id = OBJECT_ID ( N'[dbo].[PartitionLeft]' ) AND xtype in ( N'FN' , N'IF' , N'TF' ) ) BEGIN DROP FUNCTION [dbo] . [PartitionLeft] END GO CREATE FUNCTION [dbo] . [PartitionLeft] ( @value bigint , @min bigint , @max bigint , @range bigint ) RETURNS bigint...

IF EXISTS( SELECT * FROM dbo . sysobjects WHERE id = object_id ( N'[dbo].[IsValidCPF]' ) AND xtype in ( N'FN' , N'IF' , N'TF' ) ) BEGIN DROP FUNCTION [dbo] . [IsValidCPF] END GO CREATE FUNCTION dbo . IsValidCPF ( @CPF varchar ( 11 ) ) RETURNS bit AS BEGIN DECLARE @Digito int DECLARE @Index int DECLARE...

Suppose I have the following query run against Pubs database SELECT employee . emp_id , employee . fname , employee . minit , employee . lname , employee . job_id , jobs . job_desc , employee . job_lvl , employee . pub_id , employee . hire_date FROM employee INNER JOIN jobs ON employee . job_id = jobs...

Today I had to write a SQL statement quite similar to the sample below: SELECT ProductID, ProductName, CASE WHEN ProductDescription IS NULL OR ProductDescription = '' THEN '<no description>' ELSE ProductDescription END AS ProductDescription FROM Products ORDER BY ProductName Then...

Q: When I try to compile the procedure below I get the error Must declare the variable '@TableType'. Can you guess why the following stored procedure does not compile? CREATE PROCEDURE #SystemTablesAndColumns AS DECLARE @TableType TABLE (id int, name sysname) INSERT INTO @TableType ( id, name...

Last week, I created some scripts to move data from an old database to a new one. Some tables consisted of many fields and the old ones were not typed, that is, all fields where char, varchar, etc. INSERT INTO targetTable ( targetField1, targetField2, targetField3, ... targetFieldN ) SELECT targetField1...

Last week, I found out DA team, using ERWin to synchronize models and databases, had messed up with some foreign keys on a database I architected for an application I am developing and the FKs were missing. Then the question "How to find out which FKs are missing on a database made of about 200...

I generally create bit columns as NOT NULL DEFAULT dbo.BIT_NO, which in turn is a SQL server default which translates to 0. Today, I found out somebody on my team had created lots of bit fields on several tables as NULL with no default set. So I created the SQL statement below, to list these fields and...

I created the following two stored procedures using named transactions inside them /* ********************************************************** */ CREATE PROCEDURE #WorkingWithNamedTransactionsOne -- This procedure does not raise error AS DECLARE @ERROR bit PRINT 'Starting NamedTransactionOne'...

It's six months since I started to compile a list of .NET developers in the UK who blog. As a microcosm of the overall expansion of blogging, it's been an interesting experiment. The original list had just 17 names on it. As of today, I've recorded a total of 59 blogs on Microsoft development...