|
I think you are looking for R Programming Language[^]
You're welcome.
I'm retired. There's a nap for that...
- Harvey
|
|
|
|
|
|
...if SQL had macro functionality (kinda like C++) that would help reduce the amount of repetitive bullcrap we have to deal with? For instance, this is part of an answer I posted in the C# forum.
SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]
It would really nice if I could create a macro with the contents of the repeated code:
substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))
Something like this:
declare @justNumeric MACRO = <substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))>
Which would reduce the first query to this:
SELECT MIN(@justNumeric) AS MinID,
MAX(@justNumeric) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit
This couldn't possibly have an adverse effect on SQL's error reporting incapabilities (Syntax error near ','), so I'm not concerned with that.
Am I just an optimistic dreamer?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 23-Mar-18 13:01pm.
|
|
|
|
|
You could create .Net assembly for that C# code and call it in the SQL.
My brain works. Hah!
The best way to make your dreams come true is to wake up.
Paul Valery
|
|
|
|
|
It would have to completely contained in SQL Server because a lot of DBA's "aren't programmers", according to the DBA in our shop.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 23-Mar-18 13:15pm.
|
|
|
|
|
Well they have to code it even if Macro's are allowed. Plus we all know Macro's are dangerous due to security flaws and not good idea to introduce in SSMS or whatever DB Engine.
The best way to make your dreams come true is to wake up.
Paul Valery
|
|
|
|
|
So you can't write a CLR UDF and delivery it the DBAs?
|
|
|
|
|
John Simmons / outlaw programmer wrote: a lot of DBA's "aren't programmers"
It has been my experience that a lot of DBAs are not DBAs. 
|
|
|
|
|
WERD
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Slacker007 wrote: John Simmons / outlaw programmer wrote: a lot of DBA's "aren't programmers"
It has been my experience that a lot of DBAs are not DBAs.
And not all programmers are programmers.
But on the bright side the marketing people know everything about databases and programs, where would we be without them?
Signature ready for installation. Please Reboot now.
|
|
|
|
|
It did reminds me who they are.
Prostitutes. Jeremy comment[^]. 
The best way to make your dreams come true is to wake up.
Paul Valery
|
|
|
|
|
True, including 'Mongodb'ers. I find out from Jeremy.
-JB
|
|
|
|
|
They do love strip club too.
|
|
|
|
|
Too dangerous. This exe compiles together with the server and if you mess-up you could kill the server. It happened to me. UDF's are absolutely valid approach in Oracle though. There the libraries written on Java or C++ live in a separate processes. The performance is good too.
There is only one Vera Farmiga and Salma Hayek is her prophet!
Advertise here – minimum three posts per day are guaranteed.
|
|
|
|
|
Dude, who said make exe and run in server. I said make .Net assembly(formerly known as DLL). There is big difference between them.
The best way to make your dreams come true is to wake up.
Paul Valery
|
|
|
|
|
Well ... there are scalar functions:
CREATE FUNCTION justNumeric
(
@INP NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
RETURN substring(@INP, PATINDEX('%[0-9]%', @INP), 1+PATINDEX('%[0-9][^0-9]%', @INP+'x')-PATINDEX('%[0-9]%', @INP))
END
SELECT MIN(dbo.justNumeric(ID)), MAX(dbo.justNumeric(ID)), Fruit
FROM Fruits
GROUP BY Fruit Still messy code though - a DB redesign is a better approach!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Functions significantly slow down queries. Not really a valid workaround, IMHO.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
|
Scalar functions are performance killer if you have SQL (selects...) inside. In his case the optimizer commits suicide. But with pure mathematical operations their performance is okay.
There is only one Vera Farmiga and Salma Hayek is her prophet!
Advertise here – minimum three posts per day are guaranteed.
|
|
|
|
|
patindex isn't SQL either, yeah so sure, why not add more rubbish?
it's not as if microsoft ever followed standards anyway.
Signature ready for installation. Please Reboot now.
|
|
|
|
|
|
Well - there are functions, which can do this, sort of.
But, I agree. I work with a real DBA (who can program when necessary) and looking at some of his stored procedures and I would definitely go with MACROs to give my weary eyes and mind a rest.
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you are seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
Functions slow queries down. Not a valid workaround. What I want is a simple way to replace repetitive code that always seems to be found SQL queries.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
It is nonsense to have a VARCHAR as primary key. SQL92 is all you need, this fancy stuff is just there to hide that the DBA doesn't know what he is doing.
An abomination.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Well, I assume the question I answered was a classroom or homework assignment, so you can't blame a DBA for what was required. In the interest of full disclosure, I did rant about the assignment, assignments like it, and the idiot instructors that come up with this crap, in my answer.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
