Showing posts with label expressions. Show all posts
Showing posts with label expressions. Show all posts

Monday, March 19, 2012

How to use regular expressions on T-SQL?

as subjectCreate a CLR-based function - assuming you have SQL Server 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"ABC" <abc@.abc.com> wrote in message
news:OY8pTyGOGHA.2012@.TK2MSFTNGP14.phx.gbl...
as subject|||We use SQL Server 2005. How to write a CLR-based function? Is there any
example?
"Tom Moreau" <tom@.dont.spam.me.cips.ca>
'?:urVZz%23GOGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Create a CLR-based function - assuming you have SQL Server 2005.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ABC" <abc@.abc.com> wrote in message
> news:OY8pTyGOGHA.2012@.TK2MSFTNGP14.phx.gbl...
> as subject
>|||Did you check the SQL Server 2005 Books online? There are several examples
you can find in various websites ( MSDN etc. ), blogs etc.
Anith

How to use regular expressions on SSIS?

Hi everyone,

Is it possible? I haven't idea how to implement as if it were done from conventional .Net code

I'd like to check every field for a plan file

First field NNNNNNN

Second field XXXNNNNNNN

,,

Thanks for any input or idea,

You can use a script task ; an example of using here

About the second part of question an ideea can come from here.

|||This transform may also be of use - http://www.sqlis.com/91.aspx|||

Thanks indeed to both. It's useful.

|||

Hi Darren,

Regarding your link about Regular Expressions tasks, unfortunately, third-components are not allowed in our environment...

|||

enric vives wrote:

Hi Darren,

Regarding your link about Regular Expressions tasks, unfortunately, third-components are not allowed in our environment...

That's uunfortunate!

|||

I appreciate many organisations think that way.

I would be interested in knowing why that is the case for you?

What would it take to allow such component usage in your organisation? I'd be happy to see what could be done to help people like youself in these situations. Do you not use any non-MS products? If you are intertested then I'd be happy to discuss this further or offline, mail me darren dot green at konesans com

Wednesday, March 7, 2012

How to use CTE instead of temp table

I am trying to use CTE (Common Table Expressions) instead of Temporary tables. First constraint I felt was we need to SELECT that CTE out as soon as we create it. The second constraint is, it says CTE as invalid after certain points (or batches ) on the code. It will be great if you can give a workaround. This is my procedure.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter PROC [dbo].[spDL_Menu_get_temp]
@.user_id VARCHAR(2000) ,
@.request_root VARCHAR(255) ,
@.request_query_string VARCHAR(255)
AS

/*

*/

DECLARE @.query_string_local AS VARCHAR(255)

SET @.query_string_local =
CASE WHEN (LTRIM(RTRIM(@.request_query_string)) = '') THEN '?'
WHEN (LTRIM(RTRIM(@.request_query_string)) <> '') THEN ('?' + @.request_query_string)
END
;

-
WITH TBL_MENUS( TABLE_ID , MENU_ID , MENU_NAME , MENU_LEVEL , MENU_ID_PARENT , MENU_LEVEL_INDEX, URL_TARGET) AS
(
SELECT MEN.table_id ,
MEN.menu_id ,
MEN.menu_name ,
MEN.menu_level ,
MEN.menu_id_parent ,
MEN.menu_level_index ,
-- (@.request_root + url_target + '?' + @.request_query_string ) AS url_target
REPLACE( (@.request_root + url_target + @.query_string_local + ISNULL(query_string,'')) , '?','?') AS url_target
FROM DL_Menu AS MEN
JOIN DL_MENU_PERMISSIONS PER ON MEN.menu_id = PER.menu_id
JOIN DL_USER USR ON USR.USERID = @.user_id AND PER.role_id = USR.role

)

SELECT TOP 1 MENU_ID FROM TBL_MENUS ;

WITH TBL_ROLES( ROLE ) AS
(
SELECT ROL.ROLE_DESC AS ROLE FROM DL_USER USR
LEFT OUTER JOIN DL_USERROLE ROL ON USR.ROLE = ROL.ROLE_ID
WHERE USERID = @.USER_ID
)

-- select * from dl_userrole
SELECT MEN.MENU_ID , ROL.ROLE FROM TBL_ROLES ROL , TBL_MENUS MEN
SELECT top 1 role FROM TBL_ROLES

DECLARE @.MENU_LEVEL_INDEX AS TINYINT
--SELECT @.MENU_LEVEL_INDEX = MAX(MENU_LEVEL_INDEX) FROM TBL_MENUS WHERE MENU_LEVEL = 1
SELECT @.MENU_LEVEL_INDEX
SET @.MENU_LEVEL_INDEX = @.MENU_LEVEL_INDEX + 1

SELECT TABLE_ID , MENU_ID , MENU_NAME , MENU_LEVEL , MENU_ID_PARENT , MENU_LEVEL_INDEX, URL_TARGET FROM TBL_MENUS

UNION
SELECT 'Roles', 'Roles' , 'My Roles' , 1 , NULL , @.MENU_LEVEL_INDEX ,
REPLACE( (@.request_root + url_target + @.query_string_local + ISNULL(query_string,'')) , '?','?') AS url_target

UNION
SELECT ROL.ROLE , ROL.ROL , ROL.ROLE , 2 , 'Roles' , ROL.ROL ,
(REPLACE( (@.request_root + url_target + @.query_string_local + ISNULL(query_string,'')) , '?','?') + '?dbrole=' + ROL.ROLE) AS url_target
-
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If you have the code working with temp tables consider keeping it with the temp tables. Currently, there are some optimizations you can make using temp tables that are not available with CTEs. I looked through this and need some help understanding it. Can you provide (1) some sample data and (2) the desired target output? This will go a long way toward providing help.

Also, give another look to the article in books online relating to CTEs. Also, you can string together multiple CTE definitions that can be used in concert with something like:

;with CTE#1

( select ...

), CTE#2

( select ...

), ...

), CTE#N

( ...

)

select ...

from <yourCTEs>

Friday, February 24, 2012

How to use C# syntax in report expressions

I want to use C# syntax in report expressions.
I use WinForms ReportViewer in local mode.
How to use C# instead of VB.NET syntax ?
I'm thinking about the following approaches:
1. Create pre-processor which converts expressions in RDL file from C# to
VB. NET
2. Replace all expressions with a method which takes C# expression as string
parameter, evaluates it and returns result.
Any idea how to implement this ?
Where to find any samples of C# expression evaluator or C# VB .NET
expression converter ?
Andrus.On Mar 27, 4:34 am, "Andrus" <kobrule...@.hot.ee> wrote:
> I want to use C# syntax in report expressions.
> I use WinForms ReportViewer in local mode.
> How to use C# instead of VB.NET syntax ?
> I'm thinking about the following approaches:
> 1. Create pre-processor which converts expressions in RDL file from C# to
> VB. NET
> 2. Replace all expressions with a method which takes C# expression as string
> parameter, evaluates it and returns result.
> Any idea how to implement this ?
> Where to find any samples of C# expression evaluator or C# VB .NET
> expression converter ?
> Andrus.
I, myself, come from a C# background and needed to learn some basic
VB.NET for expressions in order to use SSRS 2005. Aside from basic
syntax, the two are highly similar. It doesn't seem like the time to
become familiar w/the report expressions, etc in VB.NET would be as
much of a trade off when compared to a custom C# to VB.NET expression
converting application. That said, here's one I found:
http://www.tangiblesoftwaresolutions.com/Product_Details/Instant_VB/Instant_VB.htm
Also, several are mentioned here:
http://groups.google.com/group/microsoft.public.dotnet.languages.vb/browse_thread/thread/b4787e2e70c0b9df/6ec64748dea02d40?lnk=st&q=C%23+to+vb.net+conversion&rnum=4#6ec64748dea02d40
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||> It doesn't seem like the time to
> become familiar w/the report expressions, etc in VB.NET would be as
> much of a trade off when compared to a custom C# to VB.NET expression
> converting application.
I noticed that I must write code in <Code> tags in VB .NET also.
So using reporting services requires learning full VB .NET language, not
only expressions.
I have read everywhere that .NET allows to select between C# and VB .NET
When this is fixed so that C# language can be used in RDLC files ?
Andrus.|||> I noticed that I must write code in <Code> tags in VB .NET also.
>So using reporting services requires learning full VB .NET language, not
>only expressions.
Really not true, or at least certainly not a big deal, if you put your code
in custom DLLs instead of embedding it in the report. Which you would
probably want to do for non-trivial code anyway. So put your code in custom
DLLs in C#, and go as crazy as you want creating custom functions (in C#)
which you can call in your report for anything where the built-in VB
functions seem non-intuitive or not effective from your POV.
>L<
"Andrus" <kobruleht2@.hot.ee> wrote in message
news:OWlLZNHcHHA.2120@.TK2MSFTNGP03.phx.gbl...
>> It doesn't seem like the time to
>> become familiar w/the report expressions, etc in VB.NET would be as
>> much of a trade off when compared to a custom C# to VB.NET expression
>> converting application.
> I noticed that I must write code in <Code> tags in VB .NET also.
> So using reporting services requires learning full VB .NET language, not
> only expressions.
> I have read everywhere that .NET allows to select between C# and VB .NET
> When this is fixed so that C# language can be used in RDLC files ?
> Andrus.