Passing parameter in MDX query

Hi,
I am facing problem during passing the parameters in MDX query. I need tp
pass two parameter in my dataset, but not sure whether I am writing correct
query or not. as I am getting error during parsing the query.
here the query
SELECT {
[Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
} on columns,
NON EMPTY NonEmptyCrossJoin(
[Component Parts].[Part].Members,
NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
Code].Members)
)
on rows FROM [CUBE NAME]
WHERE (" + Parameters!ManufactureDate.Value + ", " +
Parameters!Suppliername.Value + ")
--
The two parameter I have declared in parameter box with multiselect value
option.
while executiing the query I am gettiign belwo message.
"The tuple function expects a tuplet set of expression for the argument.A
string or numeric was used"
Please help me to write the correct syntax for this mdx.
Appreciate your help
Regards
SomuMosha Pasumansky has written an article called "Writing multiselect friendly
MDX calculations":
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
Maybe you'll find your answer there. :)
Have you tried your query without parameters in an MDX parser? If not, try
it and you might figure out how the query should be before trying with the
parameters.
Kaisa M. Lindahl
"Somu" <Somu@.discussions.microsoft.com> wrote in message
news:65941D3D-8CBB-494C-9E95-3F4FD983E31F@.microsoft.com...
> Hi,
> I am facing problem during passing the parameters in MDX query. I need tp
> pass two parameter in my dataset, but not sure whether I am writing
> correct
> query or not. as I am getting error during parsing the query.
> here the query
> SELECT {
> [Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
> } on columns,
> NON EMPTY NonEmptyCrossJoin(
> [Component Parts].[Part].Members,
> NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
> Code].Members)
> )
> on rows FROM [CUBE NAME]
> WHERE (" + Parameters!ManufactureDate.Value + ", " +
> Parameters!Suppliername.Value + ")
> --
> The two parameter I have declared in parameter box with multiselect value
> option.
> while executiing the query I am gettiign belwo message.
> "The tuple function expects a tuplet set of expression for the argument.A
> string or numeric was used"
> Please help me to write the correct syntax for this mdx.
> Appreciate your help
> Regards
> Somu

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).
|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

sql

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

passing parameter from vb 6 to crystal report 8

I am trying to pass value to the parameter field which i have created in crystal report 8. the parameter field is of type date. but i get error. the code is as follows.

.Reset
.ReportFileName = App.Path & "\reports\vaccine_flow_ledger.rpt"
.Connect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=APACCESS"
.DiscardSavedData = True

.ParameterFields(0) = "SDT;" & #1/7/2005#
.ParameterFields(1) = "EDT;" & #1/7/2005#

.Action = 1
End With

Please help me to resolve this problem.Try this

.ParameterFields(0) = "SDT;"# & 1/7/2005 & #"
.ParameterFields(1) = "EDT;"# & #1/7/2005& #"|||I tried that one,but i didn't work.

but

.parameterfields(0) = "sdt;" & "date(2005,1,1);"

works finesql

passing parameter from stored proc to crystal

Im using crystal 11.5.

Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.

Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.

In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.

What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.

How do I get crystal to prompt me when I add the stored proc to the report?

passing parameter FROM REPORT TO WINDOWS APP

Hi all. Is it possible to pass parameter FROM Report to my windows application(C#) ? Report is made in Business Intelligence Project. I just want to retrieve the total row number to my windows appication. Is it possible? If yes, Can you provide codes for this? Thanks. Your help would be highly appreciated.

-Ron-

Yes you can pass parameter values from your report to your windows application.

Have a default value for your parameter which is the count of rows of the table and then use Values property of ReportParametersInfo class ro retrieve the value.

Shyam

|||Excluding creative approaches such as exporting the report to HTML and parsing the HTML payload, in general the user has to initiate an action explicitly in the report. Then, assuming you use the Windows Report Viewer, you can sink the event. See the hypelink example in this article.|||

Shyam - I have no idea of what you are saying. Can you provide me sample codes for this? thanks.

Teo Lachev - yes i am using windows report viewer. can't find the hyperlink example in link you gave.

Please Help me....

-Ron-

|||In the link I gave you, there is another link that will bring you to a DevX article. From there you can download a code sample that shows how to sink a hyperlink event.

passing parameter from asp.net application to reporting services report

Hi,

There is a .net application which has a screen with four options(text boxes)

Each of these should take to reports generated by SQL REp Services

This is the requirment

The School selected in the App should be passed on to the report

How should I pass the parameter in my report

so that when user selects a link or text box report for that particular school number is displayed

Thanks

sowree

Are you displaying the report in a report viewer control, or opening up a new browser window with the report URL?

BobP

|||

hi

the report is being viewed using report manager, so the report I have deployed to report server must be displayed when the user selects the menu selecting a school number in the asp app.

I need to know how i pass the parameter in my report rdl 's stored procedure to select the school number selected in the ASP application

Thanks

|||

You would create a parameter in the report rdl to accept the school number, and then use that parameter in the call to the stored proc.

For example: You create a parameter called @.SchoolNumber

exec spgSchoolInfo @.SchooNumber

To pass the parameter to the report in report manager, you would put the school number in the URL: (http://msdn2.microsoft.com/en-US/library/ms153586.aspx)

Example: http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&SchoolNumber=1234

HTH

BobP

Passing parameter from Asp.Net

Dear Sir,
How can reporting servicing receive parameter from Asp.net program?You could call a report via URL access and pass the parameter to the URL.
Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> Dear Sir,
> How can reporting servicing receive parameter from Asp.net program?
>|||Yes, via the SOAP API Render() method. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
for an example.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"yoclee" <yoclee@.discussions.microsoft.com> wrote in message
news:886688CD-0C5F-41D4-88E8-8134AD61D579@.microsoft.com...
> Can we pass the parameter using others method instead of using URL?
> "Ravi Mumulla (Microsoft)" wrote:
> > You could call a report via URL access and pass the parameter to the
URL.
> > Refer to
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
> > for details.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> > > Dear Sir,
> > > How can reporting servicing receive parameter from Asp.net program?
> > >
> > >
> >
> >
> >sql

Passing parameter from a webpage to a report

I am developing a website which need to allow user to pass through several webpages for criteria selection and generate a report base on the selected criteria finally.

Anyone know how to pass the selected value from a webpage to the SQL statement or stored procedure which used to generate the report? Or there are other methods to do so?look at the rendering methods of RS - there's one by URL where you can pass the parameters via URL and request the report.|||I believe you simply build a querystring and append the parameters and values to the end of the querystring.|||Thanks for your kindly reply.

I have another related question see whether anyone can help. Base on my current knowledge, I know that we can pass one value for each parameter. E.g. countryID=20. If I want to pass multiple values for this parameter, e.g. countryID=20, 21, 25, ... How can I do so? Also, how should I set it in report designer for this purpose?|||Can you explain little more in detail (perhaps with some sampoe) ? Is your data in the format 20,21,25 for each record ?|||You could pass in a string like you describe. You would need to have logic in your report or stored procedure to handle such a request, though...|||In my report, there is a chart to compare sales performance between several products which are selected by user. The number of selected products is not fixed. I need to pass multiple productID into the report.

Also, I face a problem that ...seem I can't pass parameters into SQL functions to build dataset. Is it a rule? The error msg is "Syntax error or access violation.". My statement is as follows:

select * from getTable(@.productID)

Notes: getTable is a user-defined function.|||so what u can do

Select * from table where productid in (@.productid)|||In the following statement, getTable is a function with many calculation.

select * from getTable(@.productID)

If I use the one below, I can't get what I want. Also, seem can't be a string e.g. "12, 13, 14"
Select * from table where productid in (@.productid)|||so just pass the value in paenthesis

like '12,13,14'. it will definitely work.

If it is a function. then it should return the value same as u mentioned like "12,13,14"
if u can make it " '12,13,14' " . Then you can pass on values.

passing parameter

I have a stored procedure named "processInventory" like the following.
Depending on the passed in parameters, I would like to add a WHERE
clause for "select" action. For example, if any varchar type of
parameter is passed in, the where clause would use "LIKE" operator. For
example, "Select * from Main where [s/n] like @.Serial. All other types
will use "=" operator. For example, "Select * from Main where MAKE =
@.Make and Type = @.type".
How could this be achieved? Thanks.

CREATE PROCEDURE processInventory
@.Action varchar(7),
@.ControlNumber int = null,
@.AssetTag int = null,
@.Serial varchar(50) = null,
@.Description varchar(50) = null,
@.Make int = null,
@.Type int = null,
@.Model int = null,
@.Status int = null,
@.Networked bit = null,
@.LoginName varchar(50) = null,
@.Shared bit = null,
@.Org varchar(15) = null,
@.RecordDate datetime = null,
@.LastUpdate datetime = null,
@.ManufactureDate datetime = null,
@.Comment ntext = null

AS

declare @.processError int
set @.processError = 0

if @.Action = 'Select' goto selectInventory
else
If @.Action = 'Update'
begin
if @.ControlNumber = null return(1) --Required parameter value not
specified
else
goto updateInventory
end
else
if @.Action = 'Insert'
begin
if @.Serial = null return(1) --Required parameter value not
specified
else
goto InsertInventory
end
else
if @.Action = 'Delete'
begin
if @.ControlNumber = null return(1) --Required parameter value
not specified
else goto deleteInventory
end

selectInventory:
if @.Serial <> null
begin
select * from Main where [S/N] like @.Serial
if @.@.Error<>0
begin
set @.processError = @.@.Error
return @.processError
end
end
else
if @.ControlNumber <> null
begin
select * from Main where ControlNumber = @.ControlNumber
if @.@.Error <>0
begin
set @.processError = @.@.Error
return @.processError
end
end
else
select top 100* from Main

updateInventory:
update MAIN
set [Org Asset Tag] = @.AssetTag, [S/N] = @.Serial, [Description]
= @.Description, Make = @.Make, Type = @.Type,
Model = @.Model, Status = @.Status, Networked = @.Networked,
LoginName = @.LoginName, Shared = @.Shared,
Org = @.Org, [Date Of Record] = @.RecordDate, [Date Last
Updated] = @.LastUpdate, [Manuf Date] = @.ManufactureDate,
Comments = @.Comment
where ControlNumber = @.ControlNumber
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else
return(0) -- successful update

insertInventory:
insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,
Model, Status, Networked, LoginName, Shared,
Org, [Date Of Record], [Date Last Updated], [Manuf
Date],Comments)
values(@.AssetTag, @.Serial, @.Description, @.Make, @.Type, @.Model,
@.Status, @.Networked, @.LoginName, @.Shared,
@.Org, @.RecordDate, @.LastUpdate, @.ManufactureDate,
@.Comment)
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else return(0) -- successful insert

deleteInventory:
delete MAIN where ControlNumber = @.ControlNumber
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else return(0) -- successful delete
GOFirst, I would suggest that you not lump all of your actions together
in one stored procedure; you will suffer from a performance impact,
because SQL Server will be forced to recompile your procedure every
time it runs (for SELECT, UPDATE, or DELETE). This is never a good
idea.

That being said, you could set the default value of the parameter you
wish to use wildcards on as a wildcard ('%'); later, in the body of the
stored procedure, add a wildcard character to the value before you use
it in the query. A simple example is below:

CREATE PROC procTestWildcard @.Param varchar(10) = '%' AS

SET @.Param = @.Param + '%'

SELECT Column
FROM Table
WHERE Column Like @.Param

--

Running the following

exec procWildCardTest

will return all of the data in your table since you've essentially run
the statement

SELECT Column
FROM Table
WHERE Column Like '%%'

The statement

exec procWildCardTest 'S'

will return all of the data in your table that starts with the letter
'S', since the SQL statement is now interpreted as

SELECT Column
FROM Table
WHERE Column Like 'S%'

HTH,
Stu|||Thanks for your suggestion. As you can see, I have more than one
parameter that might be passed into the proc. How do I dermine which
one is passed in? If I use IF..ELSE, there would be many combination
of parameters. I don't think SQL2000 allow concation of partitial
statments, so each combination need to be dealt with like
IF @.Make <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @.Make
ELSE
IF @.Make <> NULL AND @.Model <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @.Make and MODEL LIKE
@.Model
ELSE
other paramter combination|||The suggestion I gave above will work for any number of paramater
combinations. Not the most effecient way, but it will work.

CREATE PROC procTestParams (@.Make varchar(10) = '%', @.Model varchar(10)
= '%') AS

SET @.Make = @.Make+'%'
SET @.Model = @.Model+'%'

SELECT COL1, COL2
FROM TABLE
WHERE MAKE LIKE @.Make
and MODEL LIKE @.Model

Another way to do this is to build your SQL string dynamically and use
sp_executeSQL

CREATE PROC procTestParams (@.Make varchar(10) =NULL, @.Model
varchar(10) = NULL) AS

DECLARE @.SQL nvarchar(4000)

/*Return all records by default; need a basic true WHERE condition so
that you can
append AND's to it as needed*/

SET @.SQL = 'SELECT COL1, COL2 FROM TABLE WHERE 1=1 '

IF @.Make IS NOT NULL
SET @.SQL =@.SQL + ' AND Make LIKE @.Make ' --make sure that you are
passing wildcards if needed

IF @.Model IS NOT NULL
SET @.SQL =@.SQL + ' AND Model LIKE @.Model '

exec sp_executeSQL @.SQL, N'@.Make varchar(10), @.Model varchar(10)',
@.Make, @.Model

You'll just have to play around with it to see which is more effecient;
the first version will basically run a search against all parameters,
looking for wildcards (any data) on the columns you don't specify a
value for, whereas the second version will dynamically build a SQL
statement to be executed against only those columns you supply a
parameter for. The effeciency of either approach is going to be
affected by the number and atype of indexes on your table, and the
amount of data to be returned.

Hope that clarifies.

Stu|||[posted and mailed, please reply in news]

js (androidsun@.yahoo.com) writes:
> I have a stored procedure named "processInventory" like the following.
> Depending on the passed in parameters, I would like to add a WHERE
> clause for "select" action. For example, if any varchar type of
> parameter is passed in, the where clause would use "LIKE" operator. For
> example, "Select * from Main where [s/n] like @.Serial. All other types
> will use "=" operator. For example, "Select * from Main where MAKE =
> @.Make and Type = @.type".
> How could this be achieved? Thanks.

I have a longer article on the topic on
http://www.sommarskog.se/dyn-search.html.

Since you are into UPDATE, I would careful with using dynamic SQL
because of the permissions issues.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing Parameter

I've to create an instance reference to my custom assembly by constructor
method.
It works well with a Code into the report .rdl like this:
---
<Code>
Public Obj As MyClass
Protected Overrides Sub OnInit()
dim MyArg as String = 4
Obj = new MyClass(MyArg)
End Sub
</Code>
--
But i need to pass a real parameter so:
Obj = new MyClass(Parameters!MyPar.Value)
it don't work and break with the error
BC30469 "The reference to a member not shared needs a reference to an object"
(I translate this from the italian version.... sorry for my bad english!!!
How can i do'
Thanks a lot.Try using this reference.
Report.Parameters!MyPar.Value
that should work in the Code.section
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> I've to create an instance reference to my custom assembly by constructor
> method.
> It works well with a Code into the report .rdl like this:
> ---
> <Code>
> Public Obj As MyClass
> Protected Overrides Sub OnInit()
> dim MyArg as String = 4
> Obj = new MyClass(MyArg)
> End Sub
> </Code>
> --
> But i need to pass a real parameter so:
> Obj = new MyClass(Parameters!MyPar.Value)
> it don't work and break with the error
> BC30469 "The reference to a member not shared needs a reference to an
> object"
> (I translate this from the italian version.... sorry for my bad
> english!!!
> How can i do'
> Thanks a lot.|||This not works, but the error is changed with a message box:
" Unable to load the assembly expressions. The expression refers to a
nonexistent parameter in the parameters's collection.".
This is not true, becouse the parameter exists.
Can you help me again?
"Wayne Snyder" wrote:
> Try using this reference.
> Report.Parameters!MyPar.Value
> that should work in the Code.section
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
> news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> > I've to create an instance reference to my custom assembly by constructor
> > method.
> > It works well with a Code into the report .rdl like this:
> > ---
> > <Code>
> > Public Obj As MyClass
> > Protected Overrides Sub OnInit()
> > dim MyArg as String = 4
> > Obj = new MyClass(MyArg)
> > End Sub
> > </Code>
> > --
> > But i need to pass a real parameter so:
> > Obj = new MyClass(Parameters!MyPar.Value)
> > it don't work and break with the error
> > BC30469 "The reference to a member not shared needs a reference to an
> > object"
> > (I translate this from the italian version.... sorry for my bad
> > english!!!
> > How can i do'
> > Thanks a lot.
>
>

Passing Paramenter to SP for Column Name

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@.SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@.SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type
int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michaelhi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@.selectColumn varchar(255))
as
set nocount on
declare @.dynamicSql varchar(8000)

select @.dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @.selectColumn + ' = 0)
'
execute (@.dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:
> I need to select status values form 1 of 4 possible columns, and I
need to
> pass the column name to select on as a parameter to the stored
procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
> SET NOCOUNT ON;
> SELECT *
> FROM Documents
> WHERE (@.SelectColumn = 0)
> The columns to select on are BIT columns.
> The error message on the above SP is:
> 'Syntax error converting the nvarchar value 'P' to a column of data
type
> int.'
> At this point, the passed in parameter is a string "ProducerStatus"
> Thanks
> Michael|||Thanks for the help. It worked great.

"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.z14g2000cwz.googlegr oups.com...
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
> create procedure dbo.sp_Document_select_bystatus
> (@.selectColumn varchar(255))
> as
> set nocount on
> declare @.dynamicSql varchar(8000)
> select @.dynamicSql = '
> SELECT *
> FROM Documents
> WHERE ( ' + @.selectColumn + ' = 0)
> '
> execute (@.dynamicSql)
> set nocount off
> Go
> Thank you
> santhosh
> Michael Jackson wrote:
>> I need to select status values form 1 of 4 possible columns, and I
> need to
>> pass the column name to select on as a parameter to the stored
> procedure.
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
>> (
>> @.SelectColumn nVarChar
>> )
>> AS
>>
>> SET NOCOUNT ON;
>>
>> SELECT *
>> FROM Documents
>> WHERE (@.SelectColumn = 0)
>>
>> The columns to select on are BIT columns.
>>
>> The error message on the above SP is:
>>
>> 'Syntax error converting the nvarchar value 'P' to a column of data
> type
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus"
>>
>> Thanks
>> Michael|||Avoid dynamic SQL if you can. In this case you don't need it:

SELECT col1
FROM Documents
WHERE col1 = 0 AND @.selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @.selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @.selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @.selectcolumn = 'col4'

To understand why dynamic SQL isn't a good idea for this, see:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--|||Michael Jackson (stratojack@.cox.net) writes:
> I need to select status values form 1 of 4 possible columns, and I need to
> pass the column name to select on as a parameter to the stored procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS

To add to the other responses, permit me to point out two other flaws:

1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.

2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Passing output parameter from procedure to variable

I have a stored procedure that runs a query and the result of the query is a
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:

> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks

Passing Out Parameter to Sybase Stored Proc from RS

I am defining the Output parameter from Sybase Stored Proc.
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql

Passing Order By as parameter

A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
solution:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/t
hread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/u
rl]
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
Now I have a situation with multiple fields on which to sort, one of them
being descending. How can I use this method to come out with ORDER BY Price
DESC, Title ?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
DVBrown Company
(864)292-5888You could try ORDER BY -price
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:uEOm$UzzFHA.908@.tk2msftngp13.phx.gbl...
>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
> solution:
> [url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm
/thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[
/url]
> select * from titles
> order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
> Now I have a situation with multiple fields on which to sort, one of them
> being descending. How can I use this method to come out with ORDER BY
> Price
> DESC, Title ?
> Thanks.
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> DVBrown Company
> (864)292-5888
>|||On Wed, 12 Oct 2005 09:59:33 -0400, Daniel Wilson wrote:

>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
>solution:
>[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/
thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/
url]
>select * from titles
>order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
>Now I have a situation with multiple fields on which to sort, one of them
>being descending. How can I use this method to come out with ORDER BY Pric
e
>DESC, Title ?
>Thanks.
Hi Daniel,
select * from titles
order by
case when @.col1 = 'title' and @.dir1 = 'asc' then title end,
case when @.col1 = 'title' and @.dir1 = 'desc' then title end desc,
case when @.col1 = 'price' and @.dir1 = 'asc' then price end,
case when @.col1 = 'price' and @.dir1 = 'desc' then price end desc,
case when @.col2 = 'title' and @.dir2 = 'asc' then title end,
case when @.col2 = 'title' and @.dir2 = 'desc' then title end desc,
case when @.col2 = 'price' and @.dir2 = 'asc' then price end,
case when @.col2 = 'price' and @.dir2 = 'desc' then price end desc,
..
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

passing olap parameters in url

hi. could anyone pls tell me how to pass parameter values on url to an olap
RS 2005 report? i tried to do it as such and got an error.
parameter name is AbsenceTypesAbsenceTypes
parameter value is =[Absence Types].[Absence Types].[Absence Type].&[Absent
Days]
this is the link:
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCA_Reports%2fcAtt_01&rs:Command=Render&AbsenceTypesAbsenceTypes=[Absence
Types].[Absence Types].[Absence Type].&[Absent Days]
this is the error:
The path of the item "/CA_Reports/cAtt_01,[Absent Days]" is not valid. The
path must be less than 260 characters long and must not start with slash.
Other restrictions apply.
it's obvious that the & in the value of the parameter is causing this
problem.
also if somebody knows of a site where i can find help on this subject. i've
searched a lot and none talks about the current version of RS with olap.
ThanksURL parameter values always must be encoded. E.g. & needs to be encoded as
%26, [ would be %5B, ] would be %5D.
Lookup the HttpUtility class on MSDN - it provides a static method
HttpUtility.HtmlEncode to perform this encoding.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"TinaWina" <TinaWina@.discussions.microsoft.com> wrote in message
news:1B900061-7EF8-4FC5-AA0D-EE61C20E5E53@.microsoft.com...
> hi. could anyone pls tell me how to pass parameter values on url to an
> olap
> RS 2005 report? i tried to do it as such and got an error.
> parameter name is AbsenceTypesAbsenceTypes
> parameter value is =[Absence Types].[Absence Types].[Absence
> Type].&[Absent
> Days]
> this is the link:
> http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCA_Reports%2fcAtt_01&rs:Command=Render&AbsenceTypesAbsenceTypes=[Absence
> Types].[Absence Types].[Absence Type].&[Absent Days]
> this is the error:
> The path of the item "/CA_Reports/cAtt_01,[Absent Days]" is not valid. The
> path must be less than 260 characters long and must not start with slash.
> Other restrictions apply.
> it's obvious that the & in the value of the parameter is causing this
> problem.
> also if somebody knows of a site where i can find help on this subject.
> i've
> searched a lot and none talks about the current version of RS with olap.
> Thanks|||Thank you very much.
It worked:)
"Robert Bruckner [MSFT]" wrote:
> URL parameter values always must be encoded. E.g. & needs to be encoded as
> %26, [ would be %5B, ] would be %5D.
> Lookup the HttpUtility class on MSDN - it provides a static method
> HttpUtility.HtmlEncode to perform this encoding.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TinaWina" <TinaWina@.discussions.microsoft.com> wrote in message
> news:1B900061-7EF8-4FC5-AA0D-EE61C20E5E53@.microsoft.com...
> > hi. could anyone pls tell me how to pass parameter values on url to an
> > olap
> > RS 2005 report? i tried to do it as such and got an error.
> > parameter name is AbsenceTypesAbsenceTypes
> > parameter value is =[Absence Types].[Absence Types].[Absence
> > Type].&[Absent
> > Days]
> >
> > this is the link:
> > http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fCA_Reports%2fcAtt_01&rs:Command=Render&AbsenceTypesAbsenceTypes=[Absence
> > Types].[Absence Types].[Absence Type].&[Absent Days]
> >
> > this is the error:
> > The path of the item "/CA_Reports/cAtt_01,[Absent Days]" is not valid. The
> > path must be less than 260 characters long and must not start with slash.
> > Other restrictions apply.
> >
> > it's obvious that the & in the value of the parameter is causing this
> > problem.
> >
> > also if somebody knows of a site where i can find help on this subject.
> > i've
> > searched a lot and none talks about the current version of RS with olap.
> >
> > Thanks
>
>

Passing of parameter

Need help for the passing of parameter in CR9.

How to pass the "record_id" from main report to the sql command of subreport. To clarify, i add a sql command in database expert in fmy subreport. This is my query....

For the main report..

Select record_id, distributor_name... from distributor

In the sql command of my subreport....

Select record_id, distributor_name, customer_id, sum(sales) as amount where record_id =2 group by record_id, distributor_name, customer_id
order by amount desc limit 20.

All i want to do is how to pass the record_id from the main report into the sql command of my subreport to look like this....

Select record_id, distributor_name, customer_id, sum(sales) as amount where record_id =("record_id from main_report") group by record_id, distributor_name, customer_id
order by amount desc limit 20.

My goal is how to view the top 20 accounts per distributor

Thanx a lot!!!1. Make main report
2. make sub report with same parameters.
3. In Design mode of main report, edit->subreport links.
Select main report parameter and link it to subreport parameter.

Passing Object Variable as input parameter to an Execute SQL Task Query

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

Please give me solutions for the above two..

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||

Darren,

Thanks for your solution,

Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.

When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.

Moreover I want the solution for the step 2 as well|||

DarrenSQLIS wrote:

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx

@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)

Just cast it to an integer and all will be well.

select cast(@.@.IDENTITY as int) as 'Ident'|||

Thanks Phil,

It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.

There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

|||

Subhash wrote:

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

That works for me, using OLE DB connection though. What type of connection are you using?

|||

Hi Rafael,

I am using OLEDB connection.

When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.