Example
CREATE PROCEDURE PROC_NAME
(
@MYNAME VARCHAR(10),
@YOURNAME VARCHAR(10),
@RESULT1 VARCHAR(10) OUTPUT,
@RESULT2 VARCHAR(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TABLE_NAME(MYNAME,YOURNAME) VALUES(@MYNAME,@YOURNAME);
SET @RESULT1 = @@IDENTITY
SET @RESULT2=SCOPE_IDENTITY()
END
DECLARE @RESULT1 INT
DELCARE @RESULT2 INT
EXEC PROC_NAME ‘ST1’,’ST2’,@RESULT1 OUTPUT,@RESULT2 OUTPUT
Using SP in ADO.NET
ReplyDelete===================
sqlconnection con=new sqlconnection(configurationmanager.appsettings[“constr”])
sqlcommand cmd= new sqlcommand(“spname”,con)
cmd.commandtype=commandtype.storedprocedure
sqlparameter param=new sqlparameter();
param.parametername=”firstname”
param.parametervalue=”suresh”
param.dbtype=dbtype.string
//Specify the type of parameter - input-only(default), output-only, bidirectional
param.direction=parameterdirection.input
cmd.parameters.add(param)
con.open()
int recordsaffected=cmd.ExectueNonQuery()
con.close()
SCOPE_IDENTITY() vs @@IDENTITY
ReplyDelete-------------------------------
SCOPE_IDENTITY() will return the Identity value generated in a table that is currently in scope.
@@IDENTITY will return the Identity value generated in a table irrespective of the scope.
Example:-
Suppose if we have one trigger defined on table1 that is insert a record in table2 when new record will be inserted into table1.
in this case the Output of Both SCOPE_IDENTITY() and @@IDENTITY will be different.
SCOPE_IDENTITY() will return the identity value of table1 that is in current scope.
while @@IDENTITY will return the identity value of table2.
Debugging of Stored procedure
ReplyDelete------------------------------
Can be done by using server explorer in .net
view http://www.dotnetfunda.com/articles/article27.aspx
storedprocedures vs functions
ReplyDelete-----------------------------
1>Procedure can return zero or n values whereas function can return one value which is mandatory.
2>Procedures can have input,output parameters for it whereas functions can have only input parameters.
3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4>Functions can be called from procedure whereas procedures cannot be called from function.
5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6>We can go for transaction management in procedure whereas we can't go in function.
7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
Extended Stored Procedure
ReplyDelete-------------------------
The Microsoft Extended Stored Procedure API provides a server-based application programming interface (API) for extending Microsoft SQL Server functionality. The API consists of C and C++ functions and macros used to build applications in the following categories: extended stored procedures and gateway applications.
Extended stored procedures allow you to create your own external routines in a programming language such as C. The extended stored procedures appear to users as typical stored procedures and are executed in the same way. Parameters can be passed to extended stored procedures, and they can return results and return status.