January 4, 2017 at 11:51 pm
This is my stored procedure using inside my RDL Report
USE [Invoice]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[uspInvoiceLines]
( @InInvoiceNbr int
,@InLinesPerPageint
)
AS
DECLARE @TotalRows int
DECLARE @Remainder int
DECLARE @NumPages int
DECLARE @NextPageRows int
set @TotalRows= 0
SELECT
ROW_NUMBER() OVER( ORDER BY InvProduct ) as InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
into #tempInvoice
FROM Invoice_Products
SET @TotalRows= @@ROWCOUNT
IF @TotalRows=0
BEGIN
WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
,@InInvoiceNbr
,''
,''
,0
,0
,0
,''
,0
,0)
END
END
ELSE
BEGIN
SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder
IF @Remainder !=0
BEGIN
-- Get the current page increase by 1 becasue we have a remainder.
SET @NumPages = @TotalRows/@InLinesPerPage +1
SET @NextPageRows = @NumPages * @InLinesPerPage
WHILE @TotalRows < @NextPageRows -- Add Blank Rows
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
,@InInvoiceNbr
,''
,''
,0
,0
,0
,''
,0,
0)
END
END
END
SELECT * from #tempInvoice order by InvoiceRow asc
return
I can run without error in SSMS. However when I use this sp inside my RDLC, I get Query execution failed for dataset Error converting data type varchar to numeric error. Where did I do wrong?
I am guessing it is because my InvProduct is nvarchar type and it is supposed to be int type in order to get ROW_NUMBER function.
Is there a way to convert it into numeric type?
Kindly help!
January 5, 2017 at 2:10 am
Without having sample data that includes the problem this is going to be very difficult for any of us to trouble shoot.
All I can tell you is that somewhere you have a a value that is not numeric, that is being assigned to a variable or column that is a numeric type.
You need to supply us with DDL and DLM that produces the error so that we can work out where.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 5, 2017 at 3:05 am
You can't run without error in SSMS. No way. First, you're missing a chunk of lines in your SELECT INTO statement. Second, your INSERT statements are trying to insert ten values into nine rows. Why are you using a loop? It's totally necessary and it will perform badly. You can try something like this. It's not the finished thing, because of the incomplete information you've supplied, but if you're serious about fixing this, you'll be able to tweak it so it works for you.WITH N10 AS ( -- generate 10 rows
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
)
, N100 AS ( -- generate 100 rows
SELECT n1.n
FROM N10 n1
CROSS JOIN N10 n2
)
, Numbers AS ( -- number the rows in sequence starting from 1
SELECT ROW_NUMBER() OVER (ORDER BY n) AS m
FROM N100
)
, CountStar AS ( -- get the number of rows in Invoice_Product
SELECT COUNT(*) AS c
FROM Invoice_Product
)
SELECT
s.c + n.m AS InvoiceRow
,@InInvoiceNbr AS CoID
,'' AS InvNo
,'' AS InvProduct
,0 AS InvDesc
,0 AS InvQuantity
,0 AS InvUOM
,'' AS InvUnitPrice
,0 AS InvAmt
FROM Numbers n
JOIN CountStar s ON n.m <= @InLinesPerPage
John
January 6, 2017 at 2:49 am
John, Why you think it will perform badly? Can you elaborate more for your script. I need to loop because I have to add blank rows to those with no data, in order to suit with the report layout.
A Thom, what kind of data can I provide?
January 6, 2017 at 3:01 am
You don't need to loop. I've shown you how to do it without looping. Loops perform badly because rows are inserted one at a time instead of all in one go. I can't elaborate more for my script unless you provide your full stored procedure definition, full table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, and expected results based on that sample data.
John
January 8, 2017 at 7:11 pm
Hi This is my DDL
CREATE TABLE [dbo].[Invoice_Products](
[CoID] [int] NULL,
[InvNo] [int] NULL,
[InvProduct] [nvarchar](255) NULL,
[InvDesc] [nvarchar](255) NULL,
[InvQuantity] [int] NULL,
[InvUOM] [nvarchar](50) NULL,
[InvUnitPrice] [decimal](4, 2) NULL,
[InvAmt] [decimal](5, 2) NULL,
) ON [PRIMARY]
And Here is my Stored Procedure I an using:
ALTER PROCEDURE [dbo].[uspInvoiceLines]
(
@InLinesPerPageint
)
AS
DECLARE @TotalRows int
DECLARE @Remainder int
DECLARE @NumPages int
DECLARE @NextPageRows int
set @TotalRows= 0
SELECT
ROW_NUMBER() OVER( ORDER BY InvProduct)as InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
into #tempInvoice
FROM Invoice_Products
SET @TotalRows= @@ROWCOUNT
IF @TotalRows=0
BEGIN
WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
,''
,0
,''
,''
,''
,''
,0
,0
)
END
END
ELSE
BEGIN
SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder
IF @Remainder !=0
BEGIN
-- Get the current page increase by 1 becasue we have a remainder.
SET @NumPages = @TotalRows/@InLinesPerPage +1
SET @NextPageRows = @NumPages * @InLinesPerPage
WHILE @TotalRows < @NextPageRows -- Add Blank Rows
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
,''
,0
,''
,''
,''
,''
,0
,0
)
END
END
END
SELECT * from #tempInvoice order by InvoiceRow asc
return
Thank you.
I am such a noob when it comes with SQL.
(P.S-actually I am trying to follow from here: https://www.intertech.com/Blog/use-sql-server-reporting-services-to-generate-an-invoice-document/ )
January 8, 2017 at 7:43 pm
Newbi (1/4/2017)
This is my stored procedure using inside my RDL Report
USE [Invoice]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[uspInvoiceLines]
( @InInvoiceNbr int
,@InLinesPerPageint
)
AS
DECLARE @TotalRows int
DECLARE @Remainder int
DECLARE @NumPages int
DECLARE @NextPageRows int
set @TotalRows= 0
SELECT
ROW_NUMBER() OVER( ORDER BY InvProduct ) as InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
into #tempInvoice
FROM Invoice_Products
SET @TotalRows= @@ROWCOUNT
IF @TotalRows=0
BEGIN
WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
,@InInvoiceNbr
,''
,''
,0
,0
,0
,''
,0
,0)
END
END
ELSE
BEGIN
SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder
IF @Remainder !=0
BEGIN
-- Get the current page increase by 1 becasue we have a remainder.
SET @NumPages = @TotalRows/@InLinesPerPage +1
SET @NextPageRows = @NumPages * @InLinesPerPage
WHILE @TotalRows < @NextPageRows -- Add Blank Rows
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
,@InInvoiceNbr
,''
,''
,0
,0
,0
,''
,0,
0)
END
END
END
SELECT * from #tempInvoice order by InvoiceRow asc
return
I can run without error in SSMS. However when I use this sp inside my RDLC, I get Query execution failed for dataset Error converting data type varchar to numeric error. Where did I do wrong?
I am guessing it is because my InvProduct is nvarchar type and it is supposed to be int type in order to get ROW_NUMBER function.
Is there a way to convert it into numeric type?
Kindly help!
First, the script you posted inserts 10 values into 9 columns.
Second, it inserts empty strings into int columns (e.g. InvNo) and zeros to nvarchar columns (e.g. InvDesc).
You need to pay more attention to the scripts you're writing.
_____________
Code for TallyGenerator
January 8, 2017 at 8:26 pm
ALTER PROCEDURE [dbo].[uspInvoiceLines]
(
--@InInvoiceNbr int
@InLinesPerPageint
)
AS
DECLARE @TotalRows int
DECLARE @Remainder int
DECLARE @NumPages int
DECLARE @NextPageRows int
set @TotalRows= 0
SELECT
ROW_NUMBER() OVER( ORDER BY InvProduct)as InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
into #tempInvoice
FROM Invoice_Products
SET @TotalRows= @@ROWCOUNT
IF @TotalRows=0
BEGIN
WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
--,@InInvoiceNbr
,''
,0
,''
,''
,''
,''
,0
,0
)
END
END
ELSE
BEGIN
SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder
IF @Remainder !=0
BEGIN
-- Get the current page increase by 1 becasue we have a remainder.
SET @NumPages = @TotalRows/@InLinesPerPage +1
SET @NextPageRows = @NumPages * @InLinesPerPage
WHILE @TotalRows < @NextPageRows -- Add Blank Rows
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
--,@InInvoiceNbr
,''
,0
,''
,''
,''
,''
,0
,0
)
END
END
END
SELECT * from #tempInvoice order by InvoiceRow asc
return
January 9, 2017 at 2:33 am
Newbi (1/8/2017)
ALTER PROCEDURE [dbo].[uspInvoiceLines]
(
--@InInvoiceNbr int
@InLinesPerPageint
)
AS
DECLARE @TotalRows int
DECLARE @Remainder int
DECLARE @NumPages int
DECLARE @NextPageRows int
set @TotalRows= 0
SELECT
ROW_NUMBER() OVER( ORDER BY InvProduct)as InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
into #tempInvoice
FROM Invoice_Products
SET @TotalRows= @@ROWCOUNT
IF @TotalRows=0
BEGIN
WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
--,@InInvoiceNbr
,''
,0
,''
,''
,''
,''
,0
,0
)
END
END
ELSE
BEGIN
SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder
IF @Remainder !=0
BEGIN
-- Get the current page increase by 1 becasue we have a remainder.
SET @NumPages = @TotalRows/@InLinesPerPage +1
SET @NextPageRows = @NumPages * @InLinesPerPage
WHILE @TotalRows < @NextPageRows -- Add Blank Rows
BEGIN
SET @TotalRows= @TotalRows+1
INSERT #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt
)
VALUES
(@TotalRows
--,@InInvoiceNbr
,''
,0
,''
,''
,''
,''
,0
,0
)
END
END
END
SELECT * from #tempInvoice order by InvoiceRow asc
return
You're still missing your datatypes here:
INSERT INTO #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt)
VALUES (@TotalRows --N/A
,'' --INT, providing VARCHAR
,0 --INT
,'' --nvarchar, providing VARCHAR
,'' --nvarchar, providing VARCHAR
,'' --INT, providing VARCHAR
,'' --nvarchar, providing VARCHAR
,0 --Decimal
,0 --decimal
)
This means that, for example, on the field ColID it will not be populated with '', or NULL, but a 0. Is this intended? If it should be a 0, pass a 0, not a blank string.
Also, NVARCHAR string should be preceded with N. For the above, this following will give the exact same outcome, but represents your data well:
INSERT INTO #tempInvoice
(InvoiceRow,
CoID,
InvNo,
InvProduct,
InvDesc,
InvQuantity,
InvUOM,
InvUnitPrice,
InvAmt)
VALUES (@TotalRows --N/A
,0
,0
,N''
,N''
,0
,N''
,0.00 --This format is optional, but it's a decimal, I felt it was worth showing it
,0.00 --This format is optional, but it's a decimal, I felt it was worth showing it
)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 9, 2017 at 4:20 am
Thank you. How about date time datatype? What are we supposed to give the as datatype when adding empty rows?
January 9, 2017 at 4:28 am
Are your 0's in the previous lines meant to be empty fields? A 0 in an INTEGER Field is not an empty field. Neither is a blank String ('') an empty field.
Empty = NULL, for all datatypes.
Edit: A couple example of why '', and 0, do NOT = "Empty".
Use DevTestDB
CREATE TABLE #SAMPLE (CustomerName VARCHAR(50), Orders INT);
INSERT INTO #SAMPLE (CustomerName)
VALUES ('Steve'),
('John'),
('Jane'),
(''), --This customer doesn't have a name, for some reason
('Sarah');
GO
/*
If you were to COUNT the customers in this table
What number would you expect, 4? If so, you would be wrong.
The correct answer is 5
*/
SELECT COUNT(CustomerName) AS Customers
FROM #SAMPLE;
GO
/*
What you should is use NULLs. as these are not included in the COUNTS, etc.
*/
--Clear the table first
DELETE FROM #SAMPLE;
GO
INSERT INTO #SAMPLE (CustomerName)
VALUES ('Steve'),
('John'),
('Jane'),
(NULL), --This customer doesn't have a name, for some reason
('Sarah');
GO
SELECT COUNT(CustomerName) AS Customers
FROM #SAMPLE;
GO
--Now INTEGERs. The same is true for these. 0 is not the same as NULL.
--Clear the table first
DELETE FROM #SAMPLE;
GO
INSERT INTO #SAMPLE (CustomerName, Orders)
VALUES ('Steve', 0),
('John', 7),
('Jane', 6),
('Greg', 2),
('Sarah',0);
--Again, if I did some simple logic and COUNTED the number of customers that have "orders"
SELECT COUNT(Orders) AS HavePlacedOrders
FROM #SAMPLE;
/*
Again, you get the answer 5, which is wrong. now you could add a WHERE clause here instead
But that does not prove my point, so I'm going to do this instead#
*/
DELETE FROM #SAMPLE;
GO
INSERT INTO #SAMPLE (CustomerName, Orders)
VALUES ('Steve', NULL),
('John', 7),
('Jane', 6),
('Greg', 2),
('Sarah',NULL);
SELECT COUNT(orders) AS HavePlacedOrders
FROM #SAMPLE;
--CLEAN UP
DROP TABLE #SAMPLE
The key thing really, is know your data, however, as I said, a blank string ('') IS NOT an empty field, it's a a field which contains a string of length 0. Just like a 0 is not an empty field, it's a 0. And 0 definitely represents something.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 9, 2017 at 4:20 pm
Newbi (1/9/2017)
Thank you. How about date time datatype? What are we supposed to give the as datatype when adding empty rows?
See if the following example helps
create table #t2(mydate datetime)
insert into #t2
values ('2012-07-08'),(NULL),('2015-04-17')
select * from #t2
Just spell out NULL as I did above
----------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply