Dynamic query

Hello!
I tryed the example from Reporting Services Books Online but it didn't work.
I copied ="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
in generic query designer and when I ran the report I received the following
error: Cannot use empty object or column names. Use a single space if
necessary.If department ID is a string then you need to embed it in single quotes. I
suggest doing the following, create a textbox and assign expression to it so
you can see whether you have created it correctly.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:FCD2E55C-37F9-43C9-AF60-BAF2E9FF468E@.microsoft.com...
> Hello!
> I tryed the example from Reporting Services Books Online but it didn't
work.
> I copied ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> in generic query designer and when I ran the report I received the
following
> error: Cannot use empty object or column names. Use a single space if
> necessary.
>

Dynamic formatting

Can someone please provide me some pointers on dynamic formatting. How
can I change the formatting information from code.
Where on SQL Books online can I get information on dynamic formatting
Thanks
KarenOn Jul 6, 10:03 am, KarenM <karenmiddl...@.yahoo.com> wrote:
> Can someone please provide me some pointers on dynamic formatting. How
> can I change the formatting information from code.
> Where on SQL Books online can I get information on dynamic formatting
> Thanks
> Karen
I'm not sure that I understand your question; however, you can select
a field in Layout view -> select F4 (for the Properties window) ->
select <Expression...> to the right of Format and you can enter an
expression w/an if, choice or switch statement based on the value in
the field, etc. Here are a couple of examples.
=iif(CStr(Fields!FieldName.Value) Like "*.*", "$#,0.00", "#,0") -or-
something like
=iif(Fields!FieldName.Value < 0, "(#,0)", "#,0") -or- something like
=switch(CStr(Fields!FieldName.Value) Like "*.*", "$#,0.00", Fields!
FieldName.Value < 0, "(#,0)", true, "#,0")
Also, this link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

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