difference between SP and UDF

(In order to Enlarge images Please Right Click and Open images in New Tab).

 

SQ1

                                                            SANDIP PATIL

                  Stored Procedure (SP) Function (UDF – User Defined  Function)
1 SP can return zero , single or  multiple values. Function must return a single value.(which may be a scalar or a table).
2 We can use transaction in SP.  We can’t use transaction in UDF.
3 SP can have input/output  Parameters Only input parameter.
4 We can call function from SP.  We can’t call SP from function.
4 We can’t use SP in SELECT/WHERE/ HAVING statement. We can use UDF in SELECT/WHERE/ HAVING statement.
5  We can use exception handling using Try-Catch block in SP. We can’t use Try-Catch block in UDF.
Advertisements

SilverLight Debugger enabling

(In order to Enlarge images Please Right Click and Open images in New Tab).

Solution A

Step 1)

sw1

Step 2)  Menu Debug => Attach Debugger

sw2

 

Step 3) Set default web browser

sw3

Step 4)There is xap file generally located in web project\ClientBin directory. Sometimes after build operations this file can’t be replaced and your ProjectDll and your Project PDB files not be sync. This cause wrong line match while debugging or can’t find a debugging file attached caution. I strongly suggest delete all generated files in Bus project and delete Clientbin\ProjectAPPName.xap file. After rebuild all it must be ok!

 

sw4

 

Solution B

If above solution doesn’t work, then

  • reset iis (if you are debugging in that)
  • delete temporary asp.net files (%SystemRoot%\Microsoft.NET\Framework\versionNumber\Temporary ASP.NET Files)
  • clean and rebuild your solution

Thanks……………

STORED PROC IMPLEMENTATION ENTITY FRAMEWORK –EXAMPLE

(In order to Enlarge images Please Right Click and Open images in New Tab).

Let’s see use of SProc with explanation

 

SPP1

 

spp2

spp3

Let’s see how to call SP with Parameters

spp4

 

CODE SNIPPET

SET ANSI_NULLS ON;SET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GOIF EXISTS (SELECT * FROM sysobjects WHERE name = ‘spGetPaymentsByCriteria’ AND xtype in (N’P’, N’PC’)) DROP PROCEDURE [dbo].[spGetDataByCriteria]GO
CREATE PROCEDURE [dbo].[spGetDataByCriteria]( @PatientId   uniqueidentifier, @OtherPayerName   nvarchar(200), @PayerId   uniqueidentifier, @CheckNumber   nvarchar(100), @CheckDate   datetime,)WITHEXEC AS CALLER ASBEGIN
DECLARE @CMD NVARCHAR(MAX) = ”, @CMDWhere NVARCHAR(MAX) = ”, @DBName NVARCHAR(100)= ‘SAN_MSCRM’
CREATE TABLE #ResultTable (PaymentInId UNIQUEIDENTIFIER NOT NULL, ClientId UNIQUEIDENTIFIER, ClientGroupId UNIQUEIDENTIFIER,ClientGroupIdName NVARCHAR(100),PayerId UNIQUEIDENTIFIER)
CREATE TABLE #PaymentInTbl (paymentinId UNIQUEIDENTIFIER)IF (@PatientId IS NOT NULL) BEGIN  INSERT INTO #PaymentInTbl SELECT paymentinId FROM SAN_MSCRM.dbo.inspostingdetail ipd WHERE ipd.patientId = @PatientId END
SET @CMD = ‘INSERT INTO #ResultTableSELECT  pay.paymentinId AS PaymentInId, pay.ClientId AS ClientId,        pay.clientgroupid AS ClientGroupId,        pay.clientgroupidname AS ClientGroupIdName, pay.PayerId AS PayerIdFROM ‘ + @DBName + ‘.dbo.paymentin pay INNER JOIN ‘ + @DBName + ‘.dbo.inspostingdetail insdet ON pay.paymentinId = insdet.PaymentInId INNER JOIN ‘ + @DBName + ‘.dbo.postingbatch pb ON pay.PostingBatchId = pb.postingbatchId LEFT JOIN ‘ + @DBName + ‘.dbo.deposit dep ON pb.DepositId = dep.depositIdWHERE ‘SET @CMDWhere = ”IF (@PatientId IS NOT NULL) SET @CMDWhere += ‘pay.paymentinId IN (SELECT paymentinId FROM #PaymentInTbl)’
IF (@PayerId IS NOT NULL) SET @CMDWhere += CASE WHEN (LEN(@CMDWhere) > 0) THEN ‘ AND ‘ ELSE ” END + ‘pay.PayerId = ”’ + CAST(@PayerId AS NVARCHAR(50)) + ””
IF (@CheckDate IS NOT NULL) SET @CMDWhere += CASE WHEN (LEN(@CMDWhere) > 0) THEN ‘ AND ‘ ELSE ” END +’pay.EffDate >= ”’ +  CAST(@CheckDate AS NVARCHAR(30)) + ”’ and pay.EffDate < ”’ + CAST(DATEADD(day, 1, @CheckDate) AS NVARCHAR(30)) + ””
IF (COALESCE(@CheckNumber, ”) != ”) SET @CMDWhere += CASE WHEN (LEN(@CMDWhere) > 0) THEN ‘ AND ‘ ELSE ” END + ‘pay.ReferenceNumber = ”’ + CAST(@CheckNumber AS NVARCHAR(50)) + ””
EXEC (@CMD + @CMDWhere)
SET @CMD = ‘ SELECT DISTINCT TOP  PaymentInId, ClientId,ClientGroupId,ClientGroupIdName,PayerId FROM #ResultTable’ EXEC sp_executesql @CMD;
DROP TABLE #ResultTable DROP TABLE #PaymentInTblEND
RETURNGO

 

 

var results = context.spGetDataByCriteria(PatientId, OtherPayerName, PayerId, CheckNumber,CheckDate);var results = context.spGetDataByCriteria(PatientId, OtherPayerName, PayerId, CheckNumber,CheckDate);

foreach (var item in results){

PaymentSearchHeaderDTO dto = new PaymentSearchHeaderDTO(); dto.PaymentDetails = new List<PaymentSearchDetailDTO>();

dto.PaymentInId = item.PaymentInId;

dto.ClientId = item.ClientId;        dto.ClientGroupId = item.ClientGroupId;        dto.ClientGroupIdName = item.ClientGroupIdName;        dto.PayerId = item.PayerId; }

The return types for the following stored procedures could not be detected…..

(In order to Enlarge images Please Right Click and Open images in New Tab).

Unrecognised Return Types from a Stored Procedure

Often, when you drag’n’drop a complicated SQL Server stored procedure into a .dbml file, Visual Studio will fail to work out the return type to use.

You’ll then get an error of “The return types for the following stored procedures could not be detected”, and LINQ will assume that your stored procedure returns a return type of a single int value.

 

StoredProcedureReturnType

 

This can often (but not always) be solved by adding one line to the top of your stored procedure.

CREATE PROCEDURE [dbo].[SandipdProcedure]
@CustomerID int
AS
BEGIN
SET NOCOUNT ON

— Make sure Visual Studio RUNS this Stored Procedure, to determine it’s return type
   SET FMTONLY OFF;

—Note—-

 

This is generally used while updating .dbml file 
 

How to prevent to navigate to same state again.(angular JS)

(In order to Enlarge images Please Right Click and Open images in New Tab).

 

Use “$stateChangeStart” event

Use below code for same.Just check your current state and the state where you are supposed to navigate.
angular.module(‘app’).run([‘$rootScope’,’$state’, function ($rootScope,$state) {
$rootScope.$on(‘$stateChangeStart’,function (event, toState, toParams, fromState, fromParams, error){
if ((fromState.name == “SandipPatilHomeState“) && (toState.name == “SandipPatilHomeState“)) {
event.preventDefault();
return $state.go(“SandipPatilHomeState“);
}
return;
});
}

 

Note: event.preventDefault(); Will keeps you on current state

 

bz