FWIW: Database Space Used (stored proc)

For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)

Place the code into an admin database or (more risky and less "best practice") directly into your master database.

Usage:
USE MyDatabase
GO

EXEC AdminDatabase.dbo.sp_dbspaceused
GO

CREATE PROCEDURE sp_dbspaceused

as

declare @.id int -- The object id of @.objname.
declare @.pages int -- Working variable for size calc.
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.logsize dec(15)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

/*
** If @.id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @.dbsize = Pages used
** @.bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @.dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @.dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @.logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @.bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @.pagesperMB = 1048576 / @.bytesperpage
/*
select database_name = db_name(),
database_size =
ltrim(str((@.dbsize + @.logsize) / @.pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@.dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @.pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @.pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @.pages = @.pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @.pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

GOI think this one is shorter ;):

select
reserved=(
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)))*8|||Yes it is. No one ever accused me of having an overabundance of imagination.

Thanks for the nice re-write.

Regards,

hmscott|||With compliments to rdjabarov and apologies to those who do this for a living , I offer up this version which will pull the results for each database...

Regards,

hmscott

ALTER PROC sp_dbSpaceUsed

AS

CREATE TABLE #TempSpace (
[Database] varchar(255),
Reserved dec(15),
Index_Size dec(15),
Data dec(15),
Unused dec(15)
)

DECLARE @.sSQL varchar(1000)

SELECT @.sSQL = 'INSERT INTO #TempSpace ([Database], Reserved, Index_Size, Data, Unused)
SELECT
''?'' as [Database],
reserved=(
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255)))*8'

EXEC sp_MSforeachdb @.command1=@.sSQL

SELECT * FROM #TempSpace

DROP TABLE #TempSpace

Functions with global variables

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--

Functions

I have a string argument in a stored procedure that returns a string
value. I would like to replace that string argument with a function.
Does anyone know what the syntax would be?
Here is an example:
exec sp_send_cdosysmail
'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
e-mail','Body of e-mail message'
I would like to replace DBAeMailAddress@.mycompany.com with a function.
I already have the function written and it does work successfully but
not with the function call from within the arguments for the stored
procedure.
If I can find a way to successfully implement this, I can change the
on-call DBA's name in the function instead of within every single
scheduled job.
Toni
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You cannot call a function inside a stored procedure call. But why not
declare a local variable assign a value to the variable (using the function
call) and then use that local variable in the parameter list?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Toni" <teibner@.allina.com> wrote in message
news:e2OjRW7pDHA.2012@.TK2MSFTNGP12.phx.gbl...
> I have a string argument in a stored procedure that returns a string
> value. I would like to replace that string argument with a function.
> Does anyone know what the syntax would be?
> Here is an example:
> exec sp_send_cdosysmail
> 'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
> e-mail','Body of e-mail message'
> I would like to replace DBAeMailAddress@.mycompany.com with a function.
> I already have the function written and it does work successfully but
> not with the function call from within the arguments for the stored
> procedure.
> If I can find a way to successfully implement this, I can change the
> on-call DBA's name in the function instead of within every single
> scheduled job.
>
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Function with unlimited parameters

Is there any way to write procedure with ulimited number of parameters?

Like in COALESCE function. You can pass one or more parameters.

The short answer would be no, it's not.
(there is a hard limit on # parameters, but if you ever get there, you're in deep trouble most likely)

Why would you need it? Don't you know beforehand what the procedure will do?
There is often a higher cost in reaching for the ultimate in generic, instead of specializing, which will do 'less' but with lower overhead and less test/development/maintenance time.

Keep it simple, and it will work forever =:o)

/Kenneth

|||

Nope, you can't even write a function like that where you can skip parameters. I have a date function that I need to be able to pass "unlimited" values to but I have only 16 works right now. Even worse you would have to default every parameter too.

dbo.function ('value1','value2',null,null,null,null,null,null,null,null,null,null,null,null,null,...)

As an alternative (yet more costly method) you could pass a comma delimited list as a string parameter and split it up into your N values. If that is a reasonable possibility, then you can look here for how to do this: http://www.sommarskog.se/arrays-in-sql.html. XML is another possibility. Both of these require work on your end to compile the string of course, so that might not be a great way to go either.

If that doesn't make sense, someone here can write you a query based on the data you have.

|||I believe you can create an extended stored procedure that can take an unlimited number of parameters.|||Maybe You know how?

Some example?|||

Extended stored procedures are marked for deprecation so don't plan on writing new code since you will have to convert again in couple of releases or so. What is the problem you are trying to solve? Why do you need to pass optional parameters? You could also use a temporary table for example to pass the parameter values as rows. So it depends on what functionality you want and why.

|||My question is purely teoretical. There is no some kind of problem I'am trying to solve with this solution.

function vs stored procedure

I know this is a stupid question (actually, maybe its not..?)

They seem to be identical in some ways, but not available to the outside world. what are some differences?i think its the ability to use the UDF inline that makes it easier.If you had to perform the same calculation in a procedure, you'd have to return the value as an output parameter. not necessarily difficult but mnore coding.

heres an answer fromAdvanced SQL Server Stored Procedure Programming Chat

Q: (SRC): Are there inherent performance benefits to using Stored procedure vs. User Defined Functions?

A: There is no difference in performance. Because of the enforced prevention of side-effects in a UDF, only UDFs can be used in queries.

ofcors, UDFs have some limitations too( like you cannot use non-deterministic built-in functions..like getdate() .etc), which am assuming you are aware of.

hth|||To elaborate a bit, a UDF can also return tabular (in addition to scalar) data, and thus can be joined to.

While this might seem similar to a view, it has one substantila advantage: it can be parameterized! So for potentially enormous result sets, a UDF can improve efficiency enormously.|||You also have to be careful using UDFs in other queries cause you can end up calling the func for every row returned and that can be painful. Also there are a number of normal SQL statements you can't do in funcs.

Function that returns a table

I have a function that returns a single row table with two columns:

dbo.Fun1(@.param1) : colA and colB

I tried to create a stored procedure that use this function:

select col1, col2, dbo.Fun1(col1) from table1

The result is : Invalid object name 'dbo.Fun1'

There is no join between table1 and Fun1, how can I select the both columns of Fun1 ?

Thanks in advance.

Long

Can u paste the declaration of the function?|||You are trying to use a table-valued UDF like a scalar UDF which is incorrect. You can use a table-valued function only in the FROM clause or as a table source. In any case, what you are trying to do is not possible in SQL Server 2000 since you can only pass variables or constants as parameters to table-valued UDFs. In SQL Server 2005, you can use the APPLY operator to the same.|||

Thanks, Umachandar,

I have to do the selection like this:

select col1, col2, (select colA from dbo.Fun1(col1) ), ( select colB from dbo.Fun1(col1))

from table1

It works, but I'm not satisfied, as it calculates the function twice.

Any other ideas?

Thanks in advance.

Long

|||

Hi,
due to the fact that you have to execute the statement once per row, there is no way to do it ohter than your mentioned way.

Without knowing your Function I would assume that even this is very wacky, because your Return could return more than one value ?! So you have to make sure from your query / ir function that only one row will be returned.

HTH, Jens Suessmeyer.

|||

I don't see how this will work in SQL2000. If you are on SQL2005 then you can simplify the query by using APPLY operator like:

select t.col1, t.col2, f.colA, f.colB

from table1 as t

cross apply dbo.Fun1(t.col1) as f

Function sequence error with bcp call from a stored procedure

I have a stored procedure that contains a series of bcp calls to export data
.
The bcp calls are executed by xp_cmdshell and are all the same (other than
the table name of course).
I have SET NOCOUNT ON as the first line in the stored procedure and it is
not returning any data. The bcp calls are in the format of:
bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber' queryout
SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
exec @.RC = master..xp_cmdshell @.Query
On one of the bcp shells I'm receiving the following errors but not on
others. The errors are:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL
Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
[Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation failed.
Failed to get the call stack!
ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
@.RETURN_VALUE = N/A
The stored procedure is being called from a .Net class library and is
executing on SQL Server 2000 with following version info:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
Any ideas would be appreciated. Thanks.AV normally means a bug in sqlserver code. I would suggest you contact PSS
regarding this.
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>I have a stored procedure that contains a series of bcp calls to export
>data.
> The bcp calls are executed by xp_cmdshell and are all the same (other than
> the table name of course).
> I have SET NOCOUNT ON as the first line in the stored procedure and it is
> not returning any data. The bcp calls are in the format of:
> bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber'
> queryout
> SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
> exec @.RC = master..xp_cmdshell @.Query
>
> On one of the bcp shells I'm receiving the following errors but not on
> others. The errors are:
> SQLState = S1010, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
> NULL
> Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
> [Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation
> failed.
> Failed to get the call stack!
>
> ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> Server is terminating this process.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
> Server: Msg 11, Level 16, State 1, Line 0
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
> your network documentation.
> @.RETURN_VALUE = N/A
> The stored procedure is being called from a .Net class library and is
> executing on SQL Server 2000 with following version info:
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> Any ideas would be appreciated. Thanks.
>|||AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you elaborat
e?
"oj" wrote:

> AV normally means a bug in sqlserver code. I would suggest you contact PSS
> regarding this.
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>
>|||Jacob,
Sorry. I mean Access Violation and Product Support Services.
http://support.microsoft.com/oas/de...aspx?gprid=2852
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
> AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you
> elaborate?
> "oj" wrote:
>|||Thanks, I'll look into that.
"oj" wrote:

> Jacob,
> Sorry. I mean Access Violation and Product Support Services.
> http://support.microsoft.com/oas/de...aspx?gprid=2852
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
>
>

Function Returns Data Type Error

I am writing my first function and it should be be a very simple one but
I am getting the error:
Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
Syntax error converting the varchar value 'N' to a column of data type int.
Below is the funtion and then the sleect statement that causes the error
----
CREATE FUNCTION InvTypeOther (@.InvoiceID int)
RETURNS varchar(3)
AS
BEGIN
DECLARE @.Type varchar(3)
select @.Type=
(Case InvoiceType.Name
When 'IN' Then 'N'
Else 0
End)
FROM Invoice
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
WHERE (Invoice.ID = @.InvoiceID)
Return @.Type
END
--
Select dbo.InvTypeOther(ID) from Invoice where id = 2525Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||your CASE expression is using type precedence to try to convert 'N' to
the 0 in the else.
not sure what it should be, but it shouldn't be an int. :)
either '0' or '' perhaps [or null]
Mike Harbinger wrote:
> I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type int
.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>|||That was it, thanks guys!
I am used to another programming language where numbers do not have to be
quoted when used in string variables.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OyGttQyEGHA.524@.TK2MSFTNGP09.phx.gbl...
>I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type
> int.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>

Function Performance question

I do have one store procedure which does insert into one table
CREATE PROCEDURE StoreProc1
AS
DECLARE testcursor CURSOR FOR
SELECT col1
FROM table
WHERE Id = @.ID
OPEN testcursor
FETCH NEXT FROM cursor INTO @.col1
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Here i have to use cursor because i am doing some calculation
--here based value of co11
--And then insert into one table
INSERT INTO TESTTABLE
(id,transactiondate...) values (@.value1,@.value2......)
FETCH NEXT FROM testcursor INTO @.col1
END
CLOSE testcursor
DEALLOCATE testcursor
This StoreProc1 i am running every night and which insert approx 500,000
records into TESTTABLE..Now I have a very simple function on TESTTABLE
which is as following..which i use in other store procedures...
CREATE FUNCTION TestFunction
(@.ID as INT,@.dt1 datetime,@.dt2 datetime)
returns money
AS
BEGIN
DECLARE @.retmoney money
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
RETURN @.retmoney
END
so what happen after running StoreProc1 every night...(which insert into
500 K records into TESTTABLE.. My function TestFunction becomes so slow.. it
takes 10 second to run and if i run query of that function
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE createddate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
it get execute in only o seconds...
so why if i run that function it takes long and if i run that same query it
is fast...
Pls let me know.Hi
You may have perform maintainance on this table to update the indexes or
statistics as they could be fragmented or out of date. See DBCC SHOWCONTIG,
DBCC DBREINDEX and UPDATE STATISTICS in books online
John
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6360AC44-D1D9-4D05-91B3-201C58E1B543@.microsoft.com...
>I do have one store procedure which does insert into one table
> CREATE PROCEDURE StoreProc1
> AS
> DECLARE testcursor CURSOR FOR
> SELECT col1
> FROM table
> WHERE Id = @.ID
> OPEN testcursor
> FETCH NEXT FROM cursor INTO @.col1
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --Here i have to use cursor because i am doing some calculation
> --here based value of co11
> --And then insert into one table
> INSERT INTO TESTTABLE
> (id,transactiondate...) values (@.value1,@.value2......)
> FETCH NEXT FROM testcursor INTO @.col1
> END
> CLOSE testcursor
> DEALLOCATE testcursor
> This StoreProc1 i am running every night and which insert approx 500,000
> records into TESTTABLE..Now I have a very simple function on TESTTABLE
> which is as following..which i use in other store procedures...
> CREATE FUNCTION TestFunction
> (@.ID as INT,@.dt1 datetime,@.dt2 datetime)
> returns money
> AS
> BEGIN
> DECLARE @.retmoney money
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> RETURN @.retmoney
> END
>
> so what happen after running StoreProc1 every night...(which insert into
> 500 K records into TESTTABLE.. My function TestFunction becomes so slow..
> it
> takes 10 second to run and if i run query of that function
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE createddate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> it get execute in only o seconds...
> so why if i run that function it takes long and if i run that same query
> it
> is fast...
> Pls let me know.
>

Function inside a store procedure

Is it possible to create a function inside a store procedure use it and at
the end of the procedure drop the function'
Just curious if somebody has made it !!Marco A. Pi?a wrote:
> Is it possible to create a function inside a store procedure use it
> and at the end of the procedure drop the function'
> Just curious if somebody has made it !!
You could...
Create Proc CreateExecDropFunc
as
Begin
Declare @.SQL nvarchar(4000)
Declare @.FuncName nvarchar(36)
Declare @.TestInt int
Set @.FuncName = CAST(NEWID() as nvarchar(36))
-- Watch for line breaks on next line
Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
Exec sp_executesql @.SQL
Print @.SQL
Set @.TestInt = 1
Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
Print @.SQL
Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
Exec sp_executesql @.SQL
End
Go
Exec CreateExecDropFunc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Excellent, very usefull !!!
"David Gugick" wrote:

> Marco A. Pi?a wrote:
> You could...
> Create Proc CreateExecDropFunc
> as
> Begin
> Declare @.SQL nvarchar(4000)
> Declare @.FuncName nvarchar(36)
> Declare @.TestInt int
> Set @.FuncName = CAST(NEWID() as nvarchar(36))
> -- Watch for line breaks on next line
> Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
> Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
> Exec sp_executesql @.SQL
> Print @.SQL
> Set @.TestInt = 1
> Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
> Print @.SQL
> Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
> Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
> Exec sp_executesql @.SQL
> End
> Go
> Exec CreateExecDropFunc
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten

function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten

Function Compiling Error 2005(2153)

Hello!

I have scalar function which calls from stored procedure. When SP recompiling (before start) in profiler I see Exception "Error: 536, Severity: 16, State: 5" - Invalid length parameter passed to the SUBSTRING function. at next line of function (original text changed for easy sample) :

if @.p1=4 set @.s1=substring(@.ipstr,1,@.p1-1)

but at same line like:

if @.p1=4 set @.s1=substring(@.ipstr,1,3)

works good.

Somebody can explain such?

I think you should proivide full repro with variables declaration and initialization.

Don't forget to mention MSSQL version you run this script on.

Function Call - Syntax error

Hi All,
I have defined a function and want to call the same in a stored procedure.
The function call is giving me syntax error! Here is the function call.
function_name(@.param)
What is the correct syntax to call the function?
kdkd
SELECT * FROM dbo.yourfunction('param')
OR
SELECT dbo.Yourfunction('param')
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F46EFF26-10A3-49CF-BF2E-5A5D6C31DE42@.microsoft.com...
> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd|||Here is another example for you.
create function dbo.testf()
returns int
as
begin
return(33)
end
go
print dbo.testf()
go
drop function dbo.testf
HTH, Thanks
ZULFIQAR SYED
"kd" wrote:

> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd|||You have to preface the functionname with "dbo.", as in
dbo.function_name(@.param)
If the function returns a scalar value (integer, float, varchar() etc.. Then
just usethe expression anywhere you would use the intrinsic datatype using
the same syntax
Select dbo.function_name(@.param), ColA, ColB
from table ...
or if it returns a table then use it exactly as you would a table...
Select <stuff> From dbo.function_name(@.param)
or...
Select <stuff>
From Table As T
Join dbo.function_name(@.param) As F
On F.ColA = T.ColA
"kd" wrote:

> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd

Full-Text Search: Prefix / Suffix Search

Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.

Example:

Say I want to find "Terminator" (1984).

I want to be able to use "Term" or "ator" as search results and still return the proper record.

Here is my Stored Procedure creation sql:

CREATE PROCEDURE sps_searchTitles(@.searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@.searchTerm*"')
GO

-- The above does not appear to properly check both prefix ("Term--") and suffix ("--ator") terms.

I am trying to accomplish what is similarly done with LIKE '%term%'.

thanks, YMYou want to use CONTAINS, not FREETEXT. For example:

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO