lbarnett419
asked on
Parse Email
This select works fine when run from query window:
Select left(EMAIL,charindex('@',E
from XSQL01.HQ_PSHL.DBO.HQEMPS
where charindex('@',EMAIL)>0
When I put it in a Procedure, I get this: Incorrect syntax near '@' after executing the proc. See paragraph with email parse statement.
Thank you.
Select left(EMAIL,charindex('@',E
from XSQL01.HQ_PSHL.DBO.HQEMPS
where charindex('@',EMAIL)>0
When I put it in a Procedure, I get this: Incorrect syntax near '@' after executing the proc. See paragraph with email parse statement.
Thank you.
The variables are declared in the beginning:
DECLARE @year varchar(2)
DECLARE @table varchar(4)
DECLARE @LOCNUM varchar(3)
DECLARE @SQL varchar(1000)
This is the paragraph that is causing the error:
--UPDATE STAFFID WITH EMAILPREFIX
SET @SQL = 'UPDATE PSHL.PSHLXP.PSHL.' + @table + @year + @LOCNUM
SET @SQL = @SQL + ' SET STAFFID = (SELECT LEFT(EMAIL,charindex ('@',EMAIL)-1)'
SET @SQL = @SQL + ' FROM XSQL01.HQ_PSHL.DBO.HQEMPS'
SET @SQL = @SQL + 'WHERE charindex('@',EMAIL) > 0'
SET @SQL = @SQL + ' AND CONVERT(VARCHAR,EMPNO,10) = ALTID)'
SET @SQL = @SQL + ' WHERE EXISTS'
SET @SQL = @SQL + ' ( SELECT EMPNO'
SET @SQL = @SQL + ' FROM XSQL01.HQ_PSHL.DBO.HQEMPS'
SET @SQL = @SQL + ' WHERE CONVERT(VARCHAR,EMPNO,10) = ALTID)'
EXEC(@SQL)
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
In both instances (select & where)? Or only on the line you referenced?
Also, is that apostrophe twice? Or a quote once? I've tried several combinations, to no avail.
Also, is that apostrophe twice? Or a quote once? I've tried several combinations, to no avail.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Regardless whether I use single quote twice or double quote once, the error still repeats:
Line 1: Incorrect syntax near '@'.
Line 1: Incorrect syntax near '@'.
one more time..
DECLARE @year varchar(2)
DECLARE @table varchar(4)
DECLARE @LOCNUM varchar(3)
DECLARE @SQL varchar(1000)
This is the paragraph that is causing the error:
--UPDATE STAFFID WITH EMAILPREFIX
SET @SQL = 'UPDATE PSHL.PSHLXP.PSHL.' + @table + @year + @LOCNUM
SET @SQL = @SQL + ' SET STAFFID = (SELECT LEFT(EMAIL,charindex(''@''
SET @SQL = @SQL + ' FROM XSQL01.HQ_PSHL.DBO.HQEMPS '
SET @SQL = @SQL + ' WHERE charindex(''@'',EMAIL) > 0 '
SET @SQL = @SQL + ' AND CONVERT(VARCHAR,EMPNO,10) = ALTID)'
SET @SQL = @SQL + ' WHERE EXISTS '
SET @SQL = @SQL + ' ( SELECT EMPNO '
SET @SQL = @SQL + ' FROM XSQL01.HQ_PSHL.DBO.HQEMPS '
SET @SQL = @SQL + ' WHERE CONVERT(VARCHAR,EMPNO,10) = ''ALTID'')'
EXEC(@SQL)
DECLARE @year varchar(2)
DECLARE @table varchar(4)
DECLARE @LOCNUM varchar(3)
DECLARE @SQL varchar(1000)
This is the paragraph that is causing the error:
--UPDATE STAFFID WITH EMAILPREFIX
SET @SQL = 'UPDATE PSHL.PSHLXP.PSHL.' + @table + @year + @LOCNUM
SET @SQL = @SQL + ' SET STAFFID = (SELECT LEFT(EMAIL,charindex(''@''
SET @SQL = @SQL + ' FROM XSQL01.HQ_PSHL.DBO.HQEMPS '
SET @SQL = @SQL + ' WHERE charindex(''@'',EMAIL) > 0 '
SET @SQL = @SQL + ' AND CONVERT(VARCHAR,EMPNO,10) = ALTID)'
SET @SQL = @SQL + ' WHERE EXISTS '
SET @SQL = @SQL + ' ( SELECT EMPNO '
SET @SQL = @SQL + ' FROM XSQL01.HQ_PSHL.DBO.HQEMPS '
SET @SQL = @SQL + ' WHERE CONVERT(VARCHAR,EMPNO,10) = ''ALTID'')'
EXEC(@SQL)
ASKER
Thank you
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
SET @SQL = @SQL + 'WHERE charindex(''@'',EMAIL) > 0'