• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Error in stored procedure...SQL 2005 is the server

Hello,
I'm gettting following error when I try to run a stored procedure written on SQL2005 server.
ERROR:Conversion failed when converting the varchar value 'No Load' to data type int.

And below is my stored procedure.I'm new to SP so please give me solutions which are easy to cope with
ALTER PROCEDURE dbo.spdataeval
@carrierid varchar(10)
AS
if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(1,2,3)
begin
SET NOCOUNT ON
declare @loadvalue varchar(100)
 set @loadvalue=(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid ) AND CARRIERRECIPEISACTIVE = 'TRUE')AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
 return @loadvalue
end
 
 else
 /*have more code to be written*/
      return
      RETURN
0
Anushart
Asked:
Anushart
  • 6
  • 5
1 Solution
 
ptjcbCommented:
What is the data type for the parameter value?
SELECT PARAMETERVALUE FROM TBLPARAMETER
0
 
ptjcbCommented:
If parameter is int (and it looks as if it is), then you are trying to compare a varchar value with an int. To do that you must explicitly cast or convert one of the values.

Either
set CAST(@loadvalue as int) =

OR
SELECT CAST(PARAMETERVALUE as varchar(100)) FROM TBLPARAMETER

0
 
AnushartAuthor Commented:
Hello Ptjcb thanks for the reply but they are not working
my PARAMETERVALUE is of type varchar

for the solution you gave
set CAST(@loadvalue as int) =  ....is giving me an error at '='

SELECT CAST(PARAMETERVALUE as varchar(100)) FROM TBLPARAMETER  ...this is giving me the same error as I mentioned
"Conversion failed when converting the varchar value 'No Load' to data type int."





0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
ptjcbCommented:
Where are you getting the column or value 'No Load'?

It is not listed in the procedure.
0
 
AnushartAuthor Commented:
thats the returned value from the database for parameter value.

If there is no value in database it gives as No load.

Parameter column in DB looks lik this

3000
2500
Noload
1000
2000
3000
Noload
.
.
.
Goeson
0
 
ptjcbCommented:
Ah, the problem is
return @loadvalue

The return value in a stored procedure is an EXIT command that returns an integer. In the old days we would use that for error handling.

In stored procedures, if you want to return a value, use the OUTPUT variable.

OUTPUT
Indicates that the parameter is an output parameter. The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.


The following example creates the uspGetList stored procedure. This procedures returns a list of products that have prices that do not exceed a specified amount. The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQL statement to access a value set during the procedure execution.

 Copy Code
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
    , @MaxPrice money
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

 
****
Your procedure should change to something like:

ALTER PROCEDURE dbo.spdataeval
@carrierid varchar(10), @loadvalue varchar(100) OUTPUT
AS
SET NOCOUNT ON
if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(1,2,3)
begin

 set @loadvalue=(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid ) AND CARRIERRECIPEISACTIVE = 'TRUE')AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
end




0
 
AnushartAuthor Commented:
Hurray thanks alot ptjcb it workded ...Can you please hellp me in writting this procedure in correct format...I have no knowledge of SP but I worte this as we write using programming language with comments
Below I am pasting two stored procedures please ask me if you are not clear
1 procedure is SP_DATAEVAL

CREATE PROCEDURE dbo.SP_DATAEVAL
/*below are three inputs for my SP*/
      @carrierid as int,@pulseresitor as double,@onboardres as double
      
AS
if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(2,3)

If(@plugresistor =0)
Print :"Error"
Else
declare @paravalue as varchar(10)
set @paravalue = (SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1))
If @paravalue >= @plugresistor * 0.98 And @paravalue <= @paravalue * 1.02
/* should call a sub procedure
Me assume it as SP_DATAEVAL2 */
Else
Print:"Error"
      /* SET NOCOUNT ON */
      RETURN
-------------------------------------------------------------------------------
2)stored procedure is SP_DATAEVAL2 ( note this SP is working)

CREATE PROCEDURE dbo.SP_DATAEVAL2
      @Carrierid as int
AS
declare @recipetype as int

set @recipetype = (SELECT DISTINCT RECIPETYPE FROM TBLRECIPE WHERE (RECIPEID IN(SELECT RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid)AND CARRIERRECIPEISACTIVE = 'TRUE')))

      /* SET NOCOUNT ON */
      RETURN @recipetype
0
 
ptjcbCommented:
The dbo.sp_dataeval procedure -
I'm not sure why you include the IF statement at the beginning.
Usually the developer would perform the IF statement  in the call to the stored procedure, not within the procedure itself.  As with any code, you want the procedure to be as simple as possible, therefore adding a control flow statement at the beginning tells me that the statement could be called and return a null value. Again, you might have some valid reason for doing that. I'm not a developer, I'm a DBA. It would add a layer of complexity that is not needed.

If(@plugresistor =0)
You have not declare the variable, and it is not a parameter where did the value come from? How can the variable be anything but 0?

Print statements are used to debug a stored procedure - they are not a valid flow point. Will your application know how to handle a PRINT statement?

Do not use RETURN in a stored procedure unless you have a valid reason to exit the code immediately.

Because you are returning one value, these could be user defined functions, which do use the RETURN statement. You should consider making these functions instead of stored procedures. It depends on how your application uses them.



0
 
AnushartAuthor Commented:
Thanks Ptjcb for the reply
i will explain this
First of all I am a .net developer have very poor knowledge on stored procedures ..
My application gets 3 values from a XML file those are
Carriernum,pulseresitor and onboardresistor
My SP started with a If condition because depending upon the data in database I need to make a decision.So inorder to know whats in DB i need a select statement so the If(select statement)

& about the Print...I'm writting a SP for 1st time so I took PRINT as noraml print in my programming language...so where ever there is a PRINT :"Error" ...I need some error statement .....

& I may get plugresistor value as 0 from application  so  if(@plugresitor =0) is the condition..

If you want I can send you my .net code...Let me know if you have more questions


0
 
ptjcbCommented:
Laughing, no thanks. Not a .NET programmer, never want to be. It would be gibberish to me.
0
 
AnushartAuthor Commented:
Hey Ptjcb I got the required stored procedure it is

ALTER PROCEDURE dbo.SP_DATAEVAL
/*below are three inputs for my SP*/
      @carrierid as int,
      @plugresistor as bigint,
      @onboardres as bigint      
AS

if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(2,3)
BEGIN
     If(@plugresistor =0)
     BEGIN
    return (-201)
    end
    Else
    declare @paravalue as varchar(10)
    set @paravalue = (SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM                 TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
     If @paravalue >= @plugresistor * 0.98 And @paravalue <= @paravalue * 1.02
      BEGIN
/* should call a sub procedure
Me assume it as EXEC SP_DATAEVAL2 */
EXEC DATAVAL2 @carrierid
      end
Else

return (-201)
end
Else
BEGIN
 EXEC DATAEVAL2 @carrierid
end

Thanks alot for your support ...Enjoy data administrating :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now