New answers tagged

0

There is a Data Warehouse Migration utility (available here) for Azure SQL Data Warehouse which picks up issues like incompatible datatypes, in-line functions, use of hints, use of RETURN statement, INSERT ... EXEC and many others: Unfortunately it does not pick up scalar functions which reference tables and really it should. However, for your particular ...


0

SELECT g(f(date)) AS 'week of', AVG(...) AS 'moving average' FROM tbl WHERE date > CURDATE() - INTERVAL 52 WEEK GROUP BY f(date) Where f() is a function that turns a date (or datetime or timestamp) into a number. Something like FLOOR((TO_DAYS(date) - 3) / 7), where the 3 is chosen based on what day a week "starts" on. (I doubt if ...


5

Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =. Instead you must use DECLARE @var int = or SET @var =. SQL DW UDFs also do not yet support queries on user tables. Please use our feedback page to vote for new features.


4

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse): function_body Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function. Could you double check that function is created in DW? ...


5

It's way easier in plpgsql: CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$ DECLARE _r RECORD; _id_kraj int; _nazwa_zespolu text; BEGIN _id_kraj := id_kraj FROM kraj WHERE nazwa = $1; FOR _r IN SELECT imie, nazwisko, id_zespol, id_kraj FROM pilkarz LOOP IF r.id_kraj = _id_kraj THEN _nazwa_zespolu := nazwa FROM ...


1

Yep! I finally solved my problem.. I Hope this help to somebody.. drop procedure if exists sp_RecursiveUser; delimiter # create procedure sp_RecursiveUser ( in prm_UserID int unsigned ) begin declare v_done tinyint unsigned default 0; declare v_depth smallint unsigned default 0; create temporary table hier( parent_user_id smallint unsigned, user_id ...


0

One more option is to use view. Usage: UPDATE Users SET Password = dbo.GeneratePassword(DEFAULT, DEFAULT, DEFAULT) WHERE UserID = @UserID Code: CREATE VIEW dbo.RandomNoise AS -- helper view for accessing CRYPT_GEN_RANDOM from UDF -- see dbo.GeneratePassword for details about 2048 value SELECT CRYPT_GEN_RANDOM(2048) Bytes GO CREATE FUNCTION ...


2

Given tables: CREATE TABLE dbo.Vendors ( VendorID integer NOT NULL, VendorName nvarchar(50) NOT NULL, CONSTRAINT [PK dbo.Vendors VendorID] PRIMARY KEY CLUSTERED (VendorID), CONSTRAINT [UQ dbo.Vendors VendorName] UNIQUE NONCLUSTERED (VendorName) ); CREATE TABLE dbo.Invoices ( InvoiceID integer NOT NULL, InvoiceTotal money ...


2

You don't need to convert the '$' to a VARCHAR since it is already a VARCHAR type. You need to convert the value coming out of the function to a string since it is currently an INT. However, since this is currency, you should instead do the following two things: Change the return type of the function to be MONEY (which is, in this case, better than ...


2

It looks like you're expecting the integer to get implicitly converted to string. What actually happens is the other way around - the string gets implicitly converted to integer. The simplest version of what you want is: Select '$' + Cast(dbo.fn_TotalBill('IBM') As Varchar) But: You shouldn't use Varchar without a length, it's bad practice. In this ...


4

Just for fun, here's a SQL Server 2016 Scalar User-Defined function with the In-Memory OLTP feature: ALTER FUNCTION dbo.IsPalindrome2 ( @inputString NVARCHAR(500) ) RETURNS BIT WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') DECLARE @i INT = 1, @j INT = LEN(@inputString) ...


6

This is an inline TVF-friendly version of Martin Smith's set-based solution, additionally decorated with a couple of superfluous enhancements: WITH Nums AS ( SELECT N = number FROM dbo.Numbers WITH(FORCESEEK) /*Requires a suitably indexed numbers table*/ ) SELECT IsPalindrome = CASE WHEN EXISTS ( SELECT * FROM ...


0

Final code: (It works) ALTER TABLE public.companies ADD COLUMN client_code_increment integer; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000; COMMIT TRANSACTION; BEGIN; -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() ...


6

Without using REVERSE... It's always fun to use a recursive solution ;) (I did mine in SQL Server 2012, earlier versions might have limitations on recursion) create function dbo.IsPalindrome (@s varchar(max)) returns bit as begin return case when left(@s,1) = right(@s,1) then case when len(@s) < 3 then 1 else ...


3

You can execute Dynamic SQL on that Linked Server by executing sp_executesql on that remote server using the 4-part name. Then you just assign that to a variable marked as OUTPUT: DECLARE @RemoteName sysname, @LoginID INT; SET @LoginID = 267; EXEC [LinkedServerName].master.dbo.sp_executesql N'SET @tmpRemoteName = SUSER_NAME(@tmpLoginID);', ...


17

Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a typo mention it in the comments and I'll explain what's going on. I'm going to point out several things that you may already know, so please don't take offense ...


11

The REVERSE() method "improved", i.e. reversing only half of the string: SELECT CASE WHEN RIGHT(@string, LEN(@string)/2) = REVERSE(LEFT(@string, LEN(@string)/2)) THEN 1 ELSE 0 END AS Palindrome; I don't expect anything weird to happen if the string has an odd number of characters; the middle character doesn't have to be ...


4

A major issue you're going to run into is that with any value greater than 1, LEFT or RIGHT will return multiple characters, not the character at that position. If you wanted to keep with this method of test, a really simple way to modify it would be RIGHT(LEFT(String,@n),1)=LEFT(RIGHT(String, @StringLength),1) This will always grab the rightmost ...


15

You could also use a Numbers table approach. If you don't already have an auxiliary numbers table you can create one as follows. This is populated with a million rows and so will be good for string lengths up to 2 million characters. CREATE TABLE dbo.Numbers (number int PRIMARY KEY); INSERT INTO dbo.Numbers (number) SELECT TOP 1000000 ...


8

Without using REVERSE, which is what immediately comes to mind, but still using a function1; I would construct something like the following. This part simply removed the existing function, if it already exists: IF OBJECT_ID('dbo.IsPalindrome') IS NOT NULL DROP FUNCTION dbo.IsPalindrome; GO This is the function itself: CREATE FUNCTION dbo.IsPalindrome ( ...


57

If you are using SQL Server you can use the REVERSE() function to check? SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome; Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function: SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


2

The two answers given so far both entail at least one reversal of the string to tackle the right hand side. This method doesn't. CREATE FUNCTION dbo.LTrimWhitespace(@Input VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Output VARCHAR(MAX) DECLARE @Whitespace VARCHAR(20) DECLARE @Pos INT SET @Whitespace = CHAR(0) + CHAR(9) + CHAR(10) ...


0

Closest thing that I know is to overload a function... create or replace package test_pkg as function variableParams ( a in varchar2, z in integer, y in integer ) return integer; function variableParams ( a in varchar2, z in integer, y in integer, x in integer ) return integer; end; / create or replace package body test_pkg as function ...


1

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements. There is no nice and easy way for this. There is the method with collections, for example: create or replace type t_numbers as table of number; / create or replace function sum_plsql (p_numbers t_numbers) return number as rv number := 0; begin ...


3

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why. Because you copy pasted from the website and the page doesn't show a hyphen/minus but a en-dash. You can just position your cursor behind it and delete it, then type minus on your keyboard and the code will work. If you want to modify the ...


6

The best way would probably be to create a CLR function and use .NET framework libraries. A TSQL attempt is below. DECLARE @Pattern NVARCHAR (50) = N'%[^' + /*List from http://stackoverflow.com/a/18169122/73226 */ NCHAR(0x0009) + /*tab*/ NCHAR(0x000A) + /*line feed*/ NCHAR(0x000B) + /*line tabulation*/ NCHAR(0x000C) + /*form feed*/ NCHAR(0x000D) + ...



Top 50 recent answers are included