T_SQL
-
When I run this procedure, I get an error message to the effect "You can't assign values to variables within a data retrievl operation. I cannot find any other valid syntax for the command.
I am trying to extract into separate columns the substrings between the exclamation marks in a field like:
"INKS BASE !YELLOW !CVEY8544!"
where the position of the required string is not always in the same place.
ALTER PROCEDURE [dbo].[spGetInks](@Company varchar(50),
@Site varchar(50),
@Zone varchar(50),@m int, @n int,@p int)
AS
SET @m=CHARINDEX('!',Description)
SET @n=CHARINDEX('!',Description,@m+1)
SET @p=CHARINDEX('!',Description,@n+1)
SELECT Name,
InksColourField=
RTRIM(SUBSTRING(Description,@m+1,@n-@m-1)),
InksSupplierRefField=
RTRIM(SUBSTRING(Description,@n+1,@p-@n-1))
FROM dbo.tblInks
WHERE (CompanyID = @Company) AND (SiteID = @Site) AND (ZoneID = @Zone)
I would appreciate any help with this.
-
If the position of the !'s is not fixed use the following, otherwise hardcode in the numbers.
ALTER PROCEDURE dbo.spGetInks
(@Company varchar(50), @Site varchar(50), @Zone varchar(50))
AS
SET NOCOUNT ON
SELECT D.[Name]
,D.[Description]
,RTRIM(SUBSTRING(D.[Description], D.P1 + 1, D.P2 - D.P1 - 1)) as InksColourField
,RTRIM(SUBSTRING(D.[Description], D.P2 + 1, D.P3 - D.P2 - 1)) as InksSupplierRefField
FROM
(
SELECT [Name]
,[Description]
,CHARINDEX('!', [Description]) AS P1
,CHARINDEX('!', [Description] , CHARINDEX('!', [Description]) + 1) AS P2
,CHARINDEX('!', [Description] , CHARINDEX('!', [Description] , CHARINDEX('!', [Description]) + 1) + 1) AS P3
FROM dbo.tblInks
WHERE CompanyID = @Company
AND SiteID = @Site
AND ZoneID = @Zone ) D
RETURN @@ERROR
GO
-
Thanks for the information. Unfortunately, when I run the code you suggest, I get an error saying that I must declare the variable @test.If I do declare it, when the procedure runs it asks me for the value as an input parameter.
Sorry to get back to you but I am a bit of a novice at this.
-
Replace @test-2 with [Description] as above.
ps If you do need local variables, do not declare them in the parameter list.
pps If possible, normalize the database.
- Thanks Ken This now works perfectly.
I appreciate your help.
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply