×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

DSQL in stored procedures?

DSQL in stored procedures?

DSQL in stored procedures?

(OP)
Hi!

First of all, I'm almost new in IB/FB world so I'm sorry if my question seems dumb.

I have this Stored Procedure in MSSQL:

CODE

CREATE   procedure Eventos_Consultar_Resumen (
    @idTipoEvIni smallint,
    @idTipoEvFin smallint,
    @idUsIni smallint,
    @idUsFin smallint,
    @idOpIni smallint,
    @idOpFin smallint,
    @feIni datetime,
    @feFin datetime,
    @UsuarioPrimero tinyint
) AS

DECLARE @sqlstring nvarchar(1000), @sqlparam nvarchar(1000), @iLargo as int
set @sqlparam = ''
SET @sqlstring = N'SELECT vst.idTipoEvento,et.Descripcion AS Tipoevento,vst.idUsuario,us1.Nombre AS NombreUsuario,vst.idOperador,us2.Nombre AS NombreOperador,vst.Cantidad FROM (SELECT idTipoEvento,'
IF @idUsIni IS NULL OR @idUsFin IS NULL
    SET @sqlstring = @sqlstring + N'SUM(0)'
ELSE
    SET @sqlstring = @sqlstring + N'idUsuario'

SET @sqlstring = @sqlstring + N' AS idUsuario,'

IF @idOpIni IS NULL OR @idOpFin IS NULL
    SET @sqlstring = @sqlstring + N'SUM(0)'
ELSE
    SET @sqlstring = @sqlstring + N'idOperador'

SET @sqlstring = @sqlstring + N' AS idOperador,'

SET @sqlstring = @sqlstring + N'Count(idClienteEmpresa) As Cantidad FROM dbo.Vista_Seguimiento_todos '

SET @iLargo = LEN(@sqlstring)
IF @feIni IS NOT NULL
    SET @sqlstring = @sqlstring + N'WHERE convert(char(8),fecha,112)>=convert(char(8),@fIni,112) '
IF @feFin IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'convert(char(8),fecha,112)<=convert(char(8),@fFin,112) '
END
IF @idTipoEvIni IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'idTipoEvento>=@idTEI '
END
IF @idTipoEvFin IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'idTipoEvento<=@idTEF '
END
IF @idUsIni IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'idUsuario>=@idUI '
END
IF @idUsFin IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'idUsuario<=@idUF '
END
IF @idOpIni IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'idOperador>=@idOI '
END
IF @idOpFin IS NOT NULL
BEGIN
    IF LEN(@sqlstring) != @iLargo
        SET @sqlstring = @sqlstring + N'AND '
    ELSE
        SET @sqlstring = @sqlstring + N'WHERE '
    SET @sqlstring = @sqlstring + N'idOperador<=@idOF '
END

SET @SQLString = @SQLString + N'GROUP BY '

IF (@idOpIni IS NOT NULL OR @idOpFin IS NOT NULL) AND (@idUsIni IS NOT NULL OR @idUsFin IS NOT NULL)
BEGIN
    IF @UsuarioPrimero = 1
        SET @sqlstring = @sqlstring + N'idUsuario,idOperador,'
    ELSE
        SET @sqlstring = @sqlstring + N'idOperador,idUsuario,'
END
ELSE
BEGIN
    IF @idOpIni IS NOT NULL OR @idOpFin IS NOT NULL
        SET @sqlstring = @sqlstring + N'idOperador,'

    IF @idUsIni IS NOT NULL OR @idUsFin IS NOT NULL
        SET @sqlstring = @sqlstring + N'idUsuario,'
END

SET @SQLString = @SQLString + N'idTipoEvento) vst '

SET @SQLString = @SQLString + N'INNER JOIN dbo.Eventos_Tipos et ON et.idTipoEvento=vst.idTipoEvento '
SET @sqlstring = @sqlstring + N'INNER JOIN dbo.Usuarios us1 On us1.idUsuario=vst.idUsuario '
SET @sqlstring = @sqlstring + N'INNER JOIN dbo.Usuarios us2 On us2.idUsuario=vst.idOperador '

SET @sqlparam = N'@fIni datetime, @fFin datetime,@idTEI smallint,@idTEF smallint,@idUI smallint,@idUF smallint,@idOI smallint, @idOF smallint'

exec sp_executesql @sqlstring,@sqlparam,@fIni=@feIni,@fFin=@feFin,@idTEI=@idTipoEvIni,@idTEF=@idTipoEvFin,@idUI=@idUsIni,@idUF=@idUsFin,@idOI=@idOpIni,@idOF=@idOpFin

So, from my client app I call this procedure with needed parameters and get a cursor.
What I want to know if something like this is possible in Firebird or I have to assemble the statement at the client app and then issue it.
Also, is there a way to create something like "stored DSQL" for complex procedures or the only way is through a second app?


Thanks in advance...

Gerardo Czajkowski
0

RE: DSQL in stored procedures?

First of all, this is an INTERBASE group.

Firebird questions can be asked in "firebird-support@yahoogroups.com".

Second, in Firebird 1.5, you can use EXECUTE STATEMENT to execute a DSQL thingy.

However, you need to declare output parameters in order to return a resultset.

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server
http://www.upscene.com

RE: DSQL in stored procedures?

(OP)

Quote (MartijnTonies):

Second, in Firebird 1.5, you can use EXECUTE STATEMENT to execute a DSQL thingy.

However, you need to declare output parameters in order to return a resultset.

Thanks for the tip.

Quote (MartijnTonies):

First of all, this is an INTERBASE group.

Firebird questions can be asked in "firebird-support@yahoogroups.com".

First, for what I know of the IB/FB community, almost any question on any of this products can be asked on a forum or newsgroup dedicated to IB or FB as there aren't great differences between them, even on sites such as IBPhoenix encourages this.
Second, personally I don't like firebird-support@yahoogroups.com because it's difficult to navigate and receives a lot of requests everyday.
Third, I like Tek-tips community and there's no FB forum here, plus this isn't the first FB question asked in this forum but I think this is the first to receive a response like yours. I'll ask Tek-Tips to create a FB forum so you'll not get bothered anymore.

Regards,

Gerardo Czajkowski
0

RE: DSQL in stored procedures?

Hi Gerardo,

IBPhoenix tells us that IB and Fb questions can be asked at firebird-support. However, this becomes less true every day.

As both products move along, Firebird becomes different from InterBase every day. You cannot look upon them as "the same product". Really, believe me, I handle both

As for the mailinglist (firebird-support), you can set your Yahoo account to "no email" and use the news-group interface at news.atkin.com if you like. An alternative web-based Firebird forum can be found at http://forums.devshed.com/f61/s (I will be happy to answer your Firebird questions there).

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server
http://www.upscene.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close