Create Function - Using DECLARE?

[jiggyg]

Hello! Happy Friday!

I have a question....I have some SQL that I want to make into a function; I want to only pass in the String to parse, but get an error for using DECLARE when I try to execute it...

Could someone please assist me in how to make this code a function and still only passing in the string?

/*
CREATE FUNCTION [dbo].[parseAEcountTreeString](@String varchar(255))
RETURNS TABLE
AS RETURN
*/

--/*
--declare the String
DECLARE @String varchar(255)
SET @String = 'BLOCK A/ACC - CONVENTION CENTER/RETAIL/AIS'
--*/


--declare the delimeter between each Info
DECLARE @Delimeter char(1)
SET @Delimeter = '/'

--Parse the string and insert each Parsed Item into the @tblString table
DECLARE @tblString TABLE(Parsed varchar(255))
DECLARE @Parsed varchar(255)
DECLARE @StartPos int, @Length int
WHILE LEN(@String) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @String)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@String) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
      BEGIN
        SET @Parsed = SUBSTRING(@String, 1, @StartPos - 1)
        SET @String = SUBSTRING(@String, @StartPos + 1, LEN(@String) - @StartPos)
      END
    ELSE
      BEGIN
        SET @Parsed = @String
        SET @String = ''
      END
    INSERT @tblString (Parsed) VALUES(@Parsed)
END

--Show all Parsed Items in the @tblString table
SELECT * FROM @tblString

If I execute that code it works, but if I uncomment the 'CREATE FUNTION...' and then comment the code to declare the string back in, I'm getting the error...

Thoughts and insight are greatly appreciated!

Thanks much!
-jiggyg

 

[bborissov]

And the error is?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jiggyg]

Sorry...


Msg 156, Level 15, State 1, Procedure parseAEcountTreeString, Line 16
Incorrect syntax near the keyword 'DECLARE'.

 

[jiggyg]

Hello Again!

I got a little further on this....Created a STORED PROCEDURE vs. a Function...

USE [TEST]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--/*
ALTER PROCEDURE [dbo].[parseAEcountTreeString](@String varchar(255))
AS
--*/

/*
--declare the String
DECLARE @String varchar(255)
SET @String = 'BLOCK A/ACC - CONVENTION CENTER/RETAIL/AIS'
*/


--declare the delimeter between each Info
DECLARE @Delimeter char(1)
SET @Delimeter = '/'

--Parse the string and insert each Parsed Item into the @tblString table
DECLARE @tblString TABLE(Parsed varchar(255))
DECLARE @Parsed varchar(255)
DECLARE @StartPos int, @Length int
WHILE LEN(@String) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @String)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@String) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
      BEGIN
        SET @Parsed = SUBSTRING(@String, 1, @StartPos - 1)
        SET @String = SUBSTRING(@String, @StartPos + 1, LEN(@String) - @StartPos)
      END
    ELSE
      BEGIN
        SET @Parsed = @String
        SET @String = ''
      END
    INSERT @tblString (Parsed) VALUES(@Parsed)
END

--Show all Parsed Items in the @tblString table
SELECT * FROM @tblString

However, when I go to test it by passing in a String:

EXECUTE parseAEcountTreeString('BLOCK A/ACC - CONVENTION CENTER/RETAIL/AIS')

I'm getting this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'BLOCK A/ACC - CONVENTION CENTER/RETAIL/AIS'.


Thanks in advance for your time and help!
-jiggyg

 

[jiggyg]

Aaaahhhh...got it!

When calling stored procedures, don't need to use ()'s when I pass in the string!!

EXECUTE parseAEcountTreeString'BLOCK A/ACC - CONVENTION CENTER/RETAIL/AIS'

Works!

Thanks!!

 

[bborissov]

I should saw this in a first reading, but I didn't
When you RETURN table You should DECLARE it in the RETURNS statement of the function, also You get @String as a paramete and after that you DECLARE it AGAIN. Try this:

CREATE FUNCTION [dbo].[parseAEcountTreeString](@String varchar(255))
RETURNS @tblString TABLE (Parsed varchar(255))
AS
BEGIN

DECLARE @Delimeter char(1)
SET @Delimeter = '/'

DECLARE @Parsed varchar(255)
DECLARE @StartPos int, @Length int
WHILE LEN(@String) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @String)
    IF @StartPos < 0 SET @StartPos = 0
       SET @Length = LEN(@String) - @StartPos - 1

    IF @Length < 0
       SET @Length = 0

    IF @StartPos > 0
       BEGIN
          SET @Parsed = SUBSTRING(@String, 1, @StartPos - 1)
          SET @String = SUBSTRING(@String, @StartPos + 1, LEN(@String) - @StartPos)
       END
    ELSE
      BEGIN
          SET @Parsed = @String
          SET @String = ''
      END
      INSERT @tblString (Parsed) VALUES(@Parsed)
   END
   RETURN
END

--- test the function
SELECT * FROM dbo.parseAEcountTreeString('BLOCK A/ACC - CONVENTION CENTER/RETAIL/AIS')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP