Areas of Interest, as counted by my cat

Category: T-SQL

PSA: Visual Studio 2019 Class Library Projects

Public Service Announcement:

This:

is not the same as this:

That is all.

Oh, okay. The first one won’t let you target any old version of the .NET framework, like, say, .NET 4, that you might need because you’re trying to create a CLR function library for SQL Server 2019.

Testing using SQL and common table expressions

Sometimes I forget how to do simple things, like splitting a string into sections based on a delimiter.

Here’s a nice way to test your code on a range of input values, and see the interim results, in one hit:

with 
myobject as (
-- put in a range of test values:
select 'dbo.MyFunction' as NAME
union
select 'setup.ins_Contact'
union
select 'MyOtherFunction'
),
more2 as (
-- calculate interesting attributes:
select
NAME,
len( NAME ) as LENGTH_,
charindex( '.', NAME ) as POSITION
from myobject
)
-- perform the test:
select
NAME, POSITION, LENGTH_,
case when POSITION=0 then 'dbo'
else substring( NAME, 1, POSITION-1 )
end as SCHEMA_,
substring( NAME, POSITION+1 , (LENGTH_ - POSITION ) ) as OBJECT_
from more2;
;

© 2024 More Than Four

Theme by Anders NorenUp ↑