code snipper -aptak chappan
alter PROC HisabKitab
(
@Item NVARCHAR(100),
@S_Price MONEY,
@B_Price MONEY,
@D_Price MONEY,
@copound_D nvarchar(50),
@Quan INT,
@Category VARCHAR(4)
)
AS
declare
@profit MONEY,
--@Item
nvarchar(50),
@SPrice money,
@BPrice money,
@DPrice MONEY,
@ForeignId INT=1,
@A INT
--@copound_D
nvarvhar(50)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [dbo].[Byap]
([Item]
,[S_Price]
,[B_Price]
,[D_Price]
,[copound_D]
,[Quan]
,[Category])
VALUES
(@Item, @S_Price, @B_Price,@D_Price
,@copound_D
,@Quan
,@Category)
commit TRANSACTION;
BEGIN TRANSACTION
select @S_Price=
@SPrice,@B_Price=
@BPrice
SET @profit= @S_Price-@B_Price
Select @A=ForeignId from MonthlyProfit max
SET @A=@ForeignId+@A
INSERT INTO dbo.MonthlyProfit SELECT
@profit,@Item,@Category,@D_Price,@A
COMMIT TRANSACTION;
END Try
BEGIN CATCH
EXEC dbo.Error_Bhandar;
IF (XACT_STATE())= -1
BEGIN
PRINT N'Maf garnu hola tapai
ko transaction Uncommittable esthiti ma xa.' +
'Transaction roll back garidai'
ROLLBACK TRANSACTION
END;
IF (XACT_STATE())=1
BEGIN
PRINT 'Hajur ko
Transaction Commitable xa'+'Please wait.. commit hudaixa'
COMMIT TRANSACTION;
END;
END CATCH;
END;
USE
[PPIVDummy]
GO
/******
Object: Table [dbo].[Byap] Script Date: 18/10/2019 5:10:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Byap](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Item] [NVARCHAR](100) NOT NULL,
[S_Price] [MONEY] NOT NULL,
[B_Price] [MONEY] NOT NULL,
[D_Price] [MONEY] NULL,
[copound_D] [NVARCHAR](50) NULL,
[Quan] [INT] NULL,
[Category] [VARCHAR](4) NULL,
CONSTRAINT
[PK__Byap__3214EC27E679F5FA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE
[PPIVDummy]
GO
/******
Object: Table [dbo].[MonthlyProfit] Script Date: 18/10/2019 5:11:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MonthlyProfit](
[ProfitId] [INT] IDENTITY(1,1) NOT NULL,
[ProfitEarned] [MONEY] NULL,
[Item] [NVARCHAR](100) NULL,
[Category] [VARCHAR](4) NULL,
[DiscountApplied] [BIT] NULL,
[ForeignId] [INT] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MonthlyProfit] WITH CHECK ADD CONSTRAINT
[FK__MonthlyPr__Forei__412EB0B6] FOREIGN KEY([ForeignId])
REFERENCES
[dbo].[Byap] ([ID])
GO
ALTER TABLE [dbo].[MonthlyProfit]
CHECK CONSTRAINT
[FK__MonthlyPr__Forei__412EB0B6]
GO
USE
[PPIVDummy]
GO
/******
Object: StoredProcedure
[dbo].[Error_Bhandar] Script Date:
18/10/2019 5:11:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Error_Bhandar]
AS
SELECT
ERROR_NUMBER() AS
ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
USE [PPIVDummy]
GO
/******
Object: StoredProcedure
[dbo].[s_Dummy] Script Date:
18/10/2019 5:12:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[s_Dummy] (@limit AS VARCHAR(max),@ArchString VARCHAR(max) OUTPUT)
AS
Declare
@AddString varchar(max)
set
@AddString=@limit
IF
@limit='836106247011'
BEGIN
--PRINT
@bomb;
DECLARE
@counter INT =
1;
WHILE
@counter <= 5
BEGIN
SET
@counter = @counter +
1;
IF
@counter=4
CONTINUE ; -- break will stop loop
at a fixed point and contiue will ignore one part in loop and print the rest
PRINT
@counter;
END; PRINT 'Boodummm'
END;
ELSE IF @limit='836106247012'
BEGIN
PRINT 'Teso vaye misunderstanding ni'
END;
ELSE IF @limit='836106247013'
BEGIN
PRINT 'vanna matra nasakya ho *j lai kei aaudaina'
END;
ELSE IF @limit='624806787033'
BEGIN
DECLARE
@RetrievalRef varchar (MAX),
@TraceNo varchar(max)
DECLARE
cursor_pdx CURSOR
FOR SELECT RetrievalRef,TraceNo
FROM
PWCIVKisanBSSL.. PDXLogTable(NOLOCK)
OPEN
cursor_pdx;
FETCH NEXT FROM cursor_pdx INTO
@RetrievalRef,
@TraceNo;
WHILE @@FETCH_STATUS=0
Begin
PRINT
@RetrievalRef +' '+'corresponds TO' + ''+ @TraceNo
/*FETCH
NEXT FROM cursor_pdx INTO
@RetrievalRef,
@TraceNo;*/
CLOSE cursor_pdx
DEALLOCATE
cursor_pdx;
END
End
ELSE
Begin
SELECT * FROM PWCIVKisanBSSL.. PDXLogTable WHERE
RetrievalRef=@limit
END;
THROW –
raise an exception and transfer the execution
to the CATCH block of a TRY CATCH construct.
THROW vs. RAISERROR
The following table
illustrates the difference between the THROW statement
and RAISERROR statement:
|
RAISERROR |
THROW |
|
The message_id that you pass to RAISERROR must be defined in sys.messages view. |
The error_number parameter
does not have to be defined in the sys.messages view. |
|
The message parameter
can contain printf formatting styles such as %s and %d. |
The message parameter
does not accept printf style formatting. Use FORMATMESSAGE() function to substitute parameters. |
|
The severity parameter
indicates the severity of the exception. |
The severity of the
exception is always set to 16. |
Joins
Every joins require common value ON d.id=m.id
Left join – left part and common part with right are
displayed
Right join- right part and common part with left are
displayed
Inner join- only common part with left and right are
displayed
Self join- joining with own .. here we should define two
different ids to join each other
View
There are two types of view .. One is the temporary view
another is indexed view
In temporary view, it just displays value to user from
different tables and doesn’t allow any modification. It is just a read only
view
Another is indexed view
It is a view, which like database table. In other words, we
can say it can be manipulated
1.
First we we need to bind it with schema
Eg:
Create view v_rajan
With schemabinding
As
2.
We need to define unique clusterd index for the
view
|
3 |
CREATE UNIQUE CLUSTERED INDEX ucidx_product_id ON production.product_master(product_id); |
NOTE: do not use any derived or constraint like sum of two
columns in any table possible.
AVG,
COUNT,SUM,MIN,MAX,GROUPING,STDEV,STDEVP,VAR,VARP (these are considered to be
derived)
Refrence should be taken
from pumori database to create a indexed view….
Comments
Post a Comment