computed field syntax

Hello:
I am trying to set the value of a computed field in a stored procedure to
the value returned by another stored procedure but can't seem to find the
proper syntax:
CREATE PROCEDURE PlanListGet // syntax invalid
AS
SELECT
Plans.PlanID,
Plans.[Name],
Plans.[Description],
IsPlanEstablished = EXEC PlanIsEstablished PlanID
FROM Plans
In the above code, IsPlanEstablished is the computed field,
PlanIsEstablished is a stored procedure that returns an integer value, and
PlanID is a parameter for the PlanIsEstablished stored procedure.
Any suggestions?
Thanks!
ChrisYOu cant do that in a select but you can retrieve the Value to store it in
a temptable an retrieve this from that.
alte Procedure Testint
(
@.Valuetopass int
)
AS
SELECT @.Valuetopass*5
CREATE TABLE #TempTable
(
ValueToReturn INT
)
INSERT INTO #TempTable
EXEC Testint 5
SELECT * from #TempTable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisB" <pleasereplytogroup@.thanks.com> schrieb im Newsbeitrag
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||You can't use a stored procedure as an expression for a computed column. Per
haps you can convert the
proc into a user defined scalar function?
CREATE FUNCTION f() RETURNS INT AS BEGIN RETURN 1 END
GO
CREATE TABLE t(c1 int, c2 AS dbo.f())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
the value returned by
> another stored procedure but can't seem to find the proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field, PlanIsEstablis
hed is a stored
> procedure that returns an integer value, and PlanID is a parameter for the
PlanIsEstablished
> stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||Looks like I'll have to take a different approach.
Thanks for the input!
Chris
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>

Complex IIF statement not working

I am not understanding what my issue is on this one! I can look at the data
returned from the dataset in Data view, but when I attempt to show a table
based on at least one value being true (from several) it says:
"The hidden expression for the table 'table1' refers to the field
'SSFLunchRegular'. Report item expressions can only refer to fields within
the current data set scope..."
I can turn right around and show/hide a row of the table based on the exact
same field name and it works. The structure of my IIF statment appears to be
fine because the same structure works in another report. Is it the number of
conditions? Is there a limit?
Here are the conditions placed on the Hidden statement:
=IIF(
(FIELDS!SLPLunchRegular.Value <> 0) and
(FIELDS!SLPLunchProv1.Value <> 0) and
(FIELDS!SLPLunchProv2.Value <> 0) and
(FIELDS!SLPLunchProv3.Value <> 0) and
(FIELDS!SLPBkfstRegular.Value <> 0) and
(FIELDS!SLPBkfstProv1.Value <> 0) and
(FIELDS!SLPBkfstProv2.Value <> 0) and
(FIELDS!SLPBkfstProv3.Value <> 0) and
(FIELDS!SLPSevereRegular.Value <> 0) and
(FIELDS!SLPSevereProv1.Value <> 0) and
(FIELDS!SLPSevereProv2.Value <> 0) and
(FIELDS!SLPSevereProv3.Value <> 0) and
(FIELDS!SLPSnackArea.Value <> 0) and
(FIELDS!SLPSnackNonArea.Value <> 0), False, True
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1oops, I wrote 'SSFLunchRegular', it should have been 'SLPLunchRegular'. The
misspelling was in this thread, not my code so the problem is not solved..
Rick wrote:
>I am not understanding what my issue is on this one! I can look at the data
>returned from the dataset in Data view, but when I attempt to show a table
>based on at least one value being true (from several) it says:
>"The hidden expression for the table 'table1' refers to the field
>'SSFLunchRegular'. Report item expressions can only refer to fields within
>the current data set scope..."
>I can turn right around and show/hide a row of the table based on the exact
>same field name and it works. The structure of my IIF statment appears to be
>fine because the same structure works in another report. Is it the number of
>conditions? Is there a limit?
>Here are the conditions placed on the Hidden statement:
>=IIF(
>(FIELDS!SLPLunchRegular.Value <> 0) and
>(FIELDS!SLPLunchProv1.Value <> 0) and
>(FIELDS!SLPLunchProv2.Value <> 0) and
>(FIELDS!SLPLunchProv3.Value <> 0) and
>(FIELDS!SLPBkfstRegular.Value <> 0) and
>(FIELDS!SLPBkfstProv1.Value <> 0) and
>(FIELDS!SLPBkfstProv2.Value <> 0) and
>(FIELDS!SLPBkfstProv3.Value <> 0) and
>(FIELDS!SLPSevereRegular.Value <> 0) and
>(FIELDS!SLPSevereProv1.Value <> 0) and
>(FIELDS!SLPSevereProv2.Value <> 0) and
>(FIELDS!SLPSevereProv3.Value <> 0) and
>(FIELDS!SLPSnackArea.Value <> 0) and
>(FIELDS!SLPSnackNonArea.Value <> 0), False, True
>)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1|||The problem seems to be that you're attempting to hide a whole table based
on one single record. That wouldn't be logical. You'd need to aggregate the
values somehow, something like
=IIF(
(Sum(FIELDS!SLPLunchRegular.Value) <> 0) and
(Sum(FIELDS!SLPLunchProv1.Value) <> 0) and
...
...
Hope this helps.
--
Robert Jeppesen
Durius
http://www.durius.com/
"Rick via SQLMonster.com" <u15024@.uwe> wrote in message
news:5773f7edc9ca2@.uwe...
>I am not understanding what my issue is on this one! I can look at the
>data
> returned from the dataset in Data view, but when I attempt to show a table
> based on at least one value being true (from several) it says:
> "The hidden expression for the table 'table1' refers to the field
> 'SSFLunchRegular'. Report item expressions can only refer to fields
> within
> the current data set scope..."
> I can turn right around and show/hide a row of the table based on the
> exact
> same field name and it works. The structure of my IIF statment appears to
> be
> fine because the same structure works in another report. Is it the number
> of
> conditions? Is there a limit?
> Here are the conditions placed on the Hidden statement:
> =IIF(
> (FIELDS!SLPLunchRegular.Value <> 0) and
> (FIELDS!SLPLunchProv1.Value <> 0) and
> (FIELDS!SLPLunchProv2.Value <> 0) and
> (FIELDS!SLPLunchProv3.Value <> 0) and
> (FIELDS!SLPBkfstRegular.Value <> 0) and
> (FIELDS!SLPBkfstProv1.Value <> 0) and
> (FIELDS!SLPBkfstProv2.Value <> 0) and
> (FIELDS!SLPBkfstProv3.Value <> 0) and
> (FIELDS!SLPSevereRegular.Value <> 0) and
> (FIELDS!SLPSevereProv1.Value <> 0) and
> (FIELDS!SLPSevereProv2.Value <> 0) and
> (FIELDS!SLPSevereProv3.Value <> 0) and
> (FIELDS!SLPSnackArea.Value <> 0) and
> (FIELDS!SLPSnackNonArea.Value <> 0), False, True
> )
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1