Sql Thoughts

Inserting values from one table to another using a single select statement.
In my previous post,
I ‘ve explained how to insert values from one table to another using cursor.we can do the same using a simple select statement

Code Snippet

INSERT INTO Table1 SELECT * FROM Table2

User Defined functions.
We all are familiar with the functions we use in the front end.In same manner, we can use the function in the back end also .Given below is an simple example of how to create a function and how to call it

Code Snippet

CREATE FUNCTION FN_TEST (@FIRST_NAME VARCHAR(50),@LAST_NAME VARCHAR(50))
RETURNS VARCHAR(100)
BEGIN
RETURN(@FIRST_NAME+@LAST_NAME)
END

 SELECT DBO.FN_TEST('Soumya','Joseph') AS CUSTOMER_NAME

Following are some major differences between a stored procedure and user defined functions:-
1. It can be executed using the “SELECT” clause while SP’s can not be.
2.It can not be used in XML FOR clause but SP’s can be used.
3. It does not return output parameters while SP’s return output parameters.
4. If there is an error in It will stop executing. But in SP’s it just ignores the
error and moves to the next statement.
5. It can not make permanent changes to server environments while SP’s can
change some of the server environment.
HappyCoding ! 😉

Advertisements

One response to this post.

  1. Posted by Shobin on November 7, 2010 at 2:03 AM

    Hi,

    For the example of bank transaction you have quoted, i do have a suggestion.. am not an expert…i might be wrong but still…

    I have optimized it in the below format.. correct me if am wrong..

    01 CREATE PROCEDURE [dbo].[SP_UPDATETRANSACTION]
    02 @ACC_FROM VARCHAR(20),@ACC_TO VARCHAR(20),@AMT VARCHAR(20)
    03 AS
    04 DECLARE @BALANCEFROM INT ,@BALANCETO INT ,@DIFF INT
    05
    06 SET @BALANCEFROM =(SELECT BALANCE FROM TransactionDetails WHERE ACCOUNT_NAME =@ACC_FROM)
    07 SET @BALANCETO =(SELECT BALANCE FROM TransactionDetails WHERE ACCOUNT_NAME =@ACC_TO)
    08 SET @DIFF =@BALANCEFROM -@AMT
    09 IF(@DIFF <0)
    10 PRINT 'No sufficient fund for Transfer'
    11 ELSE
    12 BEGIN TRAN
    13 UPDATE TransactionDetails set Balance=@BALANCEFROM-@AMT where ACCOUNT_NAME =@ACC_FROM
    14 UPDATE TransactionDetails set Balance=@BALANCETO+@AMT where ACCOUNT_NAME =@ACC_TO
    15 COMMIT TRAN

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: