UDF Error
in [Programming SQL]
|
From: VSLA on 13 Mar 2010 23:16 I am trying to create a UDF to extract the domain name out of a full email address, as follows: CREATE FUNCTION dbo.udf_GetDomainName (@FullEmail VARCHAR(75)) RETURNS Varchar(75) AS Begin DECLARE @domainname VARCHAR(75) Declare @delim CHAR(1) SET @delim = '@' SELECT @domainname = SUBSTRING(@domainname, 1, CHARINDEX(@delim, @domainname) - 1), SUBSTRING(@domainname, LEN(@domainname)+ 1 - CHARINDEX(@delim, REVERSE(@domainname)) + 1, LEN(@domainname)) Return (@domainname) end I am getting the following error message: Msg 141, Level 15, State 1, Procedure udf_GetDomainName, Line 19 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. Any thoughts? thanks,
From: bill on 14 Mar 2010 00:18 Not sure, but I have an idea. I have seen this error when I accidentally tried to select two columns, instead of one, into a variable. Example that would generate error: SELECT @variable = Column A ,Column B FROM Table Is it possible that your substring statement is returning two columns of data instead of a single string? Thanks, Bill
From: Uri Dimant on 14 Mar 2010 03:46 VSLA create a stored procedure rather than UDF "VSLA" <VSLA(a)discussions.microsoft.com> wrote in message news:6AA1700F-1EFE-4E6A-8B40-23F698DF7FF6(a)microsoft.com... >I am trying to create a UDF to extract the domain name out of a full email > address, as follows: > > > CREATE FUNCTION dbo.udf_GetDomainName (@FullEmail VARCHAR(75)) > RETURNS Varchar(75) > > AS > > Begin > DECLARE @domainname VARCHAR(75) > Declare @delim CHAR(1) > > SET @delim = '@' > > SELECT @domainname = SUBSTRING(@domainname, 1, CHARINDEX(@delim, > @domainname) - 1), > SUBSTRING(@domainname, LEN(@domainname)+ 1 - > CHARINDEX(@delim, REVERSE(@domainname)) + 1, LEN(@domainname)) > Return (@domainname) > end > > > > I am getting the following error message: > > Msg 141, Level 15, State 1, Procedure udf_GetDomainName, Line 19 > A SELECT statement that assigns a value to a variable must not be combined > with data-retrieval operations. > > > Any thoughts? > thanks,
From: Uri Dimant on 14 Mar 2010 05:08 Using UDF see if the below helps you DECLARE @domainname VARCHAR(75) Declare @delim CHAR(1) SELECT @domainname='TTT(a)HOTMAIL.COM' SET @delim = '@' SELECT @domainname=c1 FROM( SELECT SUBSTRING(@domainname, 1, CHARINDEX(@delim, @domainname) - 1) AS c1, SUBSTRING(@domainname, LEN(@domainname)+ 1 - CHARINDEX(@delim, REVERSE(@domainname)) + 1, LEN(@domainname)) AS c2 ) AS D SELECT @domainname "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:e8LOkp0wKHA.4532(a)TK2MSFTNGP05.phx.gbl... > VSLA > create a stored procedure rather than UDF > > > "VSLA" <VSLA(a)discussions.microsoft.com> wrote in message > news:6AA1700F-1EFE-4E6A-8B40-23F698DF7FF6(a)microsoft.com... >>I am trying to create a UDF to extract the domain name out of a full email >> address, as follows: >> >> >> CREATE FUNCTION dbo.udf_GetDomainName (@FullEmail VARCHAR(75)) >> RETURNS Varchar(75) >> >> AS >> >> Begin >> DECLARE @domainname VARCHAR(75) >> Declare @delim CHAR(1) >> >> SET @delim = '@' >> >> SELECT @domainname = SUBSTRING(@domainname, 1, CHARINDEX(@delim, >> @domainname) - 1), >> SUBSTRING(@domainname, LEN(@domainname)+ 1 - >> CHARINDEX(@delim, REVERSE(@domainname)) + 1, LEN(@domainname)) >> Return (@domainname) >> end >> >> >> >> I am getting the following error message: >> >> Msg 141, Level 15, State 1, Procedure udf_GetDomainName, Line 19 >> A SELECT statement that assigns a value to a variable must not be >> combined >> with data-retrieval operations. >> >> >> Any thoughts? >> thanks, > >
From: Tibor Karaszi on 14 Mar 2010 08:46
> Not sure, but I have an idea. I have seen this error when I > accidentally tried to select two columns, instead of one, into a > variable. Yes, that would be the problem. There are actually several substrings if you look at the posted code: SELECT @v = SUBSTRING(...), SUBSTRING(...) Can only have one! -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "bill" <billmaclean1(a)gmail.com> wrote in message news:88e11be7-c49f-4cba-8f74-b42ba5d2add4(a)o3g2000yqb.googlegroups.com... > Not sure, but I have an idea. I have seen this error when I > accidentally tried to select two columns, instead of one, into a > variable. > > Example that would generate error: > > SELECT @variable = > Column A > ,Column B > FROM > Table > > Is it possible that your substring statement is returning two columns > of data instead of a single string? > > Thanks, > > Bill |