analize sp

Hi All,
I have an store procedure with a 100 and some lines, and I want to analize
the result from another sp.
How can I get the values to do a select statement?
create procedure myPro
As
select * from orders bla...
go
create procedure analizemyPro
As
declare @.temp varchar(100)
select @.temp = ordername from (here the result of myPro)
Go
Tks in advance
JFBOutput parameters,look up (stored procedures, output parameters) in BOL
or create a table and do an insert with exec
insert into table
exec myPro
http://sqlservercode.blogspot.com/|||Tks for you reply and help... We got it :)
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1131643669.810105.317790@.g47g2000cwa.googlegroups.com...
> Output parameters,look up (stored procedures, output parameters) in BOL
> or create a table and do an insert with exec
> insert into table
> exec myPro
> http://sqlservercode.blogspot.com/
>

an In List Parameter

Is it possible (And if it is How do I do it) to create a report
parameter which allows a user to select a list of values and then
passes the entire list into the query?
IE. Parameter query returns a list of State abbreviations and the
user can then choose several which then get passed into the query as:
AND STATE IN ('GA', 'CA', 'NY')
TIA for any assistance anyone can provide me. I'm new to reporting
services and the help files haven't been all that much help.See related post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=aca0f4eb-9766-4437-a5f6-874f7344a827&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Curt" <cmgaviao@.yahoo.com> wrote in message
news:582e9f38.0408190714.544de11c@.posting.google.com...
> Is it possible (And if it is How do I do it) to create a report
> parameter which allows a user to select a list of values and then
> passes the entire list into the query?
> IE. Parameter query returns a list of State abbreviations and the
> user can then choose several which then get passed into the query as:
> AND STATE IN ('GA', 'CA', 'NY')
> TIA for any assistance anyone can provide me. I'm new to reporting
> services and the help files haven't been all that much help.

Always showing the parameter input area.

Greetings,

Is there a way to force the parameter input area to always show even when default values are specified? I want to always display the parameter area even when I set initial parameters.

Thanks

--|||

The strange thing is in one report I have a date parameter set to a default value of =Today() and in that report the parameter input area is always visible.

For a test I added a kludge parameter to the other report as a date and the input area is still in a collapsed state.

It must be the way the other parameters are processed.

|||

It's odd that your parameters area doesn't display just because the parameters are satisfied. Is this a drillthrough target by any chance? Drillthrough targets will have their parameters areas collapsed, since their parameterization is implied by the drillthrough parent.

The use of an expression for the default value ("=Today()) may short-circuit this, such that the viewer doesn't know ahead of time if the expression for the default value will evaluate. In that case, the viewer may just decide to show the parameters area in case the expression doesn't evaluate to a valid default value.

What build of Report Server are you using? On my test servers, the drillthrough target's parameters area is collapsed, even if I use an expression for the default value.

|||

"Prompt" properties of that parameter is blank?

|||

Thanks for the reply. I have not checked the forum in a while nor have I found a workaround.

This is not a drill through report and the prompt string has been specified.

I am working with 3 parameters and only one is a prompted parameter.

P1 Integer - Internal - No available values -default value of 0 - non-prompt

P2 Integer - Hidden - No available values - default value comes from query - non prompt

P3 - String - Prompt specified - Available values from query (forces drop down list) - Default value from query - prompt

I wish for the 3rd parameter, displayed in the parameter input area, to be visible when the report renders but it's not, the whole area is collapsed.

Could it be something to do of the order or timing of parameter processing?

|||

Made progress!

When I invoke the report via the url command the parameter area is always visible.

When I invoke the report via the navigation functionality of a report item the report is rendered but the parameter area collapsed.

Still dont know why....

Always showing the parameter input area.

Greetings,

Is there a way to force the parameter input area to always show even when default values are specified? I want to always display the parameter area even when I set initial parameters.

Thanks

--|||

The strange thing is in one report I have a date parameter set to a default value of =Today() and in that report the parameter input area is always visible.

For a test I added a kludge parameter to the other report as a date and the input area is still in a collapsed state.

It must be the way the other parameters are processed.

|||

It's odd that your parameters area doesn't display just because the parameters are satisfied. Is this a drillthrough target by any chance? Drillthrough targets will have their parameters areas collapsed, since their parameterization is implied by the drillthrough parent.

The use of an expression for the default value ("=Today()) may short-circuit this, such that the viewer doesn't know ahead of time if the expression for the default value will evaluate. In that case, the viewer may just decide to show the parameters area in case the expression doesn't evaluate to a valid default value.

What build of Report Server are you using? On my test servers, the drillthrough target's parameters area is collapsed, even if I use an expression for the default value.

|||

"Prompt" properties of that parameter is blank?

|||

Thanks for the reply. I have not checked the forum in a while nor have I found a workaround.

This is not a drill through report and the prompt string has been specified.

I am working with 3 parameters and only one is a prompted parameter.

P1 Integer - Internal - No available values -default value of 0 - non-prompt

P2 Integer - Hidden - No available values - default value comes from query - non prompt

P3 - String - Prompt specified - Available values from query (forces drop down list) - Default value from query - prompt

I wish for the 3rd parameter, displayed in the parameter input area, to be visible when the report renders but it's not, the whole area is collapsed.

Could it be something to do of the order or timing of parameter processing?

|||

Made progress!

When I invoke the report via the url command the parameter area is always visible.

When I invoke the report via the navigation functionality of a report item the report is rendered but the parameter area collapsed.

Still dont know why....

always display 10 major gridlines for y axis in graph

Hi,
I have graphs where I can never be sure of the range of values that
will be displayed. How would I set it to show 10 major gridlines on
the y axis' I tried setting the interval to 0.1 but this ovbiously
didn't work as the graph shows monetary values? And I can't set an
explicit interval as I won't know the max value to split in 10?
Can anyone tell me how to do this please' I'm using RS 2000
Thanks,
Gear=F3idcan anyone help with me this no? Desperate to get it done today...
Thanks...

Alternative to IIF ? Case ?

Hi,
Need to display a value in a text box based upon perhaps one of four
values a parameter could have. This example is great for two values, = iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
Is there an alternative , case... anything if we need to test for over
parameter values i.e. HHH1, or HHH2.
Many thanks, Steve.Try Switch:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Report parameters have a Value and a Label property. You could use the Label
property for this purpose.
Regarding alternatives for IIF please check the following MSDN links:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Thank you Robert !
"Robert Bruckner [MSFT]" wrote:
> Report parameters have a Value and a Label property. You could use the Label
> property for this purpose.
> Regarding alternatives for IIF please check the following MSDN links:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>|||Switch rocks !!! Thanks Jeff. Happy New year.
"Jeff A. Stucker" wrote:
> Try Switch:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>

Alternative to cursor

Hello,
I would like to find and alternative to using a cursor and updating my
table with the calculated values. The following code snippet is from my
store procedure.
-- Calculate and Update LEWeightedTerm and LEWeightedRR
DECLARE LECursor CURSOR FOR
SELECT TransactionID, Term, BusinessUnitID, LEAlloc, CalcODRBefore
FROM stage_DealTransaction
WHERE COBDateID = @.COBDateID AND LEWeightedTerm is null AND
LEWeightedRR is null
OPEN LECursor
DECLARE @.tranID AS int
DECLARE @.term AS int
DECLARE @.LEAlloc AS float
DECLARE @.BUID AS varchar(15)
DECLARE @.ODR AS Varchar(2)
DECLARE @.LEWT AS float
DECLARE @.LEWRR AS float
DECLARE @.LEAllocSum AS float
DECLARE @.rate AS float
FETCH NEXT FROM LECursor
INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
While (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.LEAllocSum = 1
IF (@.BUID IS NOT NULL)
BEGIN
SELECT @.LEAllocSum = SUM(ISNULL(LEAlloc, 0)) FROM DealTransaction
WHERE BusinessUnitID = @.BUID AND COBDateID = @.COBDateID
END
IF (@.LEAllocSum = 0)
BEGIN
SET @.LEAllocSum = 1
END
IF (@.LEAlloc IS NULL)
BEGIN
SET @.LEAlloc = 0
END
SET @.LEWT = (@.LEAlloc * ISNULL(@.term, 0)) / @.LEAllocSum
SET @.rate = CONVERT(float, ISNULL(@.ODR, '41'))
SET @.LEWRR = (@.LEAlloc * @.rate) / @.LEAllocSum
UPDATE stage_DealTransaction SET LEWeightedTerm = @.LEWT ,
LEWeightedRR = @.LEWRR WHERE TransactionID = @.tranID AND COBDateID =
@.COBDateID
END
FETCH NEXT FROM LECursor INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
END
CLOSE LECursor
DEALLOCATE LECursor
I would like to find a way to optimize it so that the updates can be
done in bulk rather than one record at a time.
Any feedback would be highly appreciated.
Thank you,
Zubinezubine@.gmail.com wrote:
> Hello,
> I would like to find and alternative to using a cursor and updating my
> table with the calculated values. The following code snippet is from
> my store procedure.
>
Could you summarize what you need this to do rather than making us analyze
the cursor code?
It always help to provide DDL and sample data as well. See
www.aspfaq.com/5006 for suggestions
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 18 Nov 2005 07:04:41 -0800, zubine@.gmail.com wrote:

>Hello,
>I would like to find and alternative to using a cursor and updating my
>table with the calculated values. The following code snippet is from my
>store procedure.
Hi Zubine,
Try the following:
UPDATE s
SET LEWeightedTerm = COALESCE (s.LEAlloc * s.Term), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1),
LEWeightedRR = COALESCE (s.LEAlloc *
CAST(COALESCE(s.CalcODRBefore, '41') AS float)), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1)
FROM stage_DealTransaction AS s
LEFT JOIN (SELECT BusinessUnitID,
SUM(LEAlloc) AS SumLEAlloc
FROM DealTransaction
WHERE dt.COBDateID = @.COBDateID
GROUP BY BusinessUnitID) AS dt
ON dt.BusinessUnitID = s.BusinessUnitID
WHERE s.COBDateID = @.COBDateID
AND s.LEWeightedTerm IS NULL
AND s.LEWeightedRR IS NULL
Depending on how your tables and data look, you might be able to replace
the LEFT JOIN with an INNER JOIN, and that might speed up the execution
as well.
Note that the stattement above is untested. See www.aspfaq.com/5006 if
you prefer a tested reply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)