June 7, 2016 at 4:22 am
Hi,
I have written a table valued function that returns only one row. I am not sure how to call it in a procedure
USE dbo
GO
CREATE FUNCTION [dbo].[fnRemoveStr](@FirstName VARCHAR(500),@LastName VARCHAR(500),@RemoveWord VARCHAR(25))
RETURNS @returnTable TABLE (FirstName VARCHAR(500),lastname VARCHAR(500))
AS
BEGIN
INSERT INTO @returnTable
SELECT LEFT(@FirstName, CHARINDEX(@RemoveWord, @firstName) - 1) AS firstname, LEFT(@lastName, CHARINDEX(@RemoveWord, @LastName) - 1) AS lastname
RETURN;
END
GO
In our data, sometimes we have strings like this attached to names..bad data from vendor eg: "Robert FirstName" instead of just "Robert". We have identified such bad data and come up with rules to remove it.
I want to call this in a Stored procedure.
USE dbo
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Name'))
DROP TABLE dbo.Name
GO
CREATE TABLE dbo.Name
(
ID CHAR(9),
Firstname VARCHAR(500),
LastName VARCHAR(500),
)
GO
USE dbo
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'work.uspProcessName') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE work.uspProcessName
GO
CREATE PROCEDURE work.uspProcessName
AS
SET NOCOUNT ON
BEGIN
DECLARE @ID VARCHAR(9)
DECLARE @Firstname VARCHAR(500)
DECLARE @Lastname VARCHAR(500)
DECLARE getData CURSOR FOR
SELECT DISTINCT ID,FirstName,LastName from dbo.Names;
OPEN getData
WHILE (0=0)
BEGIN
FETCH NEXT FROM getData
INTO @ID,@Firstname,@Latname
IF (@@FETCH_STATUS <> 0)
BREAK
IF (CHARINDEX('FirstName',@FirstName,1)) > 0 OR (CHARINDEX('FirstName',@Lastname,1)) > 0
SELECT @FirstName, @Lastname FROM dbo.fnRemoveStr(@FirstName,@Lastname,'FirstName')
INSERT INTO dbo.Name
(
ID
,FirstName
,LastName
)
SELECT
@ID,
@FirstName,
@LastName,
END
CLOSEgetData
DEALLOCATEgetData
END
When I execute the procedure work.uspProcessName and check the table dbo.Name, I still have wrong values "Robert Firstname".
How can I rectify this?
Thanks
Rs
June 7, 2016 at 4:38 am
To be honest, I would recommend a different approach.
You've got a function that returns one row, and a stored procedure that loops over all the rows in the table one by one. That's not ideal and it's not the best way to write stored procedures. SQL's optimised for set-based operations, where you process a set of rows at once, not one row at a time.
What are you trying to do, update the Names table, or insert the 'cleaned' values into a table called Name?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2016 at 4:44 am
I am trying to insert the 'cleaned' values into the table called Name
June 7, 2016 at 5:02 am
You don't need a cursor or a function to do that.
CREATE PROCEDURE work.uspProcessName
AS
SET NOCOUNT ON
BEGIN
WITH SourceData AS (
SELECT DISTINCT ID,FirstName,LastName
FROM dbo.Names
)
INSERT INTO dbo.Name (ID,FirstName,LastName)
SELECT ID,
CASE WHEN CHARINDEX('FirstName',FirstName,1) > 0 THEN LEFT(FirstName, CHARINDEX('FirstName', FirstName) - 1) ELSE FirstName END AS FirstName,
CASE WHEN CHARINDEX('FirstName',LastName,1) > 0 THEN LEFT(LastName, CHARINDEX('FirstName', LastName) - 1) ELSE LastName END AS LastName
FROM SourceData
END
Not quite sure what the intention is of this though, removing the string literal 'FirstName' from both columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2016 at 6:53 am
Yes, to remove certain unwanted strings from FirstName and Lastname columns.
Here's why I thought I should have a function...there are many such unwanted strings for example: a) Former b)Legal c)IRS
June 7, 2016 at 10:10 am
I would use REPLACE to replace the unwanted word with an empty string.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply