Board index » delphi » SQL Syntax question

Jure Bogataj

Delphi Developer
Mon, 11 Aug 2003 03:22:47 GMT

SQL Syntax question

Hi!
I'm having a question regarding SQL syntax:
I've tried to execute following SQL statement, but it doesn't work:

// usp_level is my custom made stored procedure, and id is an identity field
in MyTable
select *, (usp_level id) as MyField
from MyTable

It doesn't work! Can't I execute some stored procedure for every record
select returns? If not, why, and is there any work arround. Stored procedure
is just so complicated, I can't make simple subquery to return this value?

Anybody, help!!

regards

 
Alain Quesne

Delphi Developer
Mon, 11 Aug 2003 07:28:39 GMT

Re:SQL Syntax question


What database are you using?

--

Alain Quesnel
cinqsanss...@compuserve.com

Quote
"Jure Bogataj" <jure.boga...@cis.si> wrote in message

news:9714gd$2sc14@bornews.inprise.com...
Quote
> Hi!
> I'm having a question regarding SQL syntax:
> I've tried to execute following SQL statement, but it doesn't work:

> // usp_level is my custom made stored procedure, and id is an identity
field
> in MyTable
> select *, (usp_level id) as MyField
> from MyTable

> It doesn't work! Can't I execute some stored procedure for every record
> select returns? If not, why, and is there any work arround. Stored
procedure
> is just so complicated, I can't make simple subquery to return this value?

> Anybody, help!!

> regards

kachy

Delphi Developer
Mon, 11 Aug 2003 08:48:03 GMT

Re:SQL Syntax question


Hi Jure,

Concerning your problem.
1. ID the not so successful name of a field. More correct would be USER_ID
or DOC_ID depending on the object of identification.
2. ID uses itself SQL Server, in the system tables for example, the problem
is possible because of it.
3. It is possible to try so: (usp_level MyTable.id) as MyField.

Quote
> Hi!
> I'm having a question regarding SQL syntax:
> I've tried to execute following SQL statement, but it doesn't work:

> // usp_level is my custom made stored procedure, and id is an identity
field
> in MyTable
> select *, (usp_level id) as MyField
> from MyTable

> It doesn't work! Can't I execute some stored procedure for every record
> select returns? If not, why, and is there any work arround. Stored
procedure
> is just so complicated, I can't make simple subquery to return this value?

> Anybody, help!!

> regards

kachy

Delphi Developer
Mon, 11 Aug 2003 08:52:03 GMT

Re:SQL Syntax question


Hi Jure,

Concerning your problem. (SQL Server 7)
1. ID the not so successful name of a field. More correct would be USER_ID
or DOC_ID depending on the object of identification.
2. ID uses itself SQL Server, in the system tables for example, the problem
is possible because of it.
3. It is possible to try so: (usp_level MyTable.id) as MyField.

Quote
> Hi!
> I'm having a question regarding SQL syntax:
> I've tried to execute following SQL statement, but it doesn't work:

> // usp_level is my custom made stored procedure, and id is an identity
field
> in MyTable
> select *, (usp_level id) as MyField
> from MyTable

> It doesn't work! Can't I execute some stored procedure for every record
> select returns? If not, why, and is there any work arround. Stored
procedure
> is just so complicated, I can't make simple subquery to return this value?

> Anybody, help!!

> regards

Jure Bogata

Delphi Developer
Mon, 11 Aug 2003 22:20:43 GMT

Re:SQL Syntax question


Quote
> What database are you using?

Sorry, I'm using SQL Server 7.0/2000 on Win2000 AS/WinNT 4.0

regards

Alain Quesne

Delphi Developer
Tue, 12 Aug 2003 02:00:46 GMT

Re:SQL Syntax question


With SQL Server 2000, you can use user-defined functions (UDFs) that way.
But if memory serves, you can't call a stored procedure within a UDF.

--

Alain Quesnel
cinqsanss...@compuserve.com

Quote
"Jure Bogataj" <jure.boga...@cis.si> wrote in message

news:9737ah$9h23@bornews.inprise.com...
Quote
> > What database are you using?

> Sorry, I'm using SQL Server 7.0/2000 on Win2000 AS/WinNT 4.0

> regards

Jure Bogata

Delphi Developer
Tue, 12 Aug 2003 23:12:45 GMT

Re:SQL Syntax question


Quote
> With SQL Server 2000, you can use user-defined functions (UDFs) that way.
> But if memory serves, you can't call a stored procedure within a UDF.

I've tried creating UDF's, but it doesn't work. QueryAnalyzer says, that my
UDF is an unknown function name.

Any ideas?

regards

Alain Quesne

Delphi Developer
Wed, 13 Aug 2003 07:40:13 GMT

Re:SQL Syntax question


Make sure you created your UDF in the same database that the one you are
calling it from. Otherwise, prefix the name of your function with the
database where the function resides.

--

Alain Quesnel
cinqsanss...@compuserve.com

Quote
"Jure Bogataj" <jure.boga...@cis.si> wrote in message

news:975unl$5g91@bornews.inprise.com...
Quote
> > With SQL Server 2000, you can use user-defined functions (UDFs) that
way.
> > But if memory serves, you can't call a stored procedure within a UDF.

> I've tried creating UDF's, but it doesn't work. QueryAnalyzer says, that
my
> UDF is an unknown function name.

> Any ideas?

> regards

Other Threads

1. Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

2. Simple SQL syntax question

3. SQL syntax question

4. SQL Syntax Question

5. SQL syntax questions

6. SQL syntax question

7. SQL Syntax question

8. BDE / SQL Syntax Question

9. Newbie question: SQL syntax

10. Oracle 8 Syntax Question (vs. MS SQL)