Return value

25 Nov 2005 1:26 AM

BAT

hello check this sp
CREATE    PROCEDURE dbo.sp_Inserta_bf_PlanOpcionSeleccionBR
(
    @IdPlan int,
    @IdPlanOpcion int,
    @IdGrupoParentesco int,
    @IdEmpleado as int,
    @IdAsegurado as int
)
AS
DECLARE @SqlText varChar(2000),
    @Result int
SET @SqlText=ISNULL((SELECT RTRIM(SqlText)
        FROM bf_PlanRestriccion
        WHERE IdEstatus=1 AND IdPlan=@IdPlan),'')

IF @SqlText<>''
BEGIN
EXECUTE @SqlText

    return EXECUTE sp_sqlexec @SqlText

END
ELSE
BEGIN
    print '0'
    return 0
END

How can I save the result of sp_sqlexec @SqlText into @Result or return
value of execute is it posible?, Execution Query is correct and if I run
query this give me a scalar value.
@SqlText is dynamic Sql query.

Thanks in advance

25 Nov 2005 1:52 AM

--CELKO--

Why are you using dynamic SQL in the first place?

25 Nov 2005 2:17 AM

BAT

Because the value of the query returns from a parameter

Show quote

"--CELKO--" wrote:

> Why are you using dynamic SQL in the first place?
>
>

25 Nov 2005 3:42 PM

Anith Sen

There are a couple of problems with your approach.

In general, it is a really bad idea to have the SQL statements as string
values in a table and use them in stored procedures. Not only they violate
certain fundamental programming principles, but the difficulty in
configuration management and code maintanance for such approaches makes it a
real nightmare. It might be a real worthwhile effort to consider changing it
to use regular data access approaches using simple SQL statements.

Coming to the specifics of the code, using sp_ prefix for stored procedure
names are not recommended since they are reserved for system procedures and
are evaluated differently. The system procedure sp_sqlexec is deprecated and
the currently suggested alternative is sp_ExecuteSQL. For details of
sp_ExecuteSQL, refer to SQL Server Books Online which has certain examples
which might help you get around in the short term.

--
Anith

25 Nov 2005 7:23 AM

Tony Rogerson

You need to use sp_executesql

declare @sql nvarchar(4000)

SET @sql = 'select @outparm = 5'

declare @myval int

exec sp_executesql @sql, N'@outparm int output', @myval output

print @myval

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"BAT" <B**@discussions.microsoft.com> wrote in message
news:17653B73-73BC-4693-9132-1BE4186B5CA2@microsoft.com...
> hello check this sp
> CREATE PROCEDURE dbo.sp_Inserta_bf_PlanOpcionSeleccionBR
> (
> @IdPlan int,
> @IdPlanOpcion int,
> @IdGrupoParentesco int,
> @IdEmpleado as int,
> @IdAsegurado as int
> )
> AS
> DECLARE @SqlText varChar(2000),
> @Result int
> SET @SqlText=ISNULL((SELECT RTRIM(SqlText)
> FROM bf_PlanRestriccion
> WHERE IdEstatus=1 AND IdPlan=@IdPlan),'')
>
>
> IF @SqlText<>''
> BEGIN
> EXECUTE @SqlText
>
> return EXECUTE sp_sqlexec @SqlText
>
> END
> ELSE
> BEGIN
> print '0'
> return 0
> END
>
>
> How can I save the result of sp_sqlexec @SqlText into @Result or return
> value of execute is it posible?, Execution Query is correct and if I run
> query this give me a scalar value.
> @SqlText is dynamic Sql query.
>
> Thanks in advance

25 Nov 2005 4:06 PM

BAT

Tony, thanks a lot it works fine.

Show quote

"Tony Rogerson" wrote:

> You need to use sp_executesql
>
> declare @sql nvarchar(4000)
>
> SET @sql = 'select @outparm = 5'
>
> declare @myval int
>
> exec sp_executesql @sql, N'@outparm int output', @myval output
>
> print @myval
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
>
> "BAT" <B**@discussions.microsoft.com> wrote in message
> news:17653B73-73BC-4693-9132-1BE4186B5CA2@microsoft.com...
> > hello check this sp
> > CREATE PROCEDURE dbo.sp_Inserta_bf_PlanOpcionSeleccionBR
> > (
> > @IdPlan int,
> > @IdPlanOpcion int,
> > @IdGrupoParentesco int,
> > @IdEmpleado as int,
> > @IdAsegurado as int
> > )
> > AS
> > DECLARE @SqlText varChar(2000),
> > @Result int
> > SET @SqlText=ISNULL((SELECT RTRIM(SqlText)
> > FROM bf_PlanRestriccion
> > WHERE IdEstatus=1 AND IdPlan=@IdPlan),'')
> >
> >
> > IF @SqlText<>''
> > BEGIN
> > EXECUTE @SqlText
> >
> > return EXECUTE sp_sqlexec @SqlText
> >
> > END
> > ELSE
> > BEGIN
> > print '0'
> > return 0
> > END
> >
> >
> > How can I save the result of sp_sqlexec @SqlText into @Result or return
> > value of execute is it posible?, Execution Query is correct and if I run
> > query this give me a scalar value.
> > @SqlText is dynamic Sql query.
> >
> > Thanks in advance
>
>
>