Dynamic reports

Yooo... I'm trying to build a dynamic report with Reporting Services. The problem is that I have a stored procedure that returns a different number of columns with different name for the columns almost each time. So... how can I get dynamic the number and the name of the columns at runtime.

He is an example of the SP:

CREATE PROCEDURE [dbo].[Test]
@.nrCol INT
, @.CarCol CHAR(5)
AS
CREATE TABLE #Part(DenPart CHAR(10))
DECLARE @.i INT
SET @.i = 0
WHILE @.i < @.nrCol
BEGIN
EXEC('ALTER TABLE #Part ADD [' + @.CarCol + @.i + '] NUMERIC(18,2) NOT NULL DEFAULT(0)')
SET @.i = @.i + 1
END
INSERT INTO #Part (DenPart) VALUES('A')
INSERT INTO #Part (DenPart) VALUES('B')
SELECT * FROM #Part

Any ideeas?

Thanks

I wont think we can use the above stored procedure. To develop a report we need to have a result set at design time. Report need to know what are the data fields at design time.sql

Dynamic Query

I need to write a stored proceed that has 15 parameters that returns a
recordset. Any one of these parameters may contain values.
EX: @.Lname = ''
@.Phone = '1234567890'
@.Fname = 'JANE'
@.City = 'LA'
@.State = ''
The main part of the proc is a dynamically created SELECT statement
where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
* FROM Table WHERE '. Only parameters with values must be included in
the WHERE clause. And any parameter after the first one should have
'AND'. So the query should look like this:
@.SQL = 'SELECT * FROM Table WHERE '
@.SQL = @.SQL + ' phone = ' + @.phone
@.SQL = @.SQL + ' AND Fname = ' + @.Fname
How can I figure out which is the first parameter that contains a
value so not to include an AND condition and then add the AND for the
rest of the parameters?
Thanks,
NinelYou cud write
set @.SQL = 'SELECT * FROM Table WHERE 1=1'
If isnull(@.phone ,'') <> ''
select @.sql = @.sql + '
AND Phone = @.phone'
If isnull(@.Fname ,'')<> ''
select @.sql = @.sql + '
AND FName = @.Fname'
exec (@.sql)
Untested, shud work
Prad
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>I need to write a stored proceed that has 15 parameters that returns a
> recordset. Any one of these parameters may contain values.
> EX: @.Lname = ''
> @.Phone = '1234567890'
> @.Fname = 'JANE'
> @.City = 'LA'
> @.State = ''
> The main part of the proc is a dynamically created SELECT statement
> where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
> * FROM Table WHERE '. Only parameters with values must be included in
> the WHERE clause. And any parameter after the first one should have
> 'AND'. So the query should look like this:
> @.SQL = 'SELECT * FROM Table WHERE '
> @.SQL = @.SQL + ' phone = ' + @.phone
> @.SQL = @.SQL + ' AND Fname = ' + @.Fname
> How can I figure out which is the first parameter that contains a
> value so not to include an AND condition and then add the AND for the
> rest of the parameters?
> Thanks,
> Ninel
>|||Hi
This will not work as @.phone or @.Fname will not be in scope. Check out
http://www.sommarskog.se/dyn-search.html for working examples.
John
"Pradeep Kutty" wrote:

> You cud write
> set @.SQL = 'SELECT * FROM Table WHERE 1=1'
> If isnull(@.phone ,'') <> ''
> select @.sql = @.sql + '
> AND Phone = @.phone'
> If isnull(@.Fname ,'')<> ''
> select @.sql = @.sql + '
> AND FName = @.Fname'
> exec (@.sql)
> Untested, shud work
> Prad
> "ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in messag
e
> news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>
>|||Avoid dynamic SQL and procedures with more than five parameters.
SELECT *
FROM Foobar
WHERE first_name = COALESCE(@.my_first_name, first_name)
AND last_name = COALESCE(@.my_last_name, last_name)
AND ... ;|||On 27 Apr 2005 07:46:33 -0700, --CELKO-- wrote:

> Avoid dynamic SQL and procedures with more than five parameters.
> SELECT *
> FROM Foobar
> WHERE first_name = COALESCE(@.my_first_name, first_name)
> AND last_name = COALESCE(@.my_last_name, last_name)
> AND ... ;
Is "five parameters" an arbitrary limit based on experience?
I can vouch for the fact that when there are too many parameters, the
optimizer has a really hard time figuring out a good plan. It will do crazy
things like a table scan to compare NULLs with every row, when it could
just get the desired answer from a primary key.
In one instance I "unrolled" the query into a set of the three most often
used queries, choosing the correct one to use based on IF statements.
(Programmer insisted on a single stored procedure for looking up customer
records, when the operator would sometimes only know the last name and
state, sometimes would have member ID, sometimes would have last name,
state and some other data ...)|||>> Is "five parameters" an arbitrary limit based on experience? <<
In 1956 by a psychologist named Miller published a short article
entitled "The Magical Number Seven Plus or Minus Two: Some Limits on
Our Capacity for Processing Information" that collected a lot of data
together in one place and this has been confirmed over and over again.
It is a classic paper and it ought to be out there.
The idea is that you can juggle five things fairly well, seven is when
it gets to hard and nine requires that you train for it and it is just
about impossible to get to ten things without being a savant. What you
have to do is "chunking" things to reduce the number of distinct
elements -- so (longtitude, latitude) becomes "location" rather than
two data elements.
the optimizer has a really hard time figuring out a good plan. <<
That is another "Law of Five". There are 3 ways to squence two tables
for processing, 6 ways to squence three tables, 24 ways to squence
four tables, and 120 ways to arrange five tables. Big jump at five!
And the optimizer starts to choke.
queries, choosing the correct one to use based on IF statements. <<
While I like to avoid IF-THEN control flow, it sounds like a good way
to do it in this case.sql

Dynamic formatting question

Hello,

I have a dataset that returns one column of data from which some fields are supposed to appear horizontally in the report, while the rest appear line by line vertically. Is it possible to take, for example, three consecutive fields from the dataset and merge the corresponding table rows that hold these fields in the report? Perhaps using an expression or custom code?

Thanks in advance for any help. I'd be happy to provide examples of what I need if it can help clarify the question.

RLGow

Not exactly sure what you are trying to do, but you can write expressions that concatenate the value of multiple fields, such as:

=Fields!ColumnA.Value & Fields!ColumnB.Value & Fields!ColumnC.Value

-- Robert

Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--

Dynamic Cursor/ Dynamic SQL Statement

I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James

-- SQL -----

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

--Error-------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.using the following seem to be achieving what i wanted.
but i would still like to know how to use
1. PREPARE
2. EXECUTE
i.e. under what circumstances would you use those 2?
It must be there for a reason.

James

--Working SQL-----

DECLARE @.sql nvarchar(4000)
SET @.sql = 'DECLARE @.name nvarchar(128) ' +
'DECLARE test_cursor CURSOR FOR SElECT name FROM class_category ' +
'OPEN test_cursor ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'BEGIN ' +
'PRINT @.name ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'END '
EXECUTE sp_executesql @.sql|||You seem to be confusing the VB environment with the Transact-SQL environment. The VB code executes only on the client side, and that is where a PREPARE might make sense. The Transact-SQL code executes only on the server side, and you can't explicitly prepare code there (at least under normal circumstances).

-PatP

Dynamic Column Names?

Using SQL 2000
I am passing in the name of the column I want to update, @.Column. When I
SELECT @.Column it returns the value of @.Column instead of the value of the
actule table column. I have tried to use
Col_Name(OBJECT_ID('antwerp_cutoff'),OBJ
ECT_ID(@.Column)) but this does not
get the column name.
When I pass in Display1 I need to get back the value 2/2/2005
|Display1 | Display2|
--
|2/2/2005 | 2/3/2005|
--
Any help would be greatly appreciated.
Thanks,
Scott R. Butler
Stored Proc:
Create Procedure "Update_Antwerp_Displays"
(
@.Column varchar(256)
)
As
Declare @.i int
Declare @.Count int
set @.Count = (SELECT Count (*) FROM antwerp_trans)
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
1 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 1
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
2 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 2
UPDATE antwerp_depart
Set @.Column = (select @.Column+(Select Delta from antwerp_depart where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
UPDATE antwerp_arrive
Set @.Column = (select @.Column+(Select Delta from antwerp_arrive where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
While (@.i < @.Count)
BEGIN
UPDATE antwerp_trans
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
@.i ) from antwerp_cutoff where Delta = 0)
WHERE ID = @.i
SET @.i = @.i +1
ENDYou cannot use variables for tables, columns, etc. in queries. You'll
need to use dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for the quick response... even though it was not the response I was
looking for.
Quess I will have to find another way to get it done.
Scott
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OYjcU7uDFHA.3324@.TK2MSFTNGP15.phx.gbl...
> You cannot use variables for tables, columns, etc. in queries. You'll need
> to use dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Go back to your freshman software engineering books and look up the
concepts of coupling and cohesion.
What you want is a procedure with so little cohesion that any random
user, present or future, has more control over the RDBMS than the DBA.
This is **foundations of programming**, not anything particular to SQL.|||It seems that you are using column names to represent data. This is a
fundamental mistake and causes lots of problems, such as the one you
are having here. Columns should represent a single attribute and
repeating groups of columns are in violation of First Normal Form. Fix
your table design and you won't need messy dynamic SQL to accomplish
this.
David Portas
SQL Server MVP
--