Numeric value with comma separator...

Hi,
In select statement how will i get the numeric values with comma separated
format .
Is there any sql function available.
Regards,
M. SubbaiahSomeone was asleep in their Database 101 class! What is the **most
fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
IN THE CLIENT SIDE!!
Can you please stop programming until you have read at least one book?|||Hi
As Celko pointed out yet you will be better of douing such reports on the
client side
However T-SQL has an ability to do that .
CREATE TABLE #Test (col INT NOT NULL)
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (10)
INSERT INTO #Test VALUES (20)
DECLARE @.st VARCHAR(20)
SET @.st=''
SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
FROM #test
SELECT LEFT(@.st,LEN(@.st)-1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
> Hi,
> In select statement how will i get the numeric values with comma separated
> format .
> Is there any sql function available.
> Regards,
> M. Subbaiah
>|||Hi Uri Dimant,
Thanks for your information.
I learned new sql function COALESCE( ) and the usage.
My posted query was ,
Suppose in sql table the value is 1234567.45
My out put wiill be 1,234,567.45
Can you please answer the above one.
Regards
M. Subbaiah
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi
> As Celko pointed out yet you will be better of douing such reports on the
> client side
> However T-SQL has an ability to do that .
> CREATE TABLE #Test (col INT NOT NULL)
> INSERT INTO #Test VALUES (1)
> INSERT INTO #Test VALUES (10)
> INSERT INTO #Test VALUES (20)
>
> DECLARE @.st VARCHAR(20)
> SET @.st=''
> SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
> FROM #test
> SELECT LEFT(@.st,LEN(@.st)-1)
>
>
>
> "Subbaiah" <subbaiah@.cspl.com> wrote in message
> news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
>|||NO!
Display is ALWAYS done where it is most efficient to do it.
You DO NOT pull back 1 MILLION rows into your middle tier or client tier
only to grab page 2 of 10!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139978481.527767.63680@.z14g2000cwz.googlegroups.com...
> Someone was asleep in their Database 101 class! What is the **most
> fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
> IN THE CLIENT SIDE!!
> Can you please stop programming until you have read at least one book?
>|||If you want to cheat, use the money data type and convert:
declare @.someFloat money
set @.someFloat = 1234567.45
select convert(varchar(15), @.someFloat, 1)
Gives:
1,234,567.45
Cheers,
Stefan
http://www.fotia.co.uk
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||Hi
declare @.someDEC DECIMAL(18,2)
set @.someDEC = 1234567.45
SELECT CONVERT(VARCHAR,CAST(@.someDEC AS MONEY),1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:uqE9mUgMGHA.2668@.tk2msftngp13.phx.gbl...
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>sql

Numbers less than zero

In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.

SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM dbo.t_Agents LEFT OUTER JOIN
dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID

Any ideas how i do this?

Cheers
Anthony Swift

CASE

WHEN (dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total) < THEN 0

ELSE dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total

END AS amount_outstanding_commission

|||Cheers :)

number range in the IN statement

Can anyone tell me what is wrong with this?
Month({Command.Date_Received}) IN [({?ParamMValue})]
?ParamMValue could have... (2,3,4,5) or (2) or (1,2,3,4,5,6,7,8,9)
Why is it telling me that I need a number range in the IN statement?
What should I do instead?Make sure your parameter is a number data type and not a date or string.
GJ|||your final formula should be in format like this..

month({field}) in [ parameter ] no need to you [(2,3,5)]..


Hope this helps yousql

Number of Updated Rows

I want to get the number of rows updated by an update statement in a stored procedure. Is there a way to do this?

Example:

Declare @.NumRow int;

set @.NumRow = udpate table set Name = 'John Doe' where ID = 123456789;

print @.NumRow;

The above doesn't work.

use the @.@.ROWCOUNT internal; something like:

declare @.updateRows integer

UPDATE yourTable
SET ...
WHERE ...
SET @.updateRows = @.@.ROWCOUNT

Dave

|||

Oh yea, a hidden gotcha:

You need to be careful and make sure that the @.@.ROWCOUNT internal is used IMMEDIATELY after the update statement so that its value does not change. You may get surprised if you have any steps between your update statement and where you used @.@.ROWCOUNT.

Dave

|||Yeah, @.@.rowcount is very good that way.

Something else you might want to be aware of is that you can output the rows from an update query. This gives you the chance to do all kinds of extra analysis on what's changed - not just the number of rows, but all kinds of other details too.

Have a look through http://msdn2.microsoft.com/en-us/ms177564.aspx to see some of the things you can get up to.

Robsql

Number of rows in a table

Hi,
Is there a quicker way to get the number of rows in a table other than the
select statement? I did see that there is a table object but there is no
detailed examples in the books online. Thanks for any help.
EllieP.S. I should have mentioned that my select statement selects a recordset of
the entire table and then gets the recordcount.
"Ellie" <nospam@.nospam.net> wrote in message
news:Op3cPtDcGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a quicker way to get the number of rows in a table other than the
> select statement? I did see that there is a table object but there is no
> detailed examples in the books online. Thanks for any help.
> Ellie
>|||try this..
hope this helps..
select rowcnt from sysindexes where indid in(0,1) and id =
object_id('<table_name>')|||If you need to get an accurate count in SQL 2000, use SELECT COUNT(*). This
is more efficient that the recordset method, unless you need the recordset
for other reasons anyway.
The sysindexes query omnibuzz suggested will return an approximate count in
SQL 2000. The count is accurate in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellie" <nospam@.nospam.net> wrote in message
news:O7AxfvDcGHA.4896@.TK2MSFTNGP03.phx.gbl...
> P.S. I should have mentioned that my select statement selects a recordset
> of the entire table and then gets the recordcount.
> "Ellie" <nospam@.nospam.net> wrote in message
> news:Op3cPtDcGHA.4032@.TK2MSFTNGP02.phx.gbl...
>|||Thanks Dan. You are right. Thanks for pointing it out.
But I have a doubt here. If auto_update_statistics is on for the database
can we be sure of the rowcount or we still cannot rely on it'
and How do you we make it reliable in SQL Server 2005. By giving the async
statistics update option'
--
"Dan Guzman" wrote:

> If you need to get an accurate count in SQL 2000, use SELECT COUNT(*). Th
is
> is more efficient that the recordset method, unless you need the recordset
> for other reasons anyway.
> The sysindexes query omnibuzz suggested will return an approximate count i
n
> SQL 2000. The count is accurate in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O7AxfvDcGHA.4896@.TK2MSFTNGP03.phx.gbl...
>
>|||You should run DBCC Updateusage before querying on sysindexes table
Madhivanan|||Updating of statistics doesn't have anything to do with having updated rowco
unt in the sysindexes
(or 2005 counterpart) tables/views. These are two separate things.
If auto update statistics would also be the one to update rowcnt, then it wo
uld have to be done
reading all rows, which would be disastrous for a clustered index on a table
with, say 10,000,000
rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:F4E4270C-B43B-4B20-81FF-944D88CC3885@.microsoft.com...
> Thanks Dan. You are right. Thanks for pointing it out.
> But I have a doubt here. If auto_update_statistics is on for the database
> can we be sure of the rowcount or we still cannot rely on it'
> and How do you we make it reliable in SQL Server 2005. By giving the async
> statistics update option'
> --
>
>
> "Dan Guzman" wrote:
>|||sp_spaceused @.updateusage=true will correct the row count. However, there
is no telling how long the count will remain good.
In SQL 2005, the engine does a better job of maintaining the sysindexes row
count as changes occur. Theoretically, @.updateusage is not needed to fix
the row count in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:F4E4270C-B43B-4B20-81FF-944D88CC3885@.microsoft.com...
> Thanks Dan. You are right. Thanks for pointing it out.
> But I have a doubt here. If auto_update_statistics is on for the database
> can we be sure of the rowcount or we still cannot rely on it'
> and How do you we make it reliable in SQL Server 2005. By giving the async
> statistics update option'
> --
>
>
> "Dan Guzman" wrote:
>|||I think this explains to me why the Rows count that's displayed in
Enterprise Manager on the Table/Properties screen is sometimes
incorrect. I'm using SQL Server 8.
Are SQL Server 8 and SQL Server 2000 the same version? I'm running on
XP/Pro.
Ron|||Yes, version 8 = SQL 2000 and version 9 = SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1146933523.780782.253580@.j33g2000cwa.googlegroups.com...
>I think this explains to me why the Rows count that's displayed in
> Enterprise Manager on the Table/Properties screen is sometimes
> incorrect. I'm using SQL Server 8.
> Are SQL Server 8 and SQL Server 2000 the same version? I'm running on
> XP/Pro.
> Ron
>sql

Number of records in SQLDataSource/GridView

What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!

I've found 2 GridView properties: pagesize and pagecount. So I can write:

Label1.Text =(GridView1.PageSize*GridView1.PageCount).ToString + " records found."

But the last page of the GridView may contain less than PageSize number. Is it possible to count records number on the last page of the GridView?

|||Have you tried GridView.Rows.Count?|||Yes, I've tried GridView.Rows.Count but it counts records only in current GridView page...|||

Yes you're right. Then the only way I can figure out is to retrieve the row count from SqlDataSource:

DataSourceSelectArguments dssa = new DataSourceSelectArguments();

dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
dssa.RetrieveTotalRowCount = true;

DataView dv = (DataView)SqlDataSource1.Select(dssa);

Response.Write(dv.Table.Rows.Count);

But the dv.Table.Rows.Count will always return 0 if the SqlDataSource has some parameter bound to a control, so confused.

Number formatting in a SQL select statement

Hi

I'm trying to convert and format integer values in a SQL Server select statement to a string
representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).

I've been looking at CAST and CONVERT and think the answers there somewhere. I just don't
seem to be able to work it out.

Anyone out there able to help me please?

Thanks,
Keith.

My suggestion would be to do this on the front end rather than the back end.
In any case, I think you will need to CAST your column as a money data type, and then CONVERT it using style 1, like this:
SELECT
CONVERT(varchar(20),CAST(myColumn AS money) ,1)
This will unfortuately also return the 2 digits after the decimal point. So my next step would be to strip them out.
This seems very messy, though. Hopefully someone else will have a better idea.

|||Yeah, that is messy.
<soapbox>
The first question I have is why isn't this being done in yourpresentation layer? SQL's strong suit is selecting data, not formattingit. Your ASP.NET environment already has tools that make this mucheasier than anything that we can come up with in SQL.
</soapbox>
Even messier would be this code sample, which is a function and/orstored procedure that accomplish what you are looking for. I've neverused it, but it looks right to me.
Jason
Update: Forgot to link - http://www.issociate.de/board/post/176502/How_do_I_format_an_integer.html
|||

Definitely a front-end issue. I've had to use SQL to format results when using SQLMail and it's a nightmare. Possible using combinations of cast, convert, charindex, substring, etc., but a nightmare. Use the front-end.

|||

Try this url it is using Strings and Formatting in the Framework Class library to do custom formatting. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Kind regards,

Gift Peddie

null values return

Hi,
How do I join 2 tables with null value return?
Table1
Month, Year, Hours
Table2
Month, Year, Accounting_hrs
Here is my current statement.
Select Table1.month as MONTH, Table1.year as YEAR,
Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
from Table2,
(
select month, year, sum (hours) as sumhrs
from Table1
where year = 2005
group by month, year
)
where
(
Table1.year = Table2.year
and
Table1.month = Table2.month
)
order by Table1.Month
RESULT:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
I would like the results to look like this:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
Mar 2005 NULL
Apr 2005 NULL
May 2005 NULL
etc.
TIA!!
Joyce
*** Sent via Developersdex http://www.examnotes.net ***Probably the easiest method to solve this problem is the build a calendar ta
ble
that contains a row for every day from now until some arbitrary point in the
future. That makes this problem trival to solve. For example you might have
(granted without the specific DDL of your solution, this may not be perfect)
Create Table Calendar
(
SpecificDate DateTime
, Month TinyInt
, Year SmallInt
, W TinyInt
, Quarter TinyInt
)
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours) / Sum(T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Group By C.Month, C.Year
If you know that for every value in Table2 there exists a value in Table1, t
hen
you can adjust the query slightly like so:
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours / T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join (Table1 As T1
Join Table2 As T2
On T1.Month = T2.Month
And T1.Year = T2.Year)
On C.Month = T1.Month
And C.Year = T1.Year
Group By C.Month, C.Year
This solution of course presumes that Sum(Accounting_Hrs) will not be zero.
It
should also be noted that if either Sum(T1.Hours) or Sum(T2.Accounting_Hrs)
is
null that you will get Null for the result.
HTH,
Thomas|||Actually, as I think about you'll get bad results joining directly to the
calendar table. You would need to group your Calendar table first like so:
Select C.Month, C.Year
, Sum(T1.Hours) As TotalHours
, Sum(T1.Hours) / Sum(T2.Accounting_hrs) As AverageHours
From (
Select C1.Month, C1.Year
From Calendar As C1
Where C1.Year = 2005
Group By C1.Month, C1.Year
) As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Thomas
"Joyce L" <jsh_57@.hotmail.com> wrote in message
news:OdVy8sbSFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> How do I join 2 tables with null value return?
> Table1
> Month, Year, Hours
> Table2
> Month, Year, Accounting_hrs
> Here is my current statement.
> Select Table1.month as MONTH, Table1.year as YEAR,
> Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
> from Table2,
> (
> select month, year, sum (hours) as sumhrs
> from Table1
> where year = 2005
> group by month, year
> )
> where
> (
> Table1.year = Table2.year
> and
> Table1.month = Table2.month
> )
> order by Table1.Month
> RESULT:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
>
> I would like the results to look like this:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
> Mar 2005 NULL
> Apr 2005 NULL
> May 2005 NULL
> .etc.
> TIA!!
> Joyce
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thank you very much Thomas!!!
*** Sent via Developersdex http://www.examnotes.net ***

NULL values - finding an alternative to the IIF statement

since the IIF statement evaluates both the true and false conditions
regardless... I need to find a replacement to it...
my code is:
=IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
I get the #ERROR value in my report for values that are null since the
SUBSTRING of a NULL value generates an error.
I've tried CHOOSE and the SWITCH and they behaved as IIF.
I do not want to handle this on the server side.
Any ideas?
Thanksyou're really better off modifying the query code to replace NULLS with blank
strings before the rpt code even looks at it. anything else will be an epic
kludge.
"roy@.mgk.com" wrote:
> since the IIF statement evaluates both the true and false conditions
> regardless... I need to find a replacement to it...
> my code is:
> =IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
> SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
> I get the #ERROR value in my report for values that are null since the
> SUBSTRING of a NULL value generates an error.
> I've tried CHOOSE and the SWITCH and they behaved as IIF.
> I do not want to handle this on the server side.
> Any ideas?
> Thanks
>|||On Sep 26, 5:20 pm, Carl Henthorn
<CarlHenth...@.discussions.microsoft.com> wrote:
> you're really better off modifying the query code to replace NULLS with blank
> strings before the rpt code even looks at it. anything else will be an epic
> kludge.
> "r...@.mgk.com" wrote:
> > since the IIF statement evaluates both the true and false conditions
> > regardless... I need to find a replacement to it...
> > my code is:
> > =IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
> > SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
> > I get the #ERROR value in my report for values that are null since the
> > SUBSTRING of a NULL value generates an error.
> > I've tried CHOOSE and the SWITCH and they behaved as IIF.
> > I do not want to handle this on the server side.
> > Any ideas?
> > Thanks
argh! was hoping to avoid that...|||And what about this?
=IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Mid ("" &
Fields!SOURCE_TRANSACTION_KEY.Value,3, 2)
Hope this helps.
<roy@.mgk.com> escribió en el mensaje
news:1190895795.174272.317940@.g4g2000hsf.googlegroups.com...
> On Sep 26, 5:20 pm, Carl Henthorn
> <CarlHenth...@.discussions.microsoft.com> wrote:
>> you're really better off modifying the query code to replace NULLS with
>> blank
>> strings before the rpt code even looks at it. anything else will be an
>> epic
>> kludge.
>> "r...@.mgk.com" wrote:
>> > since the IIF statement evaluates both the true and false conditions
>> > regardless... I need to find a replacement to it...
>> > my code is:
>> > =IIF(Fields!SOURCE_TRANSACTION_KEY.Value = nothing, "", Fields!
>> > SOURCE_TRANSACTION_KEY.Value.TOSTRING.SUBSTRING(3, 2))
>> > I get the #ERROR value in my report for values that are null since the
>> > SUBSTRING of a NULL value generates an error.
>> > I've tried CHOOSE and the SWITCH and they behaved as IIF.
>> > I do not want to handle this on the server side.
>> > Any ideas?
>> > Thanks
> argh! was hoping to avoid that...
>

Null to Zero in Select Statement?

How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.

Thanks very much,you can use CASE stmt ...chk BOL for for more info

hth|||Easier, ISNULL(). If the field that might be null is an int called MyField:

SELECT IsNull(MyField,0) FROM table|||or COALESCE is the other option. i'm not always sure when ISNULL or COALESCE is more appropriate

cs|||COALESCE allows as many arguments as you want, that is:

COALESCE(v1,v2,v3,v4,0)

Will return 0 if all of v1 through v4 are null, otherwise the first non-null argument...|||that's right, I remember now. COALESCE is good if you want to pass in multiple values and get a zero if all are null, but not useful if you want to substitute another value for the null. I had forgotten about the multiple argument thing.

cs

NULL output when trying to script objects with SQL-DMO

The following statement SHOULD generate a script of the specified stored
procedure. Instead, it returns NULL. Please tell me what I'm doign wrong:
DECLARE @.oServer int
DECLARE @.method varchar(300)
DECLARE @.TSQL varchar(4000)
DECLARE @.ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
SET @.ScriptType = 1|4|32|64|256|262144
SET @.method = 'Databases("Compass").' +
'StoredProcedures("p_rpt_Distance").Script' +
'(' + CAST (@.ScriptType AS VARCHAR) + ')'
EXEC sp_OAMethod @.oServer, @.method ,
@.TSQL OUTPUT
SELECT @.TSQL
EXEC sp_OADestroy @.oServercongratulations, you've found the slowest possible way to do this ;)
if your sql is going to be <=4000 chars, you should just go against
syscomments. otherwise, your variable won't be big enough, anyway.
if you're dead-set on using the object junk, check the script type.
you've got the script type of 64 (To File only) set, but no file name to
output to.
CadeBryant wrote:
> The following statement SHOULD generate a script of the specified stored
> procedure. Instead, it returns NULL. Please tell me what I'm doign wrong
:
> DECLARE @.oServer int
> DECLARE @.method varchar(300)
> DECLARE @.TSQL varchar(4000)
> DECLARE @.ScriptType int
> EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
> EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
> EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
> SET @.ScriptType = 1|4|32|64|256|262144
> SET @.method = 'Databases("Compass").' +
> 'StoredProcedures("p_rpt_Distance").Script' +
> '(' + CAST (@.ScriptType AS VARCHAR) + ')'
> EXEC sp_OAMethod @.oServer, @.method ,
> @.TSQL OUTPUT
> SELECT @.TSQL
> EXEC sp_OADestroy @.oServer