Dynamic query: EXEC - Need to get a return value from query

I use dynamic SELECT statement trying to achive the followng:

DECLARE @.table varchar(50)

DECLARE @.ColumnA varchar(50)

DECLARE @.Result float

SET @.table = 'TABLE_A'

SET @.ColumnA = 'COLUMN_1'

SET @.SQL = ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

EXEC (@.SQL)

I need to assign the outcome of this statement to the @.Result variable. It does not work because of different contexts for @.Result. It would run if I do

SET @.SQL = 'DECLARE @.Result float '

SET @.SQL = @.SQL + ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

But in this case @.Result is not 'visible' outside EXEC statement.

One way to solve this is to use INSERT statement into temp table and then read result from the temp table.

Are there any more elegant solutions?

Thanks!

Sorry, but the code in EXEC() is considered to be its own batch, so you will need to persist it in a table, and temp tables are usually used for this. BTW, what did you need to do with the @.Result value? You may be able to put that in the same EXEC() code.

Thanks, Dean

|||

There are different ways to execute dynamic sql. The one that you need is sp_execsql. It allows you to declare parameters -- even output parameters. A key to remember is that an output parameter must be declared as output twice, in much the same way that an output parameter is declared both inside a stored procedure and on the exec line.

Your code should look like:

Declare @.result float

SET @.SQL = @.SQL + ' SELECT @.Result = (SUM((' + @.ColumnA + ' )) FROM ' + @.table

exec sp_execsql @.sql, N' @.Result float Output', @.Result output

The command wants NVarchar() parameters. Make sure @.sql is declared NVarchar() and include the N on the literal declaring the parameter. You can have multiple parameters if needed.

Dynamic Filtering, a further question

Am I correct to assume that the Host_Name function, when used in a filter
expression at the publisher will return the subsriber host name?
What does SUSER_SNAME return:
The name of the current logged on user at the subscriber?
The name of the SQL server run account ath the subsciber?
Or the SQLServerAgent run account at the subscriber?
Tony Toker
Data Identic Ltd.
That depends. If it is a pull subscription, which means the agent is running
on the subscriber it will return the name of the subscriber. If it is a push
subscription, which means the agent is running on the publisher it will
return the name of the publisher.
Unless of course we are talking merge replication and you override this
behavior by using the HostName parameter in the merge agent commands
section. (right click on your merge agent, select agent propertes, Steps,
run agent, click edit, and in the command section look or add a -HostName
and then enter the name you wish to be replaced by the HostName parameter
here.
SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:celd7r$n8l$1$830fa7b3@.news.demon.co.uk...
> Am I correct to assume that the Host_Name function, when used in a filter
> expression at the publisher will return the subsriber host name?
> What does SUSER_SNAME return:
> The name of the current logged on user at the subscriber?
> The name of the SQL server run account ath the subsciber?
> Or the SQLServerAgent run account at the subscriber?
> Tony Toker
> Data Identic Ltd.
>
|||<mini-quibble>
"SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber."
The Merge Agent typically runs under SQL Server Agent at the Distributor for
push subscriptions (not the publisher) or at the Subscriber for pull
subscriptions.
</mini-quibble>
Regards,
Paul Ibison
|||That is correct. Thanks for the correction.
Keep in mind that for most topologies - the publisher will be on the same
server as the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uM%23AusJeEHA.1000@.TK2MSFTNGP12.phx.gbl...
> <mini-quibble>
> "SUser_SName will resolve to account that the SQL Server agent runs under.
> For a push, its the SQL Server agent account on the publisher, for a pull
> the SQL Server agent account on the Subscriber."
> The Merge Agent typically runs under SQL Server Agent at the Distributor
for
> push subscriptions (not the publisher) or at the Subscriber for pull
> subscriptions.
> </mini-quibble>
> Regards,
> Paul Ibison
>

Dynamic field list

Hi,
The underlying query in the dataset of my report has a set number of static
fields (which I bind to report elements) but also can return additional
variable number of fields, depending on passed parameters. Is there a way to
access those fields at report runtime?
Thanks.How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10=A0pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset =A0of my report has a set number of st=atic
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way =to
> access those fields at report runtime?
> Thanks.|||That's the thing - I don't know in advance *how many* dynamic columns I am
going to return. Let's say I pass no parameters - the query will return
columns:
A B C
If I pass parameter "1" the query will return columns
A B C D
If I pass parameter "2" the the query will return columns
A B C D E
field list in dataset in report definition can contain only static number of
fields and if I bind report to A B C then D and E become unaccessable even
if query returns them.
The only way I can think of is, since I am launching the report from a .NET
application anyway is download report definition and modify it on the fly by
adding new columns to dataset field list. But I'd like to avoid it if
possible.
<prabhupr@.gmail.com> wrote:
How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10 pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset of my report has a set number of
> static
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way
> to
> access those fields at report runtime?
> Thanks.|||Not tested , Just an idea - Does the use of
=IIF(Fields!Column_1.IsMissing, true, false)in the hidden property of the
coloumn solve your problem ?
P.I.
"Yuriy Galanter" <yuri@.galanter.net> a écrit dans le message de news:
eXm6bCJrIHA.4848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> The underlying query in the dataset of my report has a set number of
> static fields (which I bind to report elements) but also can return
> additional variable number of fields, depending on passed parameters. Is
> there a way to access those fields at report runtime?
> Thanks.
>

dynamic columns in matrix

hi all,

i m using ssrs 2005. i want to generate a report which displays data according to the dataset returned.Now this dataset can return any number of columns.

does matrix use helps out in this case..

if anyone can really explain me on it or can even point to certain articles in this regard,that wudd be wonderful

thanks a ton...

hi all
m quite new to reporting services so may b it sounds easy for u champs but still ur replies wudd b appreciated..

thanks a ton ...

|||Hi,hilander:
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link yourdataset to a report at runtime by using ReportViewer.LocalReport methods or at disign level too.

Hopefully I help you.

|||

Hi,hilander:
We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Dynamic Columns in a Reports - HELP NEEDED

All MSRS Mentors,
I need to create a report with dynamic columns, my stored procedure will
return columns based on the parameters I pass.
I have three static columns and n dynamic columns.
My Reports should look like this
ColumnA ColumnB ColumnC DynamicColumnD DynamicColumnE ......
YY RR TT YY YY
TT OO TT YY YY
I am new to RS, please send me the steps to do this, so that I can do it.
Thanks
Balaji
--
Message posted via http://www.sqlmonster.comHi balaji,
im also working on the same problem.....
Do u have any idea in solving this....
"BALAJI KRISHNAN via SQLMonster.com" wrote:
> All MSRS Mentors,
> I need to create a report with dynamic columns, my stored procedure will
> return columns based on the parameters I pass.
> I have three static columns and n dynamic columns.
> My Reports should look like this
> ColumnA ColumnB ColumnC DynamicColumnD DynamicColumnE ......
> YY RR TT YY YY
> TT OO TT YY YY
> I am new to RS, please send me the steps to do this, so that I can do it.
> Thanks
> Balaji
> --
> Message posted via http://www.sqlmonster.com
>|||Unfortunately, I don't think this is possible in this version -- you have to
have all your columns defined up front. Perhaps you can flatten your data
and use a matrix control instead of a table control.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee0a6b9e279446d99fa4d3c96e5b443d@.SQLMonster.com...
> All MSRS Mentors,
> I need to create a report with dynamic columns, my stored procedure will
> return columns based on the parameters I pass.
> I have three static columns and n dynamic columns.
> My Reports should look like this
> ColumnA ColumnB ColumnC DynamicColumnD DynamicColumnE ......
> YY RR TT YY YY
> TT OO TT YY YY
> I am new to RS, please send me the steps to do this, so that I can do it.
> Thanks
> Balaji
> --
> Message posted via http://www.sqlmonster.com|||Jeff,
What do you mean my flaten data, could you explain me in detail, so that I
can try that.
Thanks
Balaji
--
Message posted via http://www.sqlmonster.com|||You put the data that goes into different columns into different rows of
your data source, and have a field whose value in each row becomes the
column name (when turned into the matrix column group).
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca7d41f91f334d45bf1335f57c878af6@.SQLMonster.com...
> Jeff,
> What do you mean my flaten data, could you explain me in detail, so that I
> can try that.
> Thanks
> Balaji
> --
> Message posted via http://www.sqlmonster.com|||Balaji, You need to use a matrix. Which generates dynamic columns.
However, it only allows one'fixed' column per grouping.
You can get around this by using the Rectangle control.
Create a rectangle OUTSIDE of the data region. Create some textbox
fields inside the rectangle, assign the relevant value to each text box.
Then cut the rectangle and paste it inside the row cell in the matrix.
This would be the structure of your matrix. Its a bit of a fudge but
works (it can be fiddly to get the alignments right).
+--+--+
| | |
+--+--+
|+--+| |
||+--+--+|| |
||| ColA | ColB ||| Dynamic |
||+--+--+|| data |
|+--+| |
+--+--+
If you're unsure about matrix controls, run through the AdventureWorks
samples first before trying this.
Chris
BALAJI KRISHNAN via SQLMonster.com wrote:
> All MSRS Mentors,
> I need to create a report with dynamic columns, my stored procedure
> will return columns based on the parameters I pass.
> I have three static columns and n dynamic columns.
> My Reports should look like this
> ColumnA ColumnB ColumnC DynamicColumnD DynamicColumnE ......
> YY RR TT YY YY
> TT OO TT YY YY
> I am new to RS, please send me the steps to do this, so that I can do
> it.
> Thanks
> Balaji

dynamic aliases/columns

Hi
Is it possible to return a table that its aliases are changing according to
varaiables ?
For example: I would like to use something like (ofcourse it doesn't work).
declare @.p1,@.p2 varchar(30)
set @.p1 ='blhablha'
set @.p2 ='hghfg'
select field1 as @.p1, field2 as @.p2 from table1Only with dynamic SQL (sp_executesql or exec). Most of the time,
however, dynamic SQL is not really a terrific idea. Here is Erland
Sommarskog's page on dynamic SQL (a frequently referenced page):
http://www.sommarskog.se/dynamic_sql.html
I can't imagine why you'd actually want to do this. What are you trying
to achieve?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
romy wrote:

>Hi
>Is it possible to return a table that its aliases are changing according to
>varaiables ?
>For example: I would like to use something like (ofcourse it doesn't work)
.
>
>declare @.p1,@.p2 varchar(30)
>set @.p1 ='blhablha'
>set @.p2 ='hghfg'
>select field1 as @.p1, field2 as @.p2 from table1
>
>|||rommy
if changing alias is only problem
you can use the trick of if condition in sp of CASE in query.Post your
script to suggest you better.
Regards
R.D
"romy" wrote:

> Hi
> Is it possible to return a table that its aliases are changing according t
o
> varaiables ?
> For example: I would like to use something like (ofcourse it doesn't work
).
>
> declare @.p1,@.p2 varchar(30)
> set @.p1 ='blhablha'
> set @.p2 ='hghfg'
> select field1 as @.p1, field2 as @.p2 from table1
>
>|||While I can't think of why you would need to do this on the server,
you could use put the results into a table, rename the columns
with sp_rename (which accepts parameters), then select the contents
of the table.
declare
@.cname1 sysname,
@.cname2 sysname
set @.cname1 = N'lName'
set @.cname2 = N'ID'
select LastName, EmployeeID
into #tmp
from Northwind..Employees
exec tempdb..sp_rename N'#tmp.LastName', @.cname1, 'COLUMN'
exec tempdb..sp_rename N'#tmp.EmployeeID', @.cname2, 'COLUMN'
select * from #tmp
drop table #tmp
Steve Kass
Drew University
romy wrote:

>Hi
>Is it possible to return a table that its aliases are changing according to
>varaiables ?
>For example: I would like to use something like (ofcourse it doesn't work)
.
>
>declare @.p1,@.p2 varchar(30)
>set @.p1 ='blhablha'
>set @.p2 ='hghfg'
>select field1 as @.p1, field2 as @.p2 from table1
>
>