Help to understand .Net

Store Procedure Function S.P need not be return a value Functions MUST return a value S.P can be called independently using exec keyword whereas Function is called using SELECT statements. S.P can be used for performing business logic Functions are used for computations Can be used EXEC inside an S.P EXEC command can't be used inside a Function S.P takes IN and OUT parameters. Function parameters are always IN , no OUT is possible Transaction related statement can be handled in S.P Can't be handled in function. XML parameters passed in S.P Can’t pass XML Parameters S.P can affect the state of the database by using insert, delete, update and create operations. Functions cannot affect the state of the database which means we

UNION is used to combine the result from multiple SELECT statements into a single result set. If we have a stored procedure and need to call several different times passing in different parameters each time. We would like to collect the results as a single dataset. exec MyStoredProcedure 1 UNION exec MyStoredProcedure 3 UNION exec MyStoredProcedure 5 this shows an syntax above but got the error ... Incorrect syntax near the keyword 'Union'. You'd have to use a temp table like this. CREATE TABLE #TempTABLE { COLUMN1 datatype COLUMN2 datatype } INSERT #TempTABLE exec MyStoredProcedure 1 INSERT #TempTABLE exec MyStoredProcedure 2 INSERT #TempTABLE exec MyStoredProcedure 3 EXAMPLE create table users ( userId int identity ( 1 , 1 ) not null, userName varchar ( 50 ) not null, userPass varchar ( 20 ) not null ) insert into