[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

Changing into a Function

Hi guys,

I made a nice little bit of code which works perfectly for what I want, but I now want to change it into a function so it can be used with dynamic data. I've started, but now getting some errors, here's the code I have so far:

CREATE FUNCTION HandleData
(
      @data as nvarchar(50)
)
returns @dump TABLE (results nvarchar(50))
AS
BEGIN
      DECLARE c CURSOR FOR
      SELECT * FROM dbo.fn_ParseCSVString (@data,'-')

      DECLARE @varA nvarchar(50)
      DECLARE @varB nvarchar(50)
      DECLARE @varC nvarchar(50)
      DECLARE @varD nvarchar(50)
      DECLARE @varE nvarchar(50)
      DECLARE @varF nvarchar(50)
      DECLARE @varG nvarchar(50)

      OPEN c
      FETCH NEXT FROM c INTO @varA
      FETCH NEXT FROM c INTO @varB
      FETCH NEXT FROM c INTO @varC
      FETCH NEXT FROM c INTO @varD
      FETCH NEXT FROM c INTO @varE
      FETCH NEXT FROM c INTO @varF
      FETCH NEXT FROM c INTO @varG
      CLOSE c
      DEALLOCATE c

      DECLARE @site nvarchar(50)
      DECLARE @room nvarchar(50)
      DECLARE @cab nvarchar(50)
      DECLARE @device nvarchar(50)
      DECLARE @module nvarchar(50)

      SELECT @site    = @varA + '-' + @varB
      SELECT @room    = @varA + '-' + @varB + '-' + @varC
      SELECT @cab     = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
      SELECT @device  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
      SELECT @module  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG

      IF @varG IS NULL
            BEGIN
                  PRINT 'This is a Device'
                  PRINT @device
            END
      ELSE
            BEGIN
                  PRINT 'This is a Module'
                  PRINT @module
            END
END





and the errors are:

Msg 443, Level 16, State 2, Procedure HandleData, Line 44
Invalid use of 'PRINT' within a function.
Msg 443, Level 16, State 2, Procedure HandleData, Line 45
Invalid use of 'PRINT' within a function.
Msg 443, Level 16, State 2, Procedure HandleData, Line 49
Invalid use of 'PRINT' within a function.
Msg 443, Level 16, State 2, Procedure HandleData, Line 50
Invalid use of 'PRINT' within a function.
Msg 455, Level 16, State 2, Procedure HandleData, Line 65535
The last statement included within a function must be a return statement.



If you would like to see the original code before I started to turn it into a function, please just say. I know the first problem is you can't use PRINT inside a function, but instead could I just display the information in a table? I've got a lot to learn, but I find the easiest way for me to learn is to get my hands on some work like this.

Anyone able to help me out?

Thanks again. :)
0
Cyber-Drugs
Asked:
Cyber-Drugs
  • 3
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can't useprint inside the function
0
 
Cyber-DrugsAuthor Commented:
If you read my comment below the errors, you would see I already know this, but am unsure how to return the information in table format.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE FUNCTION HandleData
(
     @data as nvarchar(50)
)
returns @dump TABLE (results nvarchar(50))
AS
BEGIN
     DECLARE c CURSOR FOR
--     SELECT * FROM dbo.fn_ParseCSVString (@data,'-')

     DECLARE @varA nvarchar(50)
     DECLARE @varB nvarchar(50)
     DECLARE @varC nvarchar(50)
     DECLARE @varD nvarchar(50)
     DECLARE @varE nvarchar(50)
     DECLARE @varF nvarchar(50)
     DECLARE @varG nvarchar(50)

     OPEN c
     FETCH NEXT FROM c INTO @varA
     FETCH NEXT FROM c INTO @varB
     FETCH NEXT FROM c INTO @varC
     FETCH NEXT FROM c INTO @varD
     FETCH NEXT FROM c INTO @varE
     FETCH NEXT FROM c INTO @varF
     FETCH NEXT FROM c INTO @varG
     CLOSE c
     DEALLOCATE c

     DECLARE @site nvarchar(50)
     DECLARE @room nvarchar(50)
     DECLARE @cab nvarchar(50)
     DECLARE @device nvarchar(50)
     DECLARE @module nvarchar(50)

     SELECT @site    = @varA + '-' + @varB
     SELECT @room    = @varA + '-' + @varB + '-' + @varC
     SELECT @cab     = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
     SELECT @device  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
     SELECT @module  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG

     IF @varG IS NULL
    INSERT INTO @dump SELECT @device
     ELSE
    INSERT INTO @dump SELECT @module

    RETURN
END
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Cyber-DrugsAuthor Commented:
Ah, so Insert into the virtual table, followed by a RETURN statement.

Thanks again. :)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Remove the comment after the DECLARE Cursor Statement




CREATE FUNCTION dbo.HandleData(@data as nvarchar(50))
RETURNS @dump TABLE (results nvarchar(50))
AS
BEGIN
     DECLARE c CURSOR FOR
     SELECT * FROM dbo.fn_ParseCSVString (@data,'-')

     DECLARE @varA nvarchar(50)
     DECLARE @varB nvarchar(50)
     DECLARE @varC nvarchar(50)
     DECLARE @varD nvarchar(50)
     DECLARE @varE nvarchar(50)
     DECLARE @varF nvarchar(50)
     DECLARE @varG nvarchar(50)

     OPEN c
     FETCH NEXT FROM c INTO @varA
     FETCH NEXT FROM c INTO @varB
     FETCH NEXT FROM c INTO @varC
     FETCH NEXT FROM c INTO @varD
     FETCH NEXT FROM c INTO @varE
     FETCH NEXT FROM c INTO @varF
     FETCH NEXT FROM c INTO @varG
     CLOSE c
     DEALLOCATE c

     DECLARE @site nvarchar(50)
     DECLARE @room nvarchar(50)
     DECLARE @cab nvarchar(50)
     DECLARE @device nvarchar(50)
     DECLARE @module nvarchar(50)

     SELECT @site    = @varA + '-' + @varB
     SELECT @room    = @varA + '-' + @varB + '-' + @varC
     SELECT @cab     = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
     SELECT @device  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
     SELECT @module  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG

     IF @varG IS NULL
    INSERT INTO @dump SELECT @device
     ELSE
    INSERT INTO @dump SELECT @module

    RETURN
END



0
 
imrancsCommented:
Are you looking for some this like this?

CREATE FUNCTION dbo.HandleData
(
     @data as nvarchar(50)
)
returns @dump TABLE (results nvarchar(50))
AS
BEGIN
     DECLARE c CURSOR FOR
     SELECT * FROM dbo.fn_ParseCSVString (@data,'-')

     DECLARE @varA nvarchar(50)
     DECLARE @varB nvarchar(50)
     DECLARE @varC nvarchar(50)
     DECLARE @varD nvarchar(50)
     DECLARE @varE nvarchar(50)
     DECLARE @varF nvarchar(50)
     DECLARE @varG nvarchar(50)

     OPEN c
     FETCH NEXT FROM c INTO @varA
     FETCH NEXT FROM c INTO @varB
     FETCH NEXT FROM c INTO @varC
     FETCH NEXT FROM c INTO @varD
     FETCH NEXT FROM c INTO @varE
     FETCH NEXT FROM c INTO @varF
     FETCH NEXT FROM c INTO @varG
     CLOSE c
     DEALLOCATE c

     DECLARE @site nvarchar(50)
     DECLARE @room nvarchar(50)
     DECLARE @cab nvarchar(50)
     DECLARE @device nvarchar(50)
     DECLARE @module nvarchar(50)

     SELECT @site    = @varA + '-' + @varB
     SELECT @room    = @varA + '-' + @varB + '-' + @varC
     SELECT @cab     = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
     SELECT @device  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
     SELECT @module  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG

     IF @varG IS NULL
          BEGIN
               --PRINT 'This is a Device'
               --PRINT @device
               INSERT INTO @dump Values('This is a Device' + @device)
          END
     ELSE
          BEGIN
               --PRINT 'This is a Module'
               --PRINT @module
               INSERT INTO @dump Values('This is a Module'+ @module)

          END
END

SELECT * FROM dbo.HandleData('you data here')


Imran
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now