Funktioner i en stored procedure

have you looked in books online under stored procedures
The syntax for creating a PROCEDURE is ... and also the data type must be a valid type, I do not think Varchar is (Access!)

CREATE PROC[EDURE] procedure_name [;number]
    [
        {@parameter data_type} [VARYING] [= default] [OUTPUT]
    ]
    [,...n]
[WITH
    {
        RECOMPILE
        | ENCRYPTION
        | RECOMPILE, ENCRYPTION
    }
]
[FOR REPLICATION]
AS
    sql_statement [...n]

An example

D. Use OUTPUT parameters
OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.

First, create the procedure:

USE pubs

GO

IF EXISTS(SELECT name FROM sysobjects

        WHERE name = \'titles_sum\' AND type = \'P\')

    DROP PROCEDURE titles_sum

GO

USE pubs

GO

CREATE PROCEDURE titles_sum @@TITLE varchar(40) = \'%\', @@SUM money OUTPUT

AS

SELECT \'Title Name\' = title

FROM titles

WHERE title LIKE @@TITLE

SELECT @@SUM = SUM(price)

FROM titles

WHERE title LIKE @@TITLE

GO

 

Jeg har læst noget af den dokumentation omkring stored procedure. Men det du kommer med her er da ikke funktion men en procedure... eller?

UCREATE FUNCTION fn_MyFunc

That last comment wast finsihed, so forget it!

CREATE FUNCTION fn_Test
(@myInput nvarchar(30))
RETURNS nvarchar(30)
BEGIN
  IF @myInput IS NULL
  SET @myInput = \"NOT Applicable\"
  RETURN @MyInput
END

You would then use the function as follows.

SELECT LastName, City, dbo.fn_Test(Region) AS Region, Country FROM dbo.Employees

Fedest mand - tusind tak

your welcome, and thank you!

Terry:

I remember someone told me that Funtions are a SQL2000 feature, can you confirm/deny this?

Is it also available in SQL7?

Well I am no expert at MS SQL but as far I as know, user defined functions exist in 7.0 but they have to be in DDL\'s or something. They are very much like stored procedures in 2000.

Userdefined functions does not exist in SQL 7.0. You can, though, create socalled extended stored procedures, which are, as you say, implemented in DLL\'s. But they are not called as functions but as stored procedures.

Functions can - in opposition to stored procedures - be called in the middle of e.g. SELECT\'s as in Terry\'s example.

hi torbenkoch> As I said I am not expert at MS SQL but read books online which stated the following, so I assumed they were called USER DEFINED FUNCTIONS: But I think it sthe same we are talking about. Even so, things are much easier in 2000.

User-defined Functions with MDX Syntax
You can create and register your own functions that operate on multidimensional data. These functions, called “user-defined functions” (UDFs), can accept arguments and return values in the MDX syntax. You can create UDFs using Component Object Model (COM) automation languages such as Microsoft® Visual Basic® or Microsoft Visual C++®. A UDF can be developed on any tool capable of generating Microsoft ActiveX® libraries.

Ahh, well - yes, here we are talking about the Data Warehousing functionality - which I am no expert in, either ;-)

But still, they don\'t look and function exactly like userdefined functions in SQL 2000. But still a good point!