help with a scalare function

i wrote a scalare function-

select name,id

from tableName

where function1(id) / function2(id)>100

but sometimes function2 returns zero so im getting a divide by zero exception.

how can i solve this problem?

thanks in advanced.

? It depends on what do you want for output if function2 returns 0... Here's one way to handle it (return nothing): select name,id from tableName where case function2(id) when 0 then 0 else function1(id) / function2(id) end > 100 ...Keep in mind that using functions in this way is going to force table scans, thereby creating some serious performance issues. You might want to reevaluate the problem you're solving here. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <ppl1@.discussions.microsoft..com> wrote in message news:ea55a515-a0d6-480b-b89a-92cd54cb6d6b@.discussions.microsoft.com... i wrote a scalare function- select name,id from tableName where function1(id) / function2(id)>100 but sometimes function2 returns zero so im getting a divide by zero exception. how can i solve this problem? thanks in advanced.|||ok thanks for the help|||

You can modify your WHERE clause to:

where function1(id) / nullif(function2(id), 0)>100

help with a query

this query don't work

use Common

go

declare @.DateTable Varchar(8)

declare @.TableName Varchar (255)

declare @.Currency Varchar (3)

select substring (TABLE_NAME,1,8) as date FROM information_schema.tables where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'

select substring (TABLE_NAME,9,11) as Currency FROM information_schema.tables where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'

set @.DateTable = date

set @.Currency = Currency

SELECT @.TableName = TABLE_NAME

FROM information_schema.tables

where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'

INSERT INTO [Common].[dbo].[IIM_RM_EXP_EUR_TEMP]

([Sedol]

,[Date]

,[Currency]

,[Name]

,[Candidate]

,[Benchmark]

,[Min]

,[Max]

,[Initial]

,[Alpha]

,[Specific]

,[Total]

,[Price]

,[Beta]

,[Buy.Cost]

,[Sell.Cost]

,[Factor.1]

,[Factor.2]

,[Factor.3]

,[Factor.4]

,[Factor.5]

,[Factor.6]

,[Factor.7]

,[Factor.8]

,[Factor.9]

,[Factor.10]

,[Factor.11]

,[Factor.12]

,[Factor.13]

,[Factor.14]

,[Factor.15]

,[Factor.16]

,[Factor.17]

,[Factor.18]

,[Factor.19]

,[Factor.20]

,[Factor.21]

,[Factor.22]

,[Factor.23]

,[Factor.24]

,[Style.1]

,[Style.2]

,[Style.3]

,[Style.4]

,[Style.5]

,[Country.1]

,[Country.2]

,[Country.3]

,[Country.4]

,[Country.5]

,[Country.6]

,[Country.7]

,[Country.8]

,[Sector.1]

,[Sector.2]

,[Sector.3]

,[Sector.4]

,[Sector.5]

,[Sector.6]

,[Sector.7]

,[Sector.8]

,[Sector.9]

,[Sector.10]

,[Sector.11]

,[Sector.12]

,[Sector.13]

,[Sector.14]

,[Sector.15]

,[Sector.16]

,[Sector.17]

,[Sector.18]

,[Group.1]

,[Group.2]

,[Group.3]

,[Group.4]

,[Group.5]

,[Group.6]

,[Group.7]

,[Group.8]

,[Group.9]

,[Group.10]

)

SELECT [ID]

,'@.DateTable'

,'@.Currency'

,[Name]

,[Candidate]

,[Benchmark]

,cast ([Min] as real)

,cast ([Max] as real)

,cast ([Initial] as real)

,cast ([Alpha] as real)

,cast ([Specific]as real)

,cast ([Total] as real )

,cast ([Price] as real)

,cast([Beta]as real)

,cast([Buy#Cost]as int)

,cast([Sell#Cost]as int)

,cast([Factor#1]as real)

,cast([Factor#2]as real)

,cast([Factor#3]as real)

,cast([Factor#4]as real)

,cast([Factor#5]as real)

,cast([Factor#6]as real)

,cast([Factor#7]as real)

,cast([Factor#8]as real)

,cast([Factor#9]as real)

,cast([Factor#10]as real)

,cast([Factor#11]as real)

,cast([Factor#12]as real)

,cast([Factor#13]as real)

,cast([Factor#14]as real)

,cast([Factor#15]as real)

,cast([Factor#16]as real)

,cast([Factor#17]as real)

,cast([Factor#18]as real)

,cast([Factor#19]as real)

,cast([Factor#20]as real)

,cast([Factor#21]as real)

,cast([Factor#22]as real)

,cast([Factor#23]as real)

,cast([Factor#24]as real)

,cast([Style#1]as Char)

,cast([Style#2]as Char)

,cast([Style#3]as Char)

,cast([Style#4]as Char)

,cast([Style#5]as Char)

,cast([Country#1]as Char)

,cast([Country#2]as Char)

,cast([Country#3]as Char)

,cast([Country#4]as Char)

,cast([Country#5]as Char)

,cast([Country#6]as Char)

,cast([Country#7]as Char)

,cast([Country#8]as Char)

,cast([Sector#1]as Char)

,cast([Sector#2]as Char)

,cast([Sector#3]as Char)

,cast([Sector#4]as Char)

,cast([Sector#5]as Char)

,cast([Sector#6]as Char)

,cast([Sector#7]as Char)

,cast([Sector#8]as Char)

,cast([Sector#9]as Char)

,cast([Sector#10]as Char)

,cast([Sector#11]as Char)

,cast([Sector#12]as Char)

,cast([Sector#13]as Char)

,cast([Sector#14]as Char)

,cast([Sector#15]as Char)

,cast([Sector#16]as Char)

,cast([Sector#17]as Char)

,cast([Sector#18]as Char)

,cast([Group#1]as Char)

,cast([Group#2]as Char)

,cast([Group#3]as Char)

,cast([Group#4]as Char)

,cast([Group#5]as Char)

,cast([Group#6]as Char)

,cast([Group#7]as Char)

,cast([Group#8]as Char)

,cast([Group#9]as Char)

,cast([Group#10]as Char)

FROM [Common].[dbo].[@.TableName]

You can't do:

FROM [Common].[dbo].[@.TableName]

In order to do this, you'll need to dynamically create the insert statement and use EXEC

eg

Code Snippet

DECLARE @.TableName VARCHAR(100)

SET @.TableName = 'sys.objects'
EXEC ('SELECT * FROM ' + @.TableName)

Help variable in SP

Dear All

CREATE PROCEDURE test AS
declare @.tableName varchar(100)
set @.tableName='ABCD'

INSERT INTO @.tableName
(A, B, C, E, F,G,H)
SELECT
A, B, C, D,
COUNT(*) AS G,
CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J
FROM
dbo.EFG
GROUP BY

ORDER BY
XXXX

( error INSERT INTO @.tableName, Do not find @.tableName, must be declare.)

help thanks

William

It appears that you first create @.TableName as a scalar variable, and then you attempt to insert into it as though it were a table variable.

If that properly reflects your intentions, I suggest that you first declare @.TableName as a table variable, with the columns properly defined -then your insert should work.

|||

You are trying to insert the values on the table which is given on the runtime. Typically it is a dynamic insert query.

Use the following query..

Code Snippet

CREATE PROCEDURE test AS

Declare @.tableName varchar(100)

Set @.tableName='ABCD'

Exec ('INSERT INTO ' + @.tableName + '(A, B, C, E, F,G,H)

SELECT

A, B, C, D,

COUNT(*) AS G,

CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J

FROM

dbo.EFG

GROUP BY XXXX

ORDER BY XXXX');

|||How to do?|||Thanks all|||

CREATE PROCEDURE test AS
declare @.tableName varchar(100)
set @.tableName='ABCD'

INSERT INTO @.tableName
(A, B, C, E, F,G,H)
SELECT
A, B, C, D,
COUNT(*) AS G,
CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J
FROM
dbo.EFG
GROUP BY

ORDER BY
XXXX

NOTE: You MUST supply the correct datatypes below. This is a suggested format to DECLARE a table variable and then populate that table variable.

Code Snippet

CREATE PROCEDURE dbo.MyTestProcedure
AS
BEGIN
DECLARE @.MyTable table
( ColA datatype,
ColB datatype,
ColC datatype,
ColD datatype,
ColE datatype,
ColF datatype,
ColG datatype
)

INSERT INTO @.MyTable
SELECT Col1,
Col2,
Col3,
Col4,
COUNT(*),
CONVERT(varchar(10), RecDate, 121),
Left(TransID, 2)
FROM dbo.EFG
GROUP BY
ORDER BY

SELECT *
FROM @.MyTable

END