The Ultimate T-SQL String Splitter (function)0 Reacties

 

I just want to share this function, since there are a lot of version around, which are not resistant against zero positions advance, for instance, if you split ‘1,2,3’ into a table, it would be find, but what if one element is empty, such as ‘1,,3’? This function deals with it setting returning a null element.

Usage:

SELECT * FROM [udf_SplitVarchar2Table]('one,two,three', ',')

returns:

ALTER FUNCTION [dbo].[udf_SplitVarchar2Table]
(
    @List varchar(max),
    @delimiter VARCHAR(10)
)

RETURNS
    @Values TABLE(col VARCHAR(512))
AS

BEGIN 
    IF @List IS NULL OR LEN(@List) = 0 RETURN;
 
  SET @List = replace(@List,CHAR(39)+CHAR(39),CHAR(39))
 
  DECLARE @Index INT=1; 
  DECLARE @ItemValue varchar(100);  
  DECLARE @pos INT = 1;
  DECLARE @l INT = LEN(@List);

  WHILE @Index > 0   
    BEGIN        
      SET @Index = CHARINDEX(@Delimiter,@List, @pos);  
   
      IF @Index  > 0 
            IF (@index- @pos> 0)
                SET @ItemValue = SUBSTRING(@List,@pos, @index- @pos );
            ELSE
                SET @ItemValue=NULL;
      ELSE
        IF (@l-@pos+1)>0
            SET @ItemValue =SUBSTRING( @List, @pos, @l-@pos+1) ;
        ELSE
            SET @ItemValue = NULL;

      INSERT INTO @Values (col) VALUES (@ItemValue);    
      SET @pos = @index+1;
    END
    RETURN;
END

T-SQL Alternative to hexadecimal binary strings?0 Reacties

I found an easy way to have binary parameters as base64 encoded string. You might wonder, why bother?

Well, in a a well used environment, size and compactness of data over the wire, still matters! Because a binary value is sent as a hexadecimal over the wire; Hexadecimals are 4 times the size of one byte. Base64 encoded strings however, just need +/- 3 times the size of one byte. .

example:

EXEC proc_receiveMyBlob 0xA05FDAF  (etc) 

 The stored procedure itself would have this signature:

CREATE PROC  @myBLob varbinary(max)    -- or image whatever

BEGIN 

   INSERT INTO tblMyBlobs VALUES(@myBlob);

END 

The trick:

 CREATE PROC  @myBLob xml  -- <-- use the xml T-SQL data type

BEGIN 

-- remember, the binary field in SQL must not be changed to xml, keep it as binary! 

   INSERT INTO tblMyBlobs VALUES(@myBlob.value('xs:base64Binary(.)', 'varbinary(max)') );

END  

 

The call to the stored proc (obviously) looks something like this:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0' 

or if you like:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0

 

Howto program a LINQ expression which effectively does an aggregate subquery with optional cardinal relationship 0 Reacties

LINQ! Yes, Also I fell in love with linq. 

So here is my first try. And as you fans know, I like to dig into subjects.

Maybe, the title is a little incorrect, but I wanted a query that returns one record, using a subquery in one statement!

Using SQL syntax, this would look like the query just below.: Yes, It seems I can dream SQL, but it was shocking to see how I underestimated the LINQ syntax which took some extra hears from my head.

The query returns events, that have not already been booked full. (There are still some places left)

SELECT [t0].[id], [t0].[maxNumberofParticipants], [t0].[OwerId], [t0].[Description], [t0].[StartTime], [t0].[EndTime],
[t0].[orderId] FROM [dbo].[Activity] AS [t0]
WHERE ([t0].[OwerId] = @p0) AND ([t0].[maxNumberofParticipants] > @p1) AND ([t0].[maxNumberofParticipants] > (ISNULL((
    SELECT SUM([t2].[value])
    FROM (
        SELECT [t1].[countOfPersons] AS [value], [t1].[activityId]
        FROM [dbo].[ActivityParticipant] AS [t1]
        ) AS [t2]
    WHERE [t2].[activityId] = [t0].[id]
    ),0)))

I could make this a stored proc, and execute this easily. But that was in my previous life.

So, how to express this using a LINQ query?

The trick with the ISNULL function is to -cast- the countOfPersons field, to a nullable type! Since the GetValueOrDefault() is available only to nullable data types we must cast it to an int? datatype.
The LINQ provider, will translate it finally, when it sends the actual SQL to SQL Server using TSQL function 'COALESC'.
bt.w., it will not use the ISNULL function. 

public IEnumerable<Activity> getActivityParticipableByOwnerWith(Guid ownerGuid)
{

var query = from a in Activities
where a.OwerId == ownerGuid && a.maxNumberofParticipants > 0 && a.maxNumberofParticipants >
ActivityParticipants.Where(aid => aid.activityId == a.id).Sum(aid => (int?)aid.countOfPersons).GetValueOrDefault(0)
select a ;
 

return query;
}

Kuddo's to Khaled Moawad, who very patiently helped me to improve the syntax.  b.t.w. guys/girls. Always try it hard yourself, before you ask the global community for support. That makes your brains retain better :)

My next goal is to make LINQ like a natural language for me, like SQL was :)