help coverting a varchar to a float

Hi,

I'm using the following code to convert a varchar to a float in a trigger.

declare @.acre varchar (6)

set @.acre_size = 0.0

select @.acre = plotsizeacre
from inserted

declare @.num int
select @.num = isnumeric (@.acre)

if @.num = 1
set @.acre_size = @.acre

This normally works fine, but I'm getting errors if the plotsizeacre field is 1,75

Casting to a float or converting to a float also gives errors.

Any ideas how to solve this problem? (The field would normally be filled in properly, eg 1.75).

Thanks in advance,

Ian

You cannot use ISNUMERIC to do strick checking. This function will return 1 for value that can be converted to any of the integer, numeric, float and money data types. The value '1,75' can be converted to money but not float. Your best option is to chnage the schema and modify the column to float. This will require modifications from the client side also to make sure that the value that user enters is typed accordingly. If you have to keep the varchar data type then you will have to perform the cleaning of the value yourself - meaning you have to check for bad formats and convert appropriately or error out gracefully.

Help coverting a stored procedure to a view

Can someone help me convert this stored procedure to a view? It is using two UDFs.

I appreciate this very much!

@.Startdatetime, @.End datetime ASSELECTC.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,dbo.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel, dbo.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description FROMtblClients CINNERJOIN tblPMTs PON C.Client_ID = P.Client_IDINNERJOIN tblPMTReceipts PRON P.PMT_ID = PR.PMT_IDINNERJOINtblClientAddresses AON C.Client_ID = A.Client_IDLEFTOUTER JOINtblSoftCreditsPMTS SCON C.Client_ID = SC.SC_Client_IDWHERE(PR.PaymentDateBETWEEN @.StartAND @.End)GROUPBY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.TitleORDERBY pmtLevelRETURN

Hi,

That stored procedure cannot be converted into a View because of the the parameters in the WHERE clause and the fact that there is a Group By that hides the PaymentDate from the results.

SQL Views do not support parameters. Because the PaymentDate is not part of the output the users of your View would not be able to provide filter by PaymentDate.

The alternative is to convert it to a SQL function that returns a table:

CREATE FUNCTION dbo.MyFunction(@.Startdatetime,
@.End datetime)

RETURNS TABLE
AS
RETURN

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+SUM(COALESCE(SC.SC_AMOUNT,0)))as Description
FROM

tblClients CINNERJOIN
tblPMTs P

ON C.Client_ID = P.Client_IDINNERJOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_IDINNERJOIN
tblClientAddresses A

ON C.Client_ID = A.Client_IDLEFTOUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDateBETWEEN @.StartAND @.End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

This would allow you to pass parameters to the function from a SELECT statement a get a result set without the need to run a stored procedure.

Example of how you would use this function:

SELECT * FROM dbo.MyFunction('1/1/2007','6/1/2007')

Hope this helps!

David

|||

Thank you for sharing with me. I don't think I will be able to use the Table Valued Function with the reporting tool I have. It only works with tables and views.

|||

What is your reporting tool?

Help convert MS Access function to MS SQL User Defined Function

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.
For example:
TrimZero("000000001023") > "1023"
TrimZero("E1025") > "E1025"
TrimZero("000000021021") > "21021"
TrimZero("R5545") > "R5545"
Here is the function that works in access:
Public Function TrimZero(strField As Variant) As String
Dim strReturn As String
If IsNull(strField) = True Then
strReturn = ""
Else
strReturn = strField
Do While Left(strReturn, 1) = "0"
strReturn = Mid(strReturn, 2)
Loop
End If
TrimZero = strReturn
End Function
Is this not possible? I seem not to be able to figure it out onmy own. If I am missing any information in my question, whichinformation should I include?
|||Okay.. with the help from experts-exchange.com I was able to get the answer.
CREATE FUNCTION [dbo].[TrimZero] (@.MyString varchar(50))
RETURNS varchar(50)
AS
BEGIN
If ISNUMERIC(@.MyString) = 1
Begin
While Left (@.MyString,1) = '0'
Begin
Set @.MyString = Right(@.Mystring,Len(@.Mystring) -1)
End
End
Return @.MyString
END
|||A even better function to do the same thing:
create function dbo.trimzero (@.mystring varchar(50))
returns varchar(50)
AS
begin
declare @.myStrInt int
if isnumeric(@.mystring)=1
set @.myString = cast(@.mystring as int)
return @.mystring
end
GO

Help again edit parameters from MDB to ADP

I need to convert this from Jet SqL to SQL Server. Moving out of an MDB to an ADP some of the queries I need to change over, I inherited this database and the bosses want all databases moved over from MDB to ADP.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));

1. For FORMAT$, look in SQL BOL for Convert. Something like Convert(Varchar(7), [Main Table].[Date]

2. For your parameter ([Enter the Month and Year]), you will likely have to create the query as a stored procedure with an input parameter (or two). Something like:

CREATE PROC spMyProc (@.MonthYear varchar(7) )

AS

SELECT ...
FROM
WHERE
MyDateString = @.MonthYear

You would have to validate that @.MonthYear was valid (like mm/yyyy). I would not necessarily do it this way. I would use one of the following options:

1. Declare the incoming parameter as a datetime and calculate the first and last day of the month. Then use two local variables and a BETWEEN statement. This should render the best performance for large amounts of data.

2. or Declare the incoming parameter as two smallint variables. Then you could use DatePart function twice to get month and year.

Regards,

Hugh Scott