Dynamic sql problem with reporting services

Julia B

unread,

Jan 5, 2010, 11:51:01 AM1/5/10

to

Hi all,

I've got a report linked to a stored procedure which is a dynamic sql union
query (apologies if this is the wrong terminology I'm new to this!).

The union query joins 3 tables and has selection criteria added depending on
the parameters selected by the user.

I've simplified the stored procedure to show you how it looks here:

ALTER PROCEDURE SearchResultsAll
/* This stored procedure uses dynamic sql to build a stored procedure for
search results based on user selected criteria
*/

@docno varchar(50)

AS

Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

/* Build the Transact-SQL String with the input parameters */
Set @SQLQuery = 'SELECT T_Manuals.ManualNo_PK AS "Item Part No"
FROM T_Manuals
WHERE (T_Manuals.ManualNo_PK IS NOT NULL)'

/* check for the condition and build the WHERE clause accordingly */
If @docno Is Not Null
Set @SQLQuery = @SQLQuery + ' AND (T_Manuals.ManualNo_PK = @docno)'

/* Continue to build the union Transact-SQL String with the input
parameters */
Set @SQLQuery = @SQLQuery + ' UNION SELECT T_Exceptions.ExceptionNo_PK
AS "Item Part No"
FROM T_Exceptions
WHERE (T_Exceptions.ExceptionNo_PK IS NOT NULL)'

/* check for the condition and build the WHERE clause accordingly */
If @docno Is Not Null
Set @SQLQuery = @SQLQuery + ' AND (T_Exceptions.ExceptionNo_PK = @docno)'

/* Continue to build the union Transact-SQL String with the input
parameters */
Set @SQLQuery = @SQLQuery + ' UNION SELECT T_Software.SoftwarePN_PK
AS "Item Part No"
FROM T_Software
WHERE (T_Software.SoftwarePN_PK IS NOT NULL)'

/* check for the condition and build the WHERE clause accordingly */
If @docno Is Not Null
Set @SQLQuery = @SQLQuery + ' AND (T_Software.SoftwarePN_PK = @docno)'

Set @SQLQuery = @SQLQuery + ' ORDER BY "Item Part No"'

/* Specify Parameter Format for all input parameters included
in the stmt */
Set @ParamDefinition = '@docno varchar(50)’

/* Execute the Transact-SQL String with all parameter value's
Using sp_executesql Command */
Execute sp_Executesql @SQLQuery,
@ParamDefinition,
@docno

What I now want to do is to add another column to the results identifying
the item type (i.e. data that is not available in the table). If I was doing
this in a stored procedure I would add a column after each of the first
columns in the sql for each table like this:

Table 1 - 'Manual' AS Type
Table 2 - 'Exception' AS Type
Table 3 - 'Software' AS Type

But if I do this then I get an error message in Reporting Services when I
try and refresh the fields telling me I have an unknown column.

I've tried the following syntax variations and none work "Manual" AS "Type",
'Manual' AS "Type", "Manual" AS 'Type' etc.

Anyone got any ideas?
Thanks in advance!
Julia

Uri Dimant

unread,

Jan 6, 2010, 4:19:10 AM1/6/10

to

Julia
Do you also add a new columnin the dataset manually?

"Julia B" <Jul...@discussions.microsoft.com> wrote in message
news:C915B149-8493-415B...@microsoft.com...

Julia B

unread,

Jan 12, 2010, 6:43:01 AM1/12/10

to

Thanks Uri, I'm not sure what you means, so I guess that means I don't.
Should I be doing something in reporting services then?

Julia

"Uri Dimant" wrote:

> .
>

Julia B

unread,

Jan 12, 2010, 7:41:01 AM1/12/10

to

Uri, i have fixed the problem. It was the synatax in the stored procedure. I
have changed the syntax for the extra column to be:

''Exception'' AS "Type"

and both SQL and reporting services seem happy with this.

Thanks for your help.
Julia

"Uri Dimant" wrote:

> .
>

Uri Dimant

unread,

Jan 12, 2010, 8:42:32 AM1/12/10

to

That is great Julia

"Julia B" <Jul...@discussions.microsoft.com> wrote in message

news:E728703B-1C1C-4E90...@microsoft.com...