Board index » delphi » Stored Procedure - Sql Server 7.0

Stored Procedure - Sql Server 7.0

Hi all,

I'm writting a sp and it needs to receive, as a
parameter, the where clause. It's easy,
but when this parameter need to be a part of a
declared cursor, it isn't easy any more.

I'll give an example:

create procedure mysp @whereclause vachar(100)
as begin
Declare mycursor cursor for
Select * from table1
where @whereclause
open mycursor
...
end

Well, I want to pass wich filter the cursor will
result, who knows how?

Thanks,

--
Willian Turner Atherton
Analista de Sistemas
Equipe de Desenvolvimento
AIX Sistemas

" A arte da natureza do Homem,
  a natureza a arte de Deus"

  willian.vcf
< 1K Download
 

Re:Stored Procedure - Sql Server 7.0


CREATE procedure sp_MyProc

@whereclause varchar (100)

AS

exec
(
'DECLARE MYCURSOR CURSOR FORWARD_ONLY STATIC FOR
SELECT
Field1,
Field2,
Field3
FROM MyTable where '
+ @whereclause
)

...

Then you can use the cursor like any other ("Fetch Next", "while
Fetch_Status <> -1", etc.). Don't forget to close and deallocate your cursor
at the end of the proc.

--

Alain Quesnel

Product Manager
Argos Software
alainnos...@argosoftware.com

===========
"Willian Turner Atherton" <will...@aix.com.br> wrote in message
news:3912DFF0.D37AE897@aix.com.br...

Quote
> Hi all,

> I'm writting a sp and it needs to receive, as a
> parameter, the where clause. It's easy,
> but when this parameter need to be a part of a
> declared cursor, it isn't easy any more.

> I'll give an example:

> create procedure mysp @whereclause vachar(100)
> as begin
> Declare mycursor cursor for
> Select * from table1
> where @whereclause
> open mycursor
> ...
> end

> Well, I want to pass wich filter the cursor will
> result, who knows how?

> Thanks,

> --
> Willian Turner Atherton
> Analista de Sistemas
> Equipe de Desenvolvimento
> AIX Sistemas

> " A arte da natureza do Homem,
>   a natureza a arte de Deus"

Re:Stored Procedure - Sql Server 7.0


Keep in mind that the BDE seems to {*word*88} on an input parameter of type
string that has more than 255 characters or so. If you need a parameter
larger than that, use a text type parameter in MSSQL Server, and an ftMemo
data type for your parameter in Delphi. When you pass the parameter value,
use :
StoredProc1.ParamByName('@whereclause').AsMemo := MyStringVar;

--

Alain Quesnel

Product Manager
Argos Software
alainnos...@argosoftware.com

===========
"Willian Turner Atherton" <will...@aix.com.br> wrote in message
news:3912DFF0.D37AE897@aix.com.br...

Quote
> Hi all,

> I'm writting a sp and it needs to receive, as a
> parameter, the where clause. It's easy,
> but when this parameter need to be a part of a
> declared cursor, it isn't easy any more.

> I'll give an example:

> create procedure mysp @whereclause vachar(100)
> as begin
> Declare mycursor cursor for
> Select * from table1
> where @whereclause
> open mycursor
> ...
> end

> Well, I want to pass wich filter the cursor will
> result, who knows how?

> Thanks,

> --
> Willian Turner Atherton
> Analista de Sistemas
> Equipe de Desenvolvimento
> AIX Sistemas

> " A arte da natureza do Homem,
>   a natureza a arte de Deus"

Other Threads