November 15, 2006 at 7:02 am
Hi,
i need help where should i incorporate this logic below in my stored procedure.The logic is fine i just need to know where in the stored procedure should i incorporate it.
-------------------------------------------------------------------------------------
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
stored procedure
--------------------------------------------------------------------------------------
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[ClaimCounts_Test] (
[Month_of_file] [datetime] NULL ,
[CalcAction] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CalculatedValue] [int] NULL
) ON [PRIMARY]
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Record Count of Unique Claims' as CalcAction, SPACE(1) as TableValue, COUNT(DISTINCT PolicyNumber) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GO
-----------------------------------------------------------------------------------------------------------------------------------------------
November 15, 2006 at 7:10 am
It should be in a separate procedure that only selects the data.
Don't take this personnally, but have you had any programming training at all *this will help me better answer your questions in the fuiture)?? This is a basic question a programmer could easily figure out!
November 15, 2006 at 8:39 am
so can you help me on this becz when i run my logic in the query analyzer it works fine but how can i incorporate in the proc
November 15, 2006 at 8:42 am
1 - What is your training in programmation and in SQL server 2000?
2 - Why are you creating a permanent table in the proc (recompilations are not performance's best friend)?
3 - The Insert and the selects should be in 2 different procedures. Create a new procedure that selects the data, then call that procedurefrom whereever you want. From the code right after that procedure, or even at the end of the current procedure.
November 15, 2006 at 8:53 am
see you did not understood my question.Regarding my experience yes iam new that is the reason i am asking questions otherwise i would be like you answering questions.Second if you know something be generous to spread it over instead of insulting somebody as it is an open forum so please try to take care of your words in future(Nobody is an expert)
Anyway wht i was trying to ask, was why cant i do the insert and select in the same proc?
November 15, 2006 at 8:56 am
your logic has 1 flaw tho ...
in the select your selecting TWO KNOWN values
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
so you can do ...
SELECT 'Record Count of Unique Claims' UNION ALL SELECT 'Total Record Count' to get the same result... if you need the counts also and the month you can do:
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
SELECT @Month_of_file_filter as Month_of_file, 'Record Count of Unique Claims' as CalcAction, SPACE(1) as TableValue, COUNT(DISTINCT PolicyNumber) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
UNION ALL
SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
or
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
SELECT @Month_of_file_filter as Month_of_file, COUNT (DISTINCT PolicyNumber) as NoOfUniqueClaims,COUNT(*) as NoOfClaims
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
Vasc
November 15, 2006 at 9:10 am
Hi Vasc,
Thanks for the reply but my question is that how do i incorpoarte that logic in my stored procedure.IF I RUN IN ANALYZER IT GIVES ME THE RIGHT RESULTS
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
BELOW IS MY STORE PROCEDURE
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[ClaimCounts_Test] (
[Month_of_file] [datetime] NULL ,
[CalcAction] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CalculatedValue] [int] NULL
) ON [PRIMARY]
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Record Count of Unique Claims' as CalcAction, SPACE(1) as TableValue, COUNT(DISTINCT PolicyNumber) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GO
November 15, 2006 at 9:11 am
"Don't take this personnally" means I don't want to offend but I must know...
Anyhow vasc showed you another way (better) of doing this. Let us know how it works for you!
November 15, 2006 at 9:16 am
CREATE PROCEDURE dbo.SpDemo
AS
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
GO
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[ClaimCounts_Test] (
[Month_of_file] [datetime] NULL ,
[CalcAction] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CalculatedValue] [int] NULL
) ON [PRIMARY]
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Record Count of Unique Claims' as CalcAction, SPACE(1) as TableValue, COUNT(DISTINCT PolicyNumber) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
EXEC dbo.SpDemo
Go
November 15, 2006 at 9:32 am
Thanks for the help but when i execute the proc
it still does not give my sort order which you created in
dbo.SpDemo
November 15, 2006 at 9:40 am
Are you telling me that this works
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
(Sort wise)
But the exact smae code in
dbo.SpDemo
Doesn't work?
November 15, 2006 at 9:47 am
yes ,see when i run this in analyzer it works
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
BUT NOW WHEN I EXECUTE MY PROC
'GE_Claim_Record_CountsTest'
AFTER PASSING PARAMTER
and then look at the table 'ClaimCounts_Test' which i create in my proc it does not sort it over there
November 15, 2006 at 9:51 am
Can you post the version of the porcs you are utilizing pls?
November 15, 2006 at 9:52 am
Asim you better say in words what you try to accomplish. You are allready on a wrong route ...
Just so that you know every time that you CALL your stored procedure you end up inserting in that table DUPLICATE rows (if counters for month don t change)
Vasc
November 15, 2006 at 9:53 am
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[ClaimCounts_Test] (
[Month_of_file] [datetime] NULL ,
[CalcAction] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TableValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CalculatedValue] [int] NULL
) ON [PRIMARY]
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Record Count of Unique Claims' as CalcAction, SPACE(1) as TableValue, COUNT(DISTINCT PolicyNumber) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
EXEC dbo.SpDemo
GO
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply