none
Don't know how to write a function

    Question

  • I'm quite a noob to TSQL, and I wanted to write a SQL function to retrieve the primary key of a user from a table.

    In case the user is not yet in the table, the function should insert a new user for the names and return the new primary key.

     

    My problem is, I can't find examples how to execute SQL-commands from a function and work with the result. My function should be called like this:

     

    Guid userid = GetUserID("MyDomain\\John");

     

    After reading quite a few pages about CREATE FUNCTION my best guess how such a function would look like is something like this:

     

        CREATE FUNCTION GetUserID

          (@identity nvarchar(50))

          RETURNS uniqueidentifier

        AS

        BEGIN

          DECLARE @return uniqueidentifier

          DECLARE @domain nvarchar(50)

          DECLARE @name nvarchar(50)

          DECLARE @index int

          SET @domain = ''

          SET @index = CHARINDEX('\', @identity)

          IF (@index = 0)

            BEGIN

              @name = @identity

            END

          ELSE

            BEGIN

              @domain = LEFT(@identity,@index-1)

              @name = RIGHT(@identity,LEN(@identity)-@index)

            END

          @return = SELECT [ID] from dbo.LibsBenutzer WHERE (Name = @name) AND (Domain = @domain)

          IF (@return IS NULL)

            BEGIN

              @return = INSERT INTO dbo.[LibsBenutzer] ([Domain], [Name]) OUTPUT inserted.ID VALUES (@domain, @name)

            END

        END

     

        RETURN @return

        END

     

    Of Course this does not work, since I got no clue how to retrieve the values of SQL Queries in a stored function.

     

    Can anyone help me here? Or point me to some reference/guide about how to write stored functions?

     

    thanks,
    Sam

    Wednesday, July 09, 2008 8:35 AM

Answers

  • In SQL Server , UDF(user defined function) has lot of limitation. like you can only do  INSERT/UPDATE/DELETE to a local table variable. ALso you can not execute sp from a function.

     

    You may want to write a sp instead. You can use EXISTS something like this

     

    IF Exists( Select * From dbo.LibsBenutzer WHERE (Name = @name) AND (Domain = @domain))

    Begin

    Update Yourtablename Set Col='somevalue'

    End

    Else

    Begin

    INSERT INTO dbo.[LibsBenutzer] ([Domain], [Name]) VALUES (@domain, @name)

    End

     

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Wednesday, July 09, 2008 10:43 AM
    Moderator

All replies

  • In SQL Server , UDF(user defined function) has lot of limitation. like you can only do  INSERT/UPDATE/DELETE to a local table variable. ALso you can not execute sp from a function.

     

    You may want to write a sp instead. You can use EXISTS something like this

     

    IF Exists( Select * From dbo.LibsBenutzer WHERE (Name = @name) AND (Domain = @domain))

    Begin

    Update Yourtablename Set Col='somevalue'

    End

    Else

    Begin

    INSERT INTO dbo.[LibsBenutzer] ([Domain], [Name]) VALUES (@domain, @name)

    End

     

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Wednesday, July 09, 2008 10:43 AM
    Moderator
  • Madhu,

     

    can a stored procedure return a value?

     

    I need to get the identifier of the row I added or found, therefore I turned to functions - it did not occur to me that sp might return values, too.

     

    Anyway, I still would not know how to program this. SQL is somewhat strange to me...

     

    thanks,

    Sam

    Wednesday, July 09, 2008 11:26 AM
  • Stored procedure can have output parameter.

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

    Wednesday, July 09, 2008 11:47 AM
    Moderator
  • Thats great!

     

    I'll have a look around if I find some site with examples how to program a sp, then. Maybe I've more luck with those than with functions.

     

    thanks,

    Sam

    Wednesday, July 09, 2008 12:08 PM