SQL Miscellanous ...

         TABLE   VALUED UDFs returns results as table data type.

No Begin and End statements are needed.

Create  Function dbo.Udf_ReturnAddress
    (
       @empid    int
    )
RETURNS TABLE
      AS
         RETURN (
                Select * from tbemp where id = @empid
               ) 
GO



Scalar functions need Begin and End Statements.
Returns a Single value of desired type.
Eg.

Create Function  dbo.udf_GetProductStock
(
    @ProductID     int
)
RETURNS INT
    AS
    BEGIN
             DECLARE @retval  int
             SELECT @retval = SUM(ppi.Quantity)   
                     FROM ProductInventory  ppi
                     WHERE ppi.ProductID =@ProductID                                                              
                         
               IF(@retval  is NULL )
                  SET @retval  = 0

        RETURN @retval
    END
   
Eg. Select Name, dbo.udf_GetProductStock(ProductID)  AS Supply FROM Products




 Trigger Eg.
CREATE TABLE   SAL_CHANGE(EmpID int, OldSal  money, NewSal  money, TimeStamp   Date)

CREATE   TRIGGER   TX_SALARY
   ON   tblEmployee
    FOR  UPDATE
      AS
                IF  UPDATE (Salary)
                 BEGIN
                     INSERT INTO SAL_CHANGE (EmpID, OldSal, NewSal,TimeStamp )
                      SELECT d.EmpID, d.Salary, i.Salary, GetDate()
                       FROM deleted   d, inserted   i
                       WHERE  d.empid  = i.empid

                END



 Cursor Eg.
CREATE TABLE #myTemp
(
     UserName varchar(100),
     UserRole varchar(50)
)   
DECLARE myCursor CURSOR
   FOR 
      SELECT Name, Role FROM tblUser


   OPEN myCursor
        DECLARE @uname varchar(100), @role varchar(50)
              FETCH NEXT FROM myCursor INTO @uname, @role
             
                    WHILE @@FETCH_STATUS = 0
                        BEGIN
                                  IF(@role = ‘admin’)
                                        BEGIN
                                           INSERT INTO #myTemp (UserName,UserRole) VALUES (@uname, @role)
                                        END
                                     FETCH_NEXT FROM myCursor INTO @uname, @role
                        END   

     SELECT * FROM #myTemp
   
CLOSE myCursor
DEALLOCATE myCursor














 

Posted in: SQL,WRITEPAD