December 15, 2011 at 8:38 am
Hello All,
I have created a simple stored procedure that will insert the values into a table. When I execute this stored procedure it is saying 0 rows affected. It is not throwing any error. When I execute only the statements inside the sp it is executing perfectly and rows are getting inserted into a table. I dont what the issue is with the stored procedure.My code looks something like this
ALTER PROCEDURE dbo.usp_test
AS
Declare @a int
set @a='some value'
insert into dbo.test
select @a
If I am executing like this EXEC dbo.usp_test it is saying 0 rows affectted.
If I execute like this
Declare @a int
set @a='some value'
insert into dbo.test
select @a
It is executing perfectly. Please assist.
Thanks
December 15, 2011 at 8:45 am
Please post the actual code, not something that kinda looks like it. Details you've removed for simplification may well be the cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2011 at 8:56 am
I found what the issue is. Now I have a different issue with the same sp. This is my actual code
USE [rlk_test]
GO
/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 12/15/2011 09:21:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Dashboard_test]
AS
/*Cut_Date*/
declare @cut_date date
set @cut_date = DATEADD(d,-1,getdate())
/*Invoice Generated*/
declare @invoice_gen decimal(12,2)
set @invoice_gen = (
select SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) line_tot
from billing_payment
where TYPE_DESC != 'Level Pay Contract'
and type_desc != 'Transfer to Payment Contract'
and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Non Regulated',
'Returned Cash - Revenue - Regulated',
'Cash - Revenue - Non Regulated',
'Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Regulated',
'Reversed Cash - Revenue - Non Regulated',
'Deposit Charge - Non Regulated'))
/*Payments Received*/
declare @payment_received decimal(12,2)
set @payment_received = (
select round(SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT),2) 'Payments Recieved'
from billing_payment
where TYPE_DESC = 'Payment')
/*Unclaimed Payments*/
declare @unclaimed_payment decimal(12,2)
set @unclaimed_payment = (
select SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 'unclaimed Payments'
from billing_payment
where (customer_tkn = 42372942
and CUST_STATEMENT_TKN is null
and CURRENT_STMT_DATE is null))
/*Payments_received --- Collections Phase*/
Declare @coll_payment_received decimal(12,2)
set @coll_payment_received= cast((@payment_received - @unclaimed_payment) * -1 as varchar(14));
/*Tdu charges*/
declare @tdu_charge decimal(12,2)
set @tdu_charge = (
select round(SUM(CALC_PRICE_AMT + price_tax_amt),2) 'tdu_charge'
from billing_payment
where TYPE_desc = 'Regular Bill')
Insert into dbo.test
select @cut_date,@invoice_gen,@payments_received,@unclaimed_payments,@coll_payments_received,@tdu_charges
This is my code. My result should be if I execute this stored procedure I will get the output as
2011-12-14 500 600 700 800 900
If I again execute this I dont want the result again to repeat. It shouldn't execute since the data already exists for that particular cut date.
Please let me know what I need to add to the sp to achive this.
Thanks
December 15, 2011 at 9:03 am
So only one row per date?
Easy to do
if not exists
( select cut_date from test where cut_date = @cut_date
)
Insert into dbo.test
select
@cut_date,
@invoice_gen,
@payments_received,
@unclaimed_payments,
@coll_payments_received,
@tdu_charges
end
December 15, 2011 at 9:08 am
Yup. i got it. Thanks a lot for your time.
December 15, 2011 at 9:39 am
I have an other issue with the same stored procedure. I need to get the value for cut date variable from a column in a table. The column is file date and the table name is account. Suppose if I execute the stored procedure for august 5th's data then my cut date variable should get that date (aug 5th) from file date column in account table.
Could anyone tell me how to achieve this.
Thanks
December 15, 2011 at 11:33 am
Please start a separate thread for a new question, and also post some sample data and table DDL. Your description of the problem doesn't make enough sense without an example.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply