Create Table of Expense
USE [AFMS]
GO
/****** Object: Table [dbo].[Expanse] Script Date: 01/03/2012 18:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Expanse](
[ExpanseId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[ExpanseDate] [datetime] NOT NULL,
[Amount] [numeric](18, 0) NOT NULL,
[LNK_File_ID] [numeric](18, 0) NOT NULL,
[LNK_SubFile_ID] [numeric](18, 0) NULL,
[LNK_CreatedBy_ID] [numeric](18, 0) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
CONSTRAINT [PK_Expanse] PRIMARY KEY CLUSTERED
(
[ExpanseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Add Data
-- -----------------------------------------------------------------
-- This script has been automatically generated by SQL Scripter 3.03
-- (unregistered and limited version)
-- 3/1/2012 5:58:34 PM
-- 7 records
-- -----------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[Expanse] ON
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 1)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (1, '20111230', 100, 1, 4, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 1
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 2)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (2, '20111230', 100, 1, 4, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 2
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 3)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (3, '20111230', 111, 2, NULL, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 111, [LNK_File_ID] = 2, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 3
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 6)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (6, '20111229', 110, 1, NULL, 4, '20111229')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111229', [Amount] = 110, [LNK_File_ID] = 1, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111229' WHERE [ExpanseId] = 6
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 7)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (7, '20111229', 15, 1, 4, 4, '20111229')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111229', [Amount] = 15, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111229' WHERE [ExpanseId] = 7
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 8)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (8, '20111228', 19, 1, 5, 4, '20111228')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111228', [Amount] = 19, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 5, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111228' WHERE [ExpanseId] = 8
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 9)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (9, '20111230', 15, 3, 6, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 15, [LNK_File_ID] = 3, [LNK_SubFile_ID] = 6, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 9
SET IDENTITY_INSERT [dbo].[Expanse] OFF
now my Stored Procedure
USE [AFMS]
GO
/****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/03/2012 18:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Created By Archana
-- Create date: 09-12-2011
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]
@LNK_File_ID numeric(18, 0) = NULL,
--@LNK_SubFile_ID numeric(18, 0) = NULL,
@LNK_Client_ID numeric(18, 0) = NULL,
@ExpanseDate datetime = NULL
-- varchar(max)
AS
BEGIN
SET NOCOUNT ON;
SELECT E.[ExpanseId]
,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]
,E.[CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
--,sf.SubFileNo
--,sf.SubFileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
--E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID
--Else E.LNK_SubFile_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
End
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011 12:00:00 AM'
I am Executing this Stored Procedure
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011 12:00:00 AM'
but I am getting Error
Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0
Error converting data type varchar to datetime.
Archana Mistry
Sr. Programmer
Kintudesigns.com