Parsing values from sp_spaceused stored proc.

I am using sp_spaceused stored procedure to get the database_size result.
The result comes back as a string such as "512 KB" or "100 MB". I really
need a number value representation of the size. I though about parsing the
string, but I was not sure if there were any other results of the string
that I may not account for. Is there any standard way to parse this string,
or should I just assume that the format of the string can either have KB or
MB at the end and parse it based on that assumption?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
--You can look at the contents of the stored proc via the Enterprise Manager
or
via
use master
go
sp_Helptext sp_spaceused
I don't see any values represented other than KB and MB
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken
You can create a table and store an output from sp_spaceused there.
If I understood you need the number only. So see if this helps you.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken,
To be sure, see the sp code from master database.
exec master..sp_helptext sp_spaceused
go
AMB
"Ken Varn" wrote:

> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing th
e
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this strin
g,
> or should I just assume that the format of the string can either have KB o
r
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>
>

Parms

I was wondering how to default an input parm to nulls.
I created a proc which looked liked the following:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID = null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
This compiles, but it does not run correctly. If I change the
declaration of @.ClubID and removed the '=null' - then it compiles, but
it requires the @.ClubID to be populated (will not accept nulls).
How do I define an input parm which is not required to be populated
(accepts nulls and is defaulted to null)?Hi Jim.
The parameter is defined correctly - you should check for null by using IS
NULL rather then = null in the body of the proc:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID is null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
Regards,
Greg Linwood
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9458D9954D842JimHeaveyhotmailcom@.207.46.248.16...
> I was wondering how to default an input parm to nulls.
> I created a proc which looked liked the following:
> ALTER proc dbo.Proc_QuerySkaters
> @.ClubID uniqueidentifier =null,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> if @.ClubID = null
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> set @.cnt = @.@.ROWCOUNT
> end
> else
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> where @.clubID = ClubID
> set @.cnt = @.@.ROWCOUNT
> end
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
> - Error code: ' + cast( @.@.ERROR as char(4) )
> return -1
> end
> set @.msg=''
> return 0
> This compiles, but it does not run correctly. If I change the
> declaration of @.ClubID and removed the '=null' - then it compiles, but
> it requires the @.ClubID to be populated (will not accept nulls).
> How do I define an input parm which is not required to be populated
> (accepts nulls and is defaulted to null)?

Parameters to SP ?

How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.

- CB

Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.

parameters in SP

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade = ' + @.rank

EXEC (@.sql)

GO

when i execute this proc without where condintion its working, but when i use where condition its dispalyin invalid column name with the name im passing

eg.

xxx admin,aB

WHEN I TRY TO EXECUTE PROC WITH ABOVE STAT, ITS DIAPLAYIN ERROR AS "INVALID COLUMN NAME ab

but

xxx admin," ' aB ' "

when i try like this its giving result.

how can i avoid second method of executin the proc and use first method for the sake of passing value from frontend

change your sql query as..

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade =''' + @.rank +'''' [...i added 3 single quotes before + @.rank and 4 single quotes after @.rank +...]

it would work..

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade =''' + @.rank +''''

EXEC (@.sql)

GO

Good Luck./.

|||

Hi,

Your Procedure should be like the following

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable wheregrade = ''' + @.rank + ''''

EXEC (@.sql)

GO

Now Execute your query. It will not raise the error.

I hope this is useful to you.

Cheers,
Ganesh.

|||

Thanks for the responses.

But what logic is this? 3 quotes before and 4 quotes after??

any way i will figure it out.

Thankyou again

Parameterized SSIS Packeges

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

did you try using the execute sql task, mapping the parameter to a variable?|||

JSR2005 wrote:

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

How hard did you look?

Execute SQL Task
(http://www.sqlis.com/default.aspx?58)

-Jamie

|||thank you it is working fine