DSQL in stored procedures?

[terosoft]

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:

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

 

[MartijnTonies]

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

 

[terosoft]

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.

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

 

[MartijnTonies]

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