Calling stored procedure in Microsoft SQL server

Hi all ,
I am using VB6 SP 4 .
Need to call stored procedure from Microsoft SQL Server in VB 6 .
The stored procedure is show below :
update_carrier (user_id, carrier_no, card_lost)" where card_lost = N
Anyone have any sample code that i can take a look is much appreciated .
Thankshttp://msdn.microsoft.com/library/d...
cfn.asp
The fragment you posted is not a valid statement or stored procedure by
itself. It may be part of something else - although it does look a
little strange.
David Portas
SQL Server MVP
--|||Yes . I am new in SQL Stored Procedure .
BTW , how to create stored procedure Microsoft SQL Stored Procedure ?
Any tutorial links is much help to me .
Thanks
David Portas wrote:
> http://msdn.microsoft.com/library/d...r />
_9cfn.asp
> The fragment you posted is not a valid statement or stored procedure by
> itself. It may be part of something else - although it does look a
> little strange.
>|||Lookup CREATE PROCEDURE in Books Online.
The link I posted before is to the web version of Books Online - the
SQL Server documentation set. Books Online (BOL) should be your first
point of reference for SQL Server questions. It should be installed on
your machine as part of your SQL installation, or you can get it here:
http://www.microsoft.com/sql/techin...00/default.mspx
David Portas
SQL Server MVP
--

Calling stored procedure in Microsoft SQL server

Hi all ,
I am using VB6 SP 4 .
Need to call stored procedure from Microsoft SQL Server in VB 6 .
The stored procedure is show below :
?update_carrier (user_id, carrier_no, card_lost)" where card_lost = ?N?
Anyone have any sample code that i can take a look is much appreciated .
Thankshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_9cfn.asp
The fragment you posted is not a valid statement or stored procedure by
itself. It may be part of something else - although it does look a
little strange.
--
David Portas
SQL Server MVP
--|||Yes . I am new in SQL Stored Procedure .
BTW , how to create stored procedure Microsoft SQL Stored Procedure ?
Any tutorial links is much help to me .
Thanks
David Portas wrote:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_9cfn.asp
> The fragment you posted is not a valid statement or stored procedure by
> itself. It may be part of something else - although it does look a
> little strange.
>|||Lookup CREATE PROCEDURE in Books Online.
The link I posted before is to the web version of Books Online - the
SQL Server documentation set. Books Online (BOL) should be your first
point of reference for SQL Server questions. It should be installed on
your machine as part of your SQL installation, or you can get it here:
http://www.microsoft.com/sql/techinfo/productdoc/2000/default.mspx
--
David Portas
SQL Server MVP
--

Calling stored procedure in Microsoft SQL server

Hi all ,
I am using VB6 SP 4 .
Need to call stored procedure from Microsoft SQL Server in VB 6 .
The stored procedure is show below :
update_carrier (user_id, carrier_no, card_lost)" where card_lost = N
Anyone have any sample code that i can take a look is much appreciated .
Thanks
http://msdn.microsoft.com/library/de...prg02_9cfn.asp
The fragment you posted is not a valid statement or stored procedure by
itself. It may be part of something else - although it does look a
little strange.
David Portas
SQL Server MVP
|||Yes . I am new in SQL Stored Procedure .
BTW , how to create stored procedure Microsoft SQL Stored Procedure ?
Any tutorial links is much help to me .
Thanks
David Portas wrote:
> http://msdn.microsoft.com/library/de...prg02_9cfn.asp
> The fragment you posted is not a valid statement or stored procedure by
> itself. It may be part of something else - although it does look a
> little strange.
>
|||Lookup CREATE PROCEDURE in Books Online.
The link I posted before is to the web version of Books Online - the
SQL Server documentation set. Books Online (BOL) should be your first
point of reference for SQL Server questions. It should be installed on
your machine as part of your SQL installation, or you can get it here:
http://www.microsoft.com/sql/techinf...0/default.mspx
David Portas
SQL Server MVP

Calling All T-SQL Gods & Goddesses

Hi

I'm having trouble with the script below that it just won't do a
correct ORDER BY for a date field.

When executing the two SELECT TOP statements on their own the records
are sorted correctly but when wrapped in the main statement, the ORDER
BY is just ignored. Tried to play around with the script but with no
luck. Script is for paging, in this case, select all records meeting
criteria, order and select Top 25 to display on first page. Any
questions, please don't hesitate contacting me.

Thanks very much to anyone who's having a look at this!

Martin

SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
(i2b_address.Address1) AS PropertyAddress,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
ReturnByDate
FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESCOn 29 May 2004 12:19:20 -0700, Martin wrote:

>Hi
>I'm having trouble with the script below that it just won't do a
>correct ORDER BY for a date field.
>When executing the two SELECT TOP statements on their own the records
>are sorted correctly but when wrapped in the main statement, the ORDER
>BY is just ignored. Tried to play around with the script but with no
>luck. Script is for paging, in this case, select all records meeting
>criteria, order and select Top 25 to display on first page. Any
>questions, please don't hesitate contacting me.
>Thanks very much to anyone who's having a look at this!
>Martin
>SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
>i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
>i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
>(i2b_address.Address1) AS PropertyAddress,
>A.ProgUserName AS ProgUserName,
>CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
>TransactionDate,
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
>i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
> (SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC

Hi Martin,

I'm not entirely sure why you would use TOP 0 for the NOT IN subquery, but
that's not the cause of the problem you post about.

The ANSI standard for SQL allows only columns appearing in the SELECT list
in the ORDER BY clause. The rationale being that first, the query gets
executed and the result set is determined; the sorting is a seperate,
final stage. ANSI SQL allows things as ORDER BY 3 (3 being the third
column in the SELECT LIST) or SELECT MyColumn AS ColumnAlias ... ORDER BY
ColumnAlias.

MS SQL Server expands on this standard. With MS SQL Server, you can ORDER
BY on a column not in the SELECT list. If you use a column name that is
not an alias in the column list, SQL Server will try to locate a column
with that name in one of the tables used in the query and use that for
sorting.

Before I get back to your problem, run the following script. It will
clearly show that SQL Server will first attempt to resolve names in the
ORDER BY clause following ANSI standard before looking at the columns that
make up the data.

CREATE TABLE testtab (a int, b int)
go
INSERT testtab (a, b) SELECT 1, 20
INSERT testtab (a, b) SELECT 2, 10
go
SELECT b
FROM testtab
ORDER BY a
go
SELECT b AS a
FROM testtab
ORDER BY a
go
SELECT b AS a, a AS b
FROM testtab
ORDER BY a
go
DROP TABLE testtab
go

In the first query, no column "a" is in the SELECT list, so SQL Server
will sort on the column a in the table. In the second query, the SELECT
list has a column aliased as "a", so this is used for sorting. In the
third query, the column a itself is used in the select list; aliases are
disregarded and sorting is done on the contents of a.

Now back to your real problem. I'll show you the parts that cause the
unexpected ordering:

>SELECT (...)
> (...)
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> (...)
> ORDER BY ReturnByDate DESC

The column ReturnByDate is not used in the SELECT list (the results of a
function are not cosidered equal to the column itself). There is however
an alias ReturnByDate, so there's no need to get back to the data in the
table either - the aliased column in the result set is used. This column
is the output of a CONVERT function call. It's varchar(10), therefor the
sorting will be done by alphabet, descending. "23-04-2003" will go before
"06-05-2004".

The fix? Either use something else as an alias ([Return By Date], for
example) or use ORDER BY CONVERT(datetime, ReturnByDate).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Calling a stored procedure through an UPDATE statement?

Hi,
I'm trying to create several insert statements in a stored procedures
as in below, but it will insert the reocrds sequentially and i want the
stored procedures to populate the fields that are parrallel. I also tried
set rowcount 0 - it didn't accept that and I don't know how to use the
UPDATE for
this like "set Count_H = exec LOE_H_Counts" and it didn't work.
Does anybody know how I can call a stored procedure in an UPDATE clause or
if there is another way to get around this?
Thank you so much for your time!
CREATE Procedure LOE_Counts
as
create table #tempLOE( Status varchar(50) ,Type varchar (50) ,
Count_H int, Count_M int, Count_L int, Count_Total int)
insert #tempLOE(id, Status, TYPE, Count_Total)
exec LOE_GrandTotal
insert #tempLOE(Count_H)
exec LOE_H_Count
insert #tempLOE(Count_M)
exec LOE_M_Count
select * from #tempLOEYou might want to look into user defined functions. You can use them to
return tables or scalar values.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
"SK" <SK@.discussions.microsoft.com> wrote in message
news:AB73E48F-B1D0-43FA-8C34-F1F60A607C10@.microsoft.com...
> Hi,
> I'm trying to create several insert statements in a stored procedures
> as in below, but it will insert the reocrds sequentially and i want the
> stored procedures to populate the fields that are parrallel. I also tried
> set rowcount 0 - it didn't accept that and I don't know how to use the
> UPDATE for
> this like "set Count_H = exec LOE_H_Counts" and it didn't work.
> Does anybody know how I can call a stored procedure in an UPDATE clause or
> if there is another way to get around this?
> Thank you so much for your time!
> CREATE Procedure LOE_Counts
> as
> create table #tempLOE( Status varchar(50) ,Type varchar (50) ,
> Count_H int, Count_M int, Count_L int, Count_Total int)
> insert #tempLOE(id, Status, TYPE, Count_Total)
> exec LOE_GrandTotal
> insert #tempLOE(Count_H)
> exec LOE_H_Count
> insert #tempLOE(Count_M)
> exec LOE_M_Count
> select * from #tempLOE|||Unfortunately you cannot use the the result from the stored procedure
automatically for an update
The round about way is to set an output parameter in the called SP
and then store it in a variable and use it for update.
if the SP returns a result set, then store it in a temp table as you had
mentioned and join it in the update clause.
Hope this helps|||>>I'm trying to create several insert statements in a stored procedures
as in below, but it will insert the records [sic] sequentially and i
want the
stored procedures to populate the fields [sic] that are parrallel
[unh?]. <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.
In SQL, the INSERT, UPDATE and DELETE statements work on rows as a
unit. This is not a file system, wher you can move a read/write head
to a field and do your work. This is what happens when you use the
wrong words.
I will not even get into the vagueness of "id", "type" and "status" as
attempts at data element names.
Next, good SQL programmers avoid UDF and procedural code. That is 3GL
programming and not declarative coding.|||Thank you Omnibuzz & Chuck,
This is finally what worked. It may not be the best way, but it'll do for n
ow
until I try your suggestions about output parameters and UDF, which I have
not done before.
CREATE Procedure LOE_Counts
as
create table #tempLOE( Status varchar(50) ,Type varchar (50) ,
Count_Total int)
insert #tempLOE( Status, TYPE, Count_Total)
exec LOE_GrandTotal
create table #Hcount(Hstatus varchar(50), Htype varchar (50),Count_H int)
insert #HCount(Hstatus, Htype, Count_H)
exec LOE_H_Count
create table #Mcount(Mstatus varchar(50), Mtype varchar (50),Count_M int)
insert #MCount(Mstatus, Mtype, Count_M)
exec LOE_M_Count
create table #Lcount(Lstatus varchar(50), Ltype varchar (50),Count_L int)
insert #LCount(Lstatus, Ltype, Count_L)
exec LOE_L_Count
select * from #tempLOE inner join #HCount on #tempLOE.Status+ #tempLOE.type=
#HCount.Hstatus+ #HCount.Htype
inner join #MCount on #tempLOE.Status+ #tempLOE.type= #MCount.Mstatus+
#MCount.Mtype inner join
#LCount on #tempLOE.Status+ #tempLOE.type= #LCount.Lstatus+ #LCount.Ltype
Have a great day!
SK|||I think you're trying to be helpful. But you only negate everything rather
than understanding what was needed here and do not provide any solution or
real guidance.
The names are just labels as an example with which to work.
"--CELKO--" wrote:

> as in below, but it will insert the records [sic] sequentially and i
> want the
> stored procedures to populate the fields [sic] that are parrallel
> [unh?]. <<
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to have a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor or in an OVER() clause.
> In SQL, the INSERT, UPDATE and DELETE statements work on rows as a
> unit. This is not a file system, wher you can move a read/write head
> to a field and do your work. This is what happens when you use the
> wrong words.
> I will not even get into the vagueness of "id", "type" and "status" as
> attempts at data element names.
> Next, good SQL programmers avoid UDF and procedural code. That is 3GL
> programming and not declarative coding.
>

Calling a Stored Procedure on Multiple Records

I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made:
CREATE PROCEDURE dbo.GetUserStats @.UserID BIGINT AS
DECLARE @.TempTable TABLE
(
UserID BIGINT,
UserName VARCHAR(60),
TotalAmt FLOAT
)

INSERT INTO @.TempTable (UserID, UserName, TotalAmt)
SELECT u.RecID, u.LastName + ', ' + u.FirstName,
(SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @.UserID)
FROM Users u
WHERE u.RecID = @.UserID

SELECT * FROM @.TempTable
GO

So if I execute this amount entering a single ID, it returns a single row for that user:

UserID UserName TotalAmt
--------
1 Doe, John 100.00

What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users:

UserID UserName TotalAmt
--------
1 Doe, John 100.00
2 Smith, Bob 123.45
3 Blow, Joe 150.55

Is there a way to re-use a stored procedure within another, based on the results of a query?Some thoughts:

1. This sounds more like a function than a stored procedure. Would make the execution concept much easier (ie, SELECT dbo.MyFunction(MyUserID) FROM MyTable).

2. You could wrap this with another stored procedure; you could pass in the user list as a parameter (there was an excellent article recently over at SQLServerCentral.com about using an xml string as a parameter).

But I really think that what you want is a function.

Regards,

hmscott|||Thanks for your feedback.

I'm not familiar with functions. Would I want to convert GetUserStats to a function?|||may, maybe not. you do know you can do that whole thing without the temp table.

BTW, bigint for userid? do you know there are not that many people in the world?|||I'm not familiar with functions. Would I want to convert GetUserStats to a function?

I wouldn't "convert" per se; at least not the way that you have written it.
Try this:

Open Query Analyzer
Click on File | New
Double click on Create Function
Double click on Create Scalar Function

Read in SQL BOL about Scalar functions.

I think you will get an idea of what a function is and how to go about writing your own. From there I think you will find it easier to understand how to proceed.

Regards,

hmscott|||may, maybe not. you do know you can do that whole thing without the temp table.

BTW, bigint for userid? do you know there are not that many people in the world?
The example I posted is very simplified. The actual query I'm trying to pull off is pretty complicated and pulls data from several tables, that's why I included the temp table. Can I use a temp table in a function?

Yeah, I guess a BIGINT is more than I need for a user ID. I had the misconception of thinking INTs only go to 32,000 when I set up this database (as they do in many programming languages).|||I have tried building a few functions. I can get them to run using a direct statemtent such as:

SELECT * FROM MyFunction(1)

But If I try to use the line above in a stored procedure or another function, I get an error:

Invalid object name 'MyFunction'.|||They're fiesty. You need the user name as a prefix, as in:SELECT *
FROM dbo.MyFunction(1)-PatP

Callind Stored Proc from Stored Proc

I am trying to call a stored proc from within a stored proc as below, but it
throws a syntax error.
SELECT COUNT(*) FROM (EXEC spName)
I have no access to "spName" to make any amends, currnetly it returns a
large recordset of customers.
Anyone any ideas how to do this?Billy
INSERT INTO #tmp EXEC spName --Make sure that number of columns in the
#tmp should be match to the output from the SP
SELECT COUNT(*) FROM #tmp
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:26DC8B64-FF85-4BE0-8913-B84A021C2D40@.microsoft.com...
>I am trying to call a stored proc from within a stored proc as below, but
>it
> throws a syntax error.
> SELECT COUNT(*) FROM (EXEC spName)
> I have no access to "spName" to make any amends, currnetly it returns a
> large recordset of customers.
> Anyone any ideas how to do this?
>|||Try using "select @.@.rowcount" immediately after executing the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Callind Stored Proc from Stored Proc

I am trying to call a stored proc from within a stored proc as below, but it
throws a syntax error.
SELECT COUNT(*) FROM (EXEC spName)
I have no access to "spName" to make any amends, currnetly it returns a
large recordset of customers.
Anyone any ideas how to do this?
Billy
INSERT INTO #tmp EXEC spName --Make sure that number of columns in the
#tmp should be match to the output from the SP
SELECT COUNT(*) FROM #tmp
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:26DC8B64-FF85-4BE0-8913-B84A021C2D40@.microsoft.com...
>I am trying to call a stored proc from within a stored proc as below, but
>it
> throws a syntax error.
> SELECT COUNT(*) FROM (EXEC spName)
> I have no access to "spName" to make any amends, currnetly it returns a
> large recordset of customers.
> Anyone any ideas how to do this?
>
|||Try using "select @.@.rowcount" immediately after executing the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Calculation between two rows

Hi,
I have a table and data set to populate it. I need to calculate
difference between two rows (lets say the number of items below) and
want to show the difference on another column on each row as below.
Does anyone know how to do that?
Table
Name #Items Date Difference
Bla Bla 60 05-06-2006 -40
Bla Bla 100 05-06-2006 20
Bla Bla 80 04-06-2006 0
Regards,
Alp Eren YILMAZAlp Eren schreef:
> Hi,
> I have a table and data set to populate it. I need to calculate
> difference between two rows (lets say the number of items below) and
> want to show the difference on another column on each row as below.
> Does anyone know how to do that?
> Table
> Name #Items Date Difference
> Bla Bla 60 05-06-2006 -40
> Bla Bla 100 05-06-2006 20
> Bla Bla 80 04-06-2006 0
> Regards,
>
> Alp Eren YILMAZ
Alp,
Something like currentmember-nextmember.......
Ronald|||On the Difference column's textbox right click and click on Expressions and
type the folliwing
=Fields!Item.Value - Previous(Fields!Item.Value)
This will give you the desired result.
Sum.
"Alp Eren" wrote:
> Hi,
> I have a table and data set to populate it. I need to calculate
> difference between two rows (lets say the number of items below) and
> want to show the difference on another column on each row as below.
> Does anyone know how to do that?
> Table
> Name #Items Date Difference
> Bla Bla 60 05-06-2006 -40
> Bla Bla 100 05-06-2006 20
> Bla Bla 80 04-06-2006 0
> Regards,
>
> Alp Eren YILMAZ
>|||Hi,
Thanks for the answers, a couple of tips
1- Change the sort order for the table data so you can use Previous
2- To reach the previous row data use Previous(..) function
= Previous( Fields!ColXXX.Value)-Fields!ColXXX.Value
MSDN Reference: http://msdn2.microsoft.com/en-us/ms156372(SQL.90).aspx
3- For the first row, avoid computation with iif and RowNumber(..)
check
Regards,
Alp Eren YILMAZ

Calculating Luhn Digit in Crystal Reports

I have an 18 digit number and i need a SQL formula in Crystal Reports to calculate the Luhn digit.

Below is how Luhn is calculated.

Step 1: Double the value of alternate digits beginning with the first right hand digit.

e.g 8 6 3 7 5 = 8 12 3 14 5

Step 2: Add the individual digits compromising the products obtained in Step 1 to each of the unaffected digits in the original number.

e.g 8 12 3 14 5 = 8+1+2+3+1+4+5 (total=24)

Step 3: Subtract the total obtained in Step 2 from the next higher number ending in 0.

e.g 24 = 30-24 = 6 (Luhn = 6!)

Any help here would be fantastic!!This should do it...

Local StringVar sValue := {test.NUMBER};

Local NumberVar nSubtotal;
Local NumberVar i;

For i := (Length(sValue) - 1) To 1 Step -2 Do
(nSubtotal := nSubtotal + CDbl(sValue[i]));

For i := Length(sValue) To 1 Step -2 Do
nSubtotal := nSubTotal + (If CDbl(sValue[i]) < 5
Then CDbl(sValue[i]) * 2
Else
CDbl(sValue[i]) - (9 - CDbl(sValue[i])));

nSubtotal Mod 10;

Calculating Hours, Mins over 24 hour periods

Below I'm returning the decimal duration [decDuration], a HH:MM:SS format
without "padding" 0's [realDuration], and a HH:MM:SS format with "padding"
0's [realDuration2].
If you run my EXAMPLE, in RESULTS below you'll see that [decDuration] and
[realDuration] work fine, but [realDuration2] looses 24 hours if a duration
spans over a day.
Can someone help me modify my formula for [realDuration2] so it doesn't
loose 24 hours if a duration is longer than a day?
EXAMPLE **********
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 09:34:11'
set @.dtEndDate = '20060315 14:42:53'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select CONVERT(decimal(10, 6), @.duration / 3600.0) AS decDuration,
RTRIM(@.duration/3600) + ':' + RTRIM(@.duration % 3600/60) + ':' +
RTRIM(@.duration % 60) AS realDuration,
CONVERT(varchar,CONVERT(datetime,DATEADD
(s,@.duration,'19000101' )),108) AS
realDuration2
RESULTS **********
[decDuration] [realDuration] [realDuration2]
----
29.145000 29:8:42 05:08:42declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 09:34:11'
set @.dtEndDate = '20060315 14:42:53'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select
CONVERT(decimal(10, 6), @.duration / 3600.0)
AS decDuration,
RTRIM(@.duration/3600) + ':'
+ RTRIM(@.duration % 3600/60) + ':'
+ RTRIM(@.duration % 60)
AS realDuration,
RIGHT('00'+RTRIM(@.duration/3600),2) + ':'
+ RIGHT('00'+RTRIM(@.duration % 3600/60),2) + ':'
+ RIGHT('00'+RTRIM(@.duration % 60),2)
AS realDuration2
"scott" <sbailey@.mileslumber.com> wrote in message
news:u0pS4rHbGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Below I'm returning the decimal duration [decDuration], a HH:MM:SS format
> without "padding" 0's [realDuration], and a HH:MM:SS format with "padding"
> 0's [realDuration2].
> If you run my EXAMPLE, in RESULTS below you'll see that [decDuration] and
> [realDuration] work fine, but [realDuration2] looses 24 hours if a
> duration spans over a day.
> Can someone help me modify my formula for [realDuration2] so it doesn't
> loose 24 hours if a duration is longer than a day?
> EXAMPLE **********
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 09:34:11'
> set @.dtEndDate = '20060315 14:42:53'
> set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
> select CONVERT(decimal(10, 6), @.duration / 3600.0) AS decDuration,
> RTRIM(@.duration/3600) + ':' + RTRIM(@.duration % 3600/60) + ':' +
> RTRIM(@.duration % 60) AS realDuration,
> CONVERT(varchar,CONVERT(datetime,DATEADD
(s,@.duration,'19000101' )),108) AS
> realDuration2
>
> RESULTS **********
> [decDuration] [realDuration] [realDuration2]
> ----
> 29.145000 29:8:42 05:08:42
>|||that did it, thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23p9PjrIbGHA.4676@.TK2MSFTNGP04.phx.gbl...
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 09:34:11'
> set @.dtEndDate = '20060315 14:42:53'
> set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
> select
> CONVERT(decimal(10, 6), @.duration / 3600.0)
> AS decDuration,
> RTRIM(@.duration/3600) + ':'
> + RTRIM(@.duration % 3600/60) + ':'
> + RTRIM(@.duration % 60)
> AS realDuration,
> RIGHT('00'+RTRIM(@.duration/3600),2) + ':'
> + RIGHT('00'+RTRIM(@.duration % 3600/60),2) + ':'
> + RIGHT('00'+RTRIM(@.duration % 60),2)
> AS realDuration2
>
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:u0pS4rHbGHA.3328@.TK2MSFTNGP02.phx.gbl...
>|||Why not keep track of total minutes (or seconds, if appropriate) and
let the front end worry about the display format? Tiered architecture
and all that jazz?

Calculating Age in Yrs & Months

Hello all,
I need to add Year & Months to a persons age from a
datetime (DoB) field. The current script I have (as below)
can calculate only Years but NOT months. Does anyone have
any idea or a ready script that I can use which will give
me months & years for that person using the Dob field?
CASE
WHEN DATEADD(year, DATEDIFF (year, p1.DOB, getdate()),
p1.DOB) > getdate()
THEN DATEDIFF (year, p1.DOB, getdate()) - 1
ELSE DATEDIFF (year, p1.DOB, getdate())
END as 'Age'
Thanks much in advance,
JoshiSee if this helps:
[url]http://www.alegsa.com.ar/Visitas/i45/Calculating%20yrs%20&%20mths%20for%20age.php[
/url]
AMB
"J. Joshi" wrote:

> Hello all,
> I need to add Year & Months to a persons age from a
> datetime (DoB) field. The current script I have (as below)
> can calculate only Years but NOT months. Does anyone have
> any idea or a ready script that I can use which will give
> me months & years for that person using the Dob field?
> CASE
> WHEN DATEADD(year, DATEDIFF (year, p1.DOB, getdate()),
> p1.DOB) > getdate()
> THEN DATEDIFF (year, p1.DOB, getdate()) - 1
> ELSE DATEDIFF (year, p1.DOB, getdate())
> END as 'Age'
> Thanks much in advance,
> Joshi
>|||Substracting the integer representation of the date of birth's month value
from the integer representation of current month value will give you the
number of months you are looking for.
Anith