SQL Script

USE [Pyramid]
GO

/****** Object:  Table [dbo].[RegistrationForm]    Script Date: 06/14/2019 11:03:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[RegistrationForm](
[Id] [int] IDENTITY(1,1) NOT NULL,
[C_Fname] [varchar](50) NOT NULL,
[C_Lname] [varchar](50) NULL,
[C_Email] [varchar](50) NULL,
[C_Login] [varchar](50) NULL,
[C_Password] [varchar](50) NOT NULL,
[C_MobileNo] [varchar](50) NOT NULL,
[C_Country] [varchar](50) NOT NULL,
[C_Role] [varchar](50) NULL,
[C_IMID] [varchar](50) NOT NULL,
[C_TypeOf] [varchar](50) NULL,
[C_SocialReferennce] [varchar](50) NOT NULL,
[C_SocialProfId] [varchar](50) NULL,
[C_Operation] [varchar](50) NULL,
[UID] [int] NULL,
[FromDate] [date] NULL,
[ToDate] [date] NULL,
[Test] [varchar](50) NULL,
[Test1] [varchar](500) NULL,
[CreatedBy] [int] NULL,
 CONSTRAINT [PK_RegistrationForm] 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],
 CONSTRAINT [UQ_RegistrationForm] UNIQUE NONCLUSTERED
(
[C_Login] 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

ALTER TABLE [dbo].[RegistrationForm]  WITH CHECK ADD  CONSTRAINT [FK__Registrat__Creat__29572725] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[RegistrationForm] ([Id])
GO

ALTER TABLE [dbo].[RegistrationForm] CHECK CONSTRAINT [FK__Registrat__Creat__29572725]
GO

ALTER TABLE [dbo].[RegistrationForm] ADD  CONSTRAINT [DF_RegistrationForm_C_Operation]  DEFAULT ('No') FOR [C_Operation]
GO

-------------------------------------------------
USE [Pyramid]
GO

/****** Object:  Table [dbo].[Setting]    Script Date: 06/14/2019 11:05:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Setting](
[ID] [int] IDENTITY(1,1) NOT NULL,
[User] [varchar](100) NULL,
[Client] [varchar](100) NULL,
[Proc] [varchar](100) NULL,
[Columns] [varchar](max) NULL,
[Status] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

----------------------------------------------
USE [Pyramid]
GO

/****** Object:  StoredProcedure [dbo].[SP_CmmonSetting]    Script Date: 06/14/2019 11:05:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--EXEC SP_CmmonSetting 'admin','121','SP_GetASN'

CREATE PROCEDURE [dbo].[SP_CmmonSetting](
@User VARCHAR(100),@Client VARCHAR(100),@Proc VARCHAR(100)
)
AS
BEGIN
    BEGIN
         DECLARE @Cnt INT
         SET @Cnt=(SELECT [Status] FROM Setting WHERE [User]=@User AND Client=@Client AND [Proc]=@Proc)
         IF @Cnt=1
         BEGIN
              SELECT 1 AS [Status]
              SELECT * FROM Setting WHERE [User]=@User AND Client=@Client AND  [Status]=1
         END
         ELSE
         BEGIN
       
              SELECT 0 AS [Status]
              SELECT * FROM Setting WHERE [User]=@User AND Client=@Client  and [Status]=0
              EXEC @Proc
         END
    END
END
GO

----------------------------------------------
USE [Pyramid]
GO

/****** Object:  StoredProcedure [dbo].[UpdateColumns]    Script Date: 06/14/2019 11:06:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateColumns](
@User VARCHAR(100),@Client VARCHAR(100),@Proc VARCHAR(100),@Columns VARCHAR(MAX)
)
AS
BEGIN
    update Setting set Columns=@Columns,[Status]=1 WHERE [User]=@User AND Client=@Client AND [Proc] =@Proc
END
GO

---------------------------------------------
USE [Pyramid]
GO

/****** Object:  StoredProcedure [dbo].[SP_GetPO]    Script Date: 06/14/2019 11:06:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_GetPO]
AS
BEGIN
     SELECT C_Fname,C_Lname,C_Email,C_Login FROM RegistrationForm
END

GO

--------------------------------
USE [Pyramid]
GO

/****** Object:  StoredProcedure [dbo].[SP_GetASN]    Script Date: 06/14/2019 11:06:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_GetASN]
AS
BEGIN
     SELECT C_Fname,C_Lname FROM RegistrationForm
END

GO

Share: