How to get the null values in my stored procedure i am getting error dbnullvalue on front

I have the following stored proc. which i am using on the front end to get all the record from table:

if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

Thank you very much.Note use of IsNull() function.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
IsNull(r.ReviewerComment,''),
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

|||You can use the case statement in sql to look at a field before its returned. For example, you could do this (uses the pubs db)

select au_lname, au_fname,
AuthorCity = Case when (city is null) then '' else city end
from authors

if any city values are null, they'll come back as a '' empty string.

Hope this helps,

Scott

How to get the dates on which a proc is altered ?

Hi All,
I want to see the dates on which a proc is altered in a database.
Is there any way to get this information ?
Regards
Satyatry this...
select LAST_ALTERED from information_Schema.routines
where routine_name = '<proc_name>'
and routine_type = 'procedure'
hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
I am using SQL 2000.
LAST_ALTERED is same as CREATED, eventhough I altered the proc.
Regards
Satya
"Omnibuzz" wrote:

> try this...
> select LAST_ALTERED from information_Schema.routines
> where routine_name = '<proc_name>'
> and routine_type = 'procedure'
> hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Hi
In SQL Server 2000 you can't, however in SQL Server 2005 it is posible ,
lookup modified_date if I rememver well
"Satya" <Satya@.discussions.microsoft.com> wrote in message
news:A0AB9BE9-29E4-4090-84D2-340B5CD44A8E@.microsoft.com...
> Hi,
> I am using SQL 2000.
> LAST_ALTERED is same as CREATED, eventhough I altered the proc.
> Regards
> Satya
>
> "Omnibuzz" wrote:
>|||Oh.. yeah.. Forgot totally about it.
Its not implemented in SQL 2k.. That column was there because of ANSI
compilance view definition and actually holds the created date info :)
SQL Server 2000 does not store the information and you cannot get it.
It was implemented though, in 2k5.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

How to get the database name dynamically?

I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%'

and (DATABASENAME=?)

Can someone help me out?

Thanks.

Try the DB_NAME() function.

|||

DB_NAME() works but if i have to run this query dynamically in the database as per the database parameter how do i do it?

like

declare @.dbname varchar(100)

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%Master'

and DB_NAME=@.dbname

|||

You need to use dynamic SQL to execute commands in multiple databases at run-time. See example below on how to do it the safe way (without SQL injection risks):

Code Snippet

-- SQL Server 2005 version:

-- Will work with minimal changes in SQL Server 2000 too:

declare @.sp nvarchar(500), @.dbname nvarchar(128);

declare @.dbs cursor;

set @.dbs = cursor fast_forward for

select name from sys.databases

where name not in ('master', 'model', 'msdb', 'tempdb');

open @.dbs;

while(1=1)

begin

fetch @.dbs into @.dbname;

if @.@.fetch_status < 0 break;

set @.sp = quotename(@.dbname) + N'.sys.sp_executesql';

exec @.sp N'

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ''BASE TABLE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N''.'' + QUOTENAME(TABLE_NAME)),

''IsMSShipped'') = 0

'

end;

How to get Stored Procedure output ?

I have a variable @.NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @.NetPay. How can I do That?

Set @.NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.

Create Procedure dbo.spGetNetPay (
 @.NetPay Money OUTPUT
) AS
SET @.NetPay = (Select Top 1 NetPay From SomeTable)
GO

|||Post your code for spGetNetPay to enable determine the best solution for you.|||I use Kay Lee's solution. My spGetNetPay is huge, having more than 200 factors to determine the net pay. However, adding an output parameter is an easy job for me:)

I am still interested in if it is possible to code "user defined function". It has its beauty such that you can add it in select statment column list. But it is totally new for me. I even never seen sample code...

how to get result of a dynamic query in a proc

Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
dynamically determined. What are natural ways to receive the value of
cnt and continue to process accordingly within a proc?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Charles,
See if this helps..
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/?id=262499
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Charles Yu" <anonymous@.devdex.com> wrote in message
news:uEDA8V%23SDHA.1572@.TK2MSFTNGP12.phx.gbl...
> Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
> dynamically determined. What are natural ways to receive the value of
> cnt and continue to process accordingly within a proc?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to get recordset from only last SELECT stmt executed??

How to get recordset from only last SELECT stmt executed?

this is part of my code.. in T-Sql

Create proc some mySp
...
AS
...
set nocount on
if (@.SelUserID is null)
select 0
else if (@.SelUserID = @.userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @.incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @.logText
if (@.@.rowcount != 0)
set @.isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do?ANytime you select values without an assignment operator, they will be returned as part of the rowset. It sounds like you want to check for the existence of one rowset before selecting the second. So how about this:


if exists (select 1
from dms_prsn_trx_log
where @.incNo = ult_incid_no
and prsn_trx_no = 10
and trx_log_txt = @.logText)
begin
select b.
end
else
select a.
end|||Thank you it helps..
So, you means there is no such switch thing on off?
I thought it has...-.-

how to get minimum value from a list of user defined values

i've three variables in my stored proc. & i want get the least of them.
i know i can do that using case or if statements but i want to does
T-SQL provides any other way of doing it.
regards,
rameshHere you go
DECLARE @.num1 int
DECLARE @.num2 int
DECLARE @.num3 int
SET @.num1 = 5
SET @.num2 = 3
SET @.num3 = 7
SELECT Min(Num)
FROM
(SELECT @.num1 AS Num
UNION ALL
SELECT @.num2
UNION ALL
SELECT @.num3) T
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<rameshsaive@.gmail.com> wrote in message
news:1139377057.261785.191850@.f14g2000cwb.googlegroups.com...
> i've three variables in my stored proc. & i want get the least of them.
> i know i can do that using case or if statements but i want to does
> T-SQL provides any other way of doing it.
>
> regards,
> ramesh
>

how to get disk total space info?

hi all,
I know that master..xp_fixeddrives can give you the free space info for each
drive on your sql server.
but is there any proc that can return the total disk space?
also, anyone know how to see the code inside of master..xp_fixeddrives?
many thanks!!
JJI don;t knoiw of any way for SQL t odo that... But...
Don't you know how big the disks are ?
These values will not change often.
Create a table and put this data in it...
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||xp_fixeddrives is an extended stored proc. written in C/C++ and compiled.
It most likely calls the GetDiskFreeSpaceEx API function to get the free
space. You could always write your own extended stored proc to make the
same call, but utilize the other information returned like total disk size.
Here's more info. on that:
http://support.microsoft.com/defaul...b;en-us;231497.
It might be easier just to do it from your client app - most languages have
easier ways to get at this particular info., and I think I read that SQL
2005 will also offer easier methods to access this - but someone else will
need to give you specifics on that.
"JJ Wang" <JJ Wang@.discussions.microsoft.com> wrote in message
news:B388A868-9058-45D9-A99F-38BD00B8B0B4@.microsoft.com...
> hi all,
> I know that master..xp_fixeddrives can give you the free space info for
> each
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
/* Returns the total capacity for any drive letter */
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13),
@.drv INTEGER, @.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
David Portas
SQL Server MVP
--|||thank you guys so much for all your useful tips!!
Dave,
thank you so much for the function of yours, it works like a charm!!
have you been using this in your production servers? is there anything I
should look out in this function?
many thanks!!
JJ
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||I have used this in production - not as something to be called
regularly but just as required to refresh a table of drive sizes. I
agree with CBretana that it makes sense to retain this information in a
table but in a large SAN environment with many drive arrays (we were
managing about 15 terabytes across 6 servers) it can be a difficult to
keep that information accurate and up-to-date.
You need to be wary about memory and resource leaks when calling COM
objects with sp_OACreate. I haven't had problems with this one (that's
under SP3, which I assume you are at) but do test it out in your
environment.
David Portas
SQL Server MVP
--|||hi Dave,
thanks again for the wonderful info!
yes, our sql servers are at the highest sp.
how long have you been using this function on your prod servers? and how
often do you refresh your drive size table? Sounds like 'daily' is a bad
idea to run the com objects, huh? :-)
how much memory and cpu in avg do your servers have(which you run this
function on)?
thanks Dave!
JJ
"David Portas" wrote:

> I have used this in production - not as something to be called
> regularly but just as required to refresh a table of drive sizes. I
> agree with CBretana that it makes sense to retain this information in a
> table but in a large SAN environment with many drive arrays (we were
> managing about 15 terabytes across 6 servers) it can be a difficult to
> keep that information accurate and up-to-date.
> You need to be wary about memory and resource leaks when calling COM
> objects with sp_OACreate. I haven't had problems with this one (that's
> under SP3, which I assume you are at) but do test it out in your
> environment.
> --
> David Portas
> SQL Server MVP
> --
>