BorlandTalk.com

Eduardo A. Salgado · Guest

Having problems in using the same code set for Firebird as for MS SQL
because we have used the "Select * from <stored procedure> ..." to fire
off some very complex stored procedures. Well,... MS SQL does not seem
to like that. It answers that the stored procedure called that way is
an "Unknown object".

We have tried to use ADOQueries and ADOStoredProcedure components in
Delphi 5 with no success. In Delphi 5 ADO for MS SQL, how do you get a
return value (say just an Integer) from a stored procedure?

This is an example of a simpler stored procedure that we need to get
working, preferable with the "Select * from <stored procedure> ..." to
keep from having to rewrite code.

CREATE PROCEDURE SAVECVSETTINGS (
@AUSERID Integer,
@AUSERABBR VarChar(5),
@ANINIINFO IMAGE,
@ASETTINGSID Integer OUTPUT)
AS

BEGIN
SET @ASettingsID = ( SELECT SettingID FROM CvSettings
WHERE USERID = @AUSERID)
IF (@ASettingsID IS NULL)
BEGIN
INSERT INTO CvSettings (
UserID,
UserAbbr,
IniInfo)
VALUES (
@AUserID,
@AUserAbbr,
@AnIniInfo);
SET @ASettingsID = @@identity
END ELSE
BEGIN
UPDATE CvSettings
SET IniInfo = @AnINIINFO
WHERE SettingID = @ASettingsID;
END
END

---
TIA

- Eduardo
Stop Continental Drift!
-- Anon

Eminent Domain Software
"Custom Software Development For Your Domain"

Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.

Kevin Frevert · Guest

Eduardo,

In MSSQL, you cannot 'select' from stored procedures. If the stored
procedure returns a result set, you still need to call the execute method.
There are a few ways to do this, one is to use a TADODataSet, set (all at
design time) the CommandType to cmdStoredProc, and the CommandText property
editor changes to a drop-down list of available stored procedures. Set the
parameters, call the Open method and you are good to go.

You didn't mention which version of MSSQL you are using, but if you are
using MSSQL 2000 or 2005 and want/need to use the Select statement, take a
look at using table based user-defined functions. MSSQL's Books Online does
an excellent job explaining the various types of user-defined functions.

Good luck,
krf

"Eduardo A. Salgado" <eas (AT) onedomain (DOT) com> wrote in message
news:4644ee5a$1 (AT) newsgroups (DOT) borland.com...

Del Murray · Guest

Eduardo,

Kevins advise is correct. What you are trying to do fits more with a "view"
in MS SQL Server. If your SP is not full of cursors and "logic" then you
could use a view and then "select * from myview where xxxxxx"

Del

Eduardo A. Salgado · Guest

Kevin,

Thanks for the quick and complete answer. When I tried the functions,
being new to them, I would get a complaint about "... time dependent
...." around the "Insert Into CVSettings" portion. Of course, I was
trying to do a return of an Integer and not a table based user-defined
function. Guess I better go back and hit the books on how to do functions.

- Eduardo
Stop Continental Drift!
-- Anon

Eminent Domain Software
"Custom Software Development For Your Domain"

Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.

Kevin Frevert wrote:

Eduardo A. Salgado · Guest

Del,

So, you think it is possible to turn my Stored Procedures into Views
that then allow me to continue to use the "Select * from ... " ?

In most cases, the "Select" is from one table only so it should be like
a View. I guess the If Then part of the code is what is causing me
problems with the Insert and Update. If I was just reading something it
may work, as some of the other Stored Procedures seem to work.

Thanks!

- Eduardo
Stop Continental Drift!
-- Anon

Eminent Domain Software
"Custom Software Development For Your Domain"

Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.

Del Murray wrote:

Del Murray · Guest

I would need to see the reqirements before we could tell if view, stored
procs or UDF's were appropriate. A view has no "where" logic in it. It can
do joins to other tables etc. The "where" and "sort" and group and stuff
like that go in the adoDataSet that you are using to do the select. That
makes your code simple .. "select * from MyView where amount > 500 order by
amount desc" is in the program. In the "view", the code may be

"select t1.*, t2.name, t2.address as addr1, t3.amount as amount
from Customers t1
join addresses t2 on t2.key1=t1.key1 and t2.key2=t1.key2 and t2.addr2 is not
null
left outer join invoices t3 on t3.key1=t1.key1 and t3.key3=t2.key1

You dont want to be writing that over and over in code so you store is once
in a view. A lot of people store a "general purpose" view that has
frequently used columns from frequently used tables and provides them data
for say .. 12 different reports.

You can join the result set from a view to another table if needed.

Chris.Cheney · Guest

"Del Murray" <Del.Murray (AT) CreditHawk (DOT) Net> wrote in
news:46462aed$1 (AT) newsgroups (DOT) borland.com:

Mikael Eriksson · Guest

Eduardo A. Salgado skrev:

Del Murray · Guest

Yes of course but what I meant was that you typically then want a subset of
what the view was so *that* "where" would not be in the view. I was trying
to keep it simple so that he didn't create 10 duplicate views with different
"where" clauses in them, when all he needed was one view and the select from
it with an "independent" where clause. Left to their own devices, some new
guys will do some very strange things that you would not do as a matter of
course.