can anyone help me figure out why when i run the following stored
procedure i get the error:
(1460 row(s) affected)
Msg 245, Level 16, State 1, Procedure SP_SALESTRENDS, Line 40
Conversion failed when converting the varchar value 'X' to data type
int.
SP:
--STORED PROCEDURE FOR INVOICE TRENDS:
--To use Stored Procedure use the following code:
--EXEC SP_INSPECTIONSUMRY (MONTH), (OFFICE)
-- (OFFICE) CAN BE: BGR FOR BANGOR, SP FOR SOUTH PORTLAND, NH FOR NEW
HAMPSHIRE, UNH FOR UNH
-- (REPORT) CAN BE: PRODUCT CODE FOR REPORT BROKEN OUT BY PRODUCT CODE
-- EXEC SP_SALESTRENDS BGR, INVOICED, 2006, X
ALTER PROCEDURE SP_SALESTRENDS
@OFFICE VARCHAR(30),
@REPORT VARCHAR(30),
@VARYEAR INT,
@CODE VARCHAR(30)
AS
IF @REPORT='INVOICED'
SELECT YEAR(I.INVOICEDAT) AS VARYEAR, MONTH(I.INVOICEDAT) AS VARMONTH,
SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTN
INTO #TEMP_SALESTRENDS
FROM OPENQUERY(PROJECTS, '
SELECT PROJECT, INVOICEDAT, STOTAL
FROM INVSUMYR') I
LEFT JOIN
(SELECT *
FROM OPENQUERY(PROJECTS, '
SELECT NUMBER, PRODUCT, PERSON
FROM PROJMAST
')) P
ON (LTRIM(I.PROJECT)=LTRIM(P.NUMBER))
LEFT JOIN
(SELECT PC, DESCRIPTN
FROM OPENQUERY(PROJECTS, '
SELECT PC, DESCRIPTN
FROM PRODCODE')) C
ON (C.PC=P.PRODUCT)
GROUP BY YEAR(I.INVOICEDAT), MONTH(I.INVOICEDAT), P.PERSON, P.PRODUCT,
C.DESCRIPTN
ORDER BY VARYEAR, VARMONTH
-- INVOICED REPORT BROKEN OUT BY OFFICE
IF @REPORT='INVOICED' AND @CODE=1 AND @VARYEAR=1234 AND
@OFFICE='NORRIS'
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @CODE!=1 AND @VARYEAR=1234
SELECT VARYEAR, VARMONTH, SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT=@CODE
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED'AND @CODE!=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT=@CODE AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='NORRIS' AND @CODE=1 AND
@VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L') AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4') AND
VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P') AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('U', 'Z', 'R', 'V')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('U', 'Z', 'R', 'V') AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
--END OF SALES TRENDS STORED PROCEDURE
thanks. 4 4801
Conversion failed when converting the varchar value 'X' to data type
int.
It looks to me like the problem code is:
@CODE=1
Here, you are comparing value 'X' (varchar(30)) to 1 (integer). Because
integer has a higher data type precedence than varchar, SQL Server attempts
to convert the 'X' to integer and you get the error.
I see that you are a bit loose about this but it's a good practice to always
enclose literals in single quotes. For example:
EXEC SP_SALESTRENDS 'BGR', 'INVOICED', 2006, 'X'
and
@CODE='1'
--
Hope this helps.
Dan Guzman
SQL Server MVP
<jo***@norrisinc.comwrote in message
news:11********************@m79g2000cwm.googlegrou ps.com...
can anyone help me figure out why when i run the following stored
procedure i get the error:
(1460 row(s) affected)
Msg 245, Level 16, State 1, Procedure SP_SALESTRENDS, Line 40
Conversion failed when converting the varchar value 'X' to data type
int.
SP:
--STORED PROCEDURE FOR INVOICE TRENDS:
--To use Stored Procedure use the following code:
--EXEC SP_INSPECTIONSUMRY (MONTH), (OFFICE)
-- (OFFICE) CAN BE: BGR FOR BANGOR, SP FOR SOUTH PORTLAND, NH FOR NEW
HAMPSHIRE, UNH FOR UNH
-- (REPORT) CAN BE: PRODUCT CODE FOR REPORT BROKEN OUT BY PRODUCT CODE
-- EXEC SP_SALESTRENDS BGR, INVOICED, 2006, X
ALTER PROCEDURE SP_SALESTRENDS
@OFFICE VARCHAR(30),
@REPORT VARCHAR(30),
@VARYEAR INT,
@CODE VARCHAR(30)
AS
IF @REPORT='INVOICED'
SELECT YEAR(I.INVOICEDAT) AS VARYEAR, MONTH(I.INVOICEDAT) AS VARMONTH,
SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTN
INTO #TEMP_SALESTRENDS
FROM OPENQUERY(PROJECTS, '
SELECT PROJECT, INVOICEDAT, STOTAL
FROM INVSUMYR') I
LEFT JOIN
(SELECT *
FROM OPENQUERY(PROJECTS, '
SELECT NUMBER, PRODUCT, PERSON
FROM PROJMAST
')) P
ON (LTRIM(I.PROJECT)=LTRIM(P.NUMBER))
LEFT JOIN
(SELECT PC, DESCRIPTN
FROM OPENQUERY(PROJECTS, '
SELECT PC, DESCRIPTN
FROM PRODCODE')) C
ON (C.PC=P.PRODUCT)
GROUP BY YEAR(I.INVOICEDAT), MONTH(I.INVOICEDAT), P.PERSON, P.PRODUCT,
C.DESCRIPTN
ORDER BY VARYEAR, VARMONTH
-- INVOICED REPORT BROKEN OUT BY OFFICE
IF @REPORT='INVOICED' AND @CODE=1 AND @VARYEAR=1234 AND
@OFFICE='NORRIS'
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @CODE!=1 AND @VARYEAR=1234
SELECT VARYEAR, VARMONTH, SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT=@CODE
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED'AND @CODE!=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT=@CODE AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='NORRIS' AND @CODE=1 AND
@VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L') AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4') AND
VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P') AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('U', 'Z', 'R', 'V')
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
IF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR!=1234
SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNT
FROM #TEMP_SALESTRENDS
WHERE PRODUCT IN ('U', 'Z', 'R', 'V') AND VARYEAR=@VARYEAR
GROUP BY VARYEAR, VARMONTH
ORDER BY VARYEAR, VARMONTH
--END OF SALES TRENDS STORED PROCEDURE
thanks.
I see that you are a bit loose about this but it's a good practice to
always enclose literals in single quotes.
To clarify, I mean enclose *character* literals in single quotes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Thanks, I corrected the syntax on the @ code, and it is working now.
Dan Guzman wrote:
I see that you are a bit loose about this but it's a good practice to
always enclose literals in single quotes.
To clarify, I mean enclose *character* literals in single quotes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Thanks, I corrected the syntax on the @ code, and it is working now.
Dan Guzman wrote:
I see that you are a bit loose about this but it's a good practice to
always enclose literals in single quotes.
To clarify, I mean enclose *character* literals in single quotes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rebecca Lovelace |
last post by:
For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following...
|
by: Jim |
last post by:
For some reason the compiler is telling me that I must declarethe
variable @costcenter_tmp on lines 74 and 98...but if i put a select
statement in ther (for testing) before the loop I get data back...
|
by: pb648174 |
last post by:
I have a pivot table implementation, part of which is posted below. It
returns no errors in query analyzer, but when profiler is run, it shows
that "Error 208" is happening. I looked that up in BOL...
|
by: joe pribele |
last post by:
I have this stored procedure that takes some xml as input. What I need
to is use xml as a table so that I can join other tables to the xml
and get information back that matches the criteria.
I...
|
by: Timothy Perrigo |
last post by:
This bug? feature? caused a bit of havoc for us yesterday...A
reproducible example follows. Essentially, if you have a table with a
primary key called "id", and you create a temp table (via a...
|
by: Timothy Perrigo |
last post by:
(PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6)
I'm working on a function which creates and populates a temporary
table, then returns the number of records it has inserted. I'm getting
an error,...
|
by: wackyphill |
last post by:
If you were doing paging of results on a web page and were interested
in grabbing say records 10-20 of a result set. But also wanted to know
the total # of records in the result set (so you could...
|
by: John Dalberg |
last post by:
I am getting "Input string was not in a correct format" error in a line
like this:
Int32.Parse(((DataRowView)e.Item.DataItem).Row.ItemArray.ToString())
Field #8 comes from a SQL Server stored...
|
by: pukivruki |
last post by:
hi,
I wish to create a temporary table who's name is dynamic based on the
argument.
ALTER PROCEDURE .
@PID1 VARCHAR(50),
@PID2 VARCHAR(50),
@TICKET VARCHAR(20)
|
by: R. K. Wijayaratne |
last post by:
Hi everyone,
I have a SPROC which selects records into a MSSQL #temp table and then
selects the records from that. I drag this SPROC onto the right-hand
pane of the DBML diagram, but the method...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |