Parameter prompt value

is there a way to get parameter prompt value inside the report?
If i have parameter 1 with a prompt value "Customer ID", how can i get
"Customer ID" string value?Not really documented anywhere that I have seen but this is how to do it.
Parameters!ParamName.Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1141676084.764341.275170@.e56g2000cwe.googlegroups.com...
> is there a way to get parameter prompt value inside the report?
> If i have parameter 1 with a prompt value "Customer ID", how can i get
> "Customer ID" string value?
>

Parameter Does Not Work When Deployed

This is my first attempt at using a second dataset to fill a drop-down
parameter. This works fine inside of the designer but when I deploy to the
report server, there are no values in the drop down.
I thought perhaps it was because the data source name on the server is
different. In the past I would just update it on the server after I deploy
and it worked fine. After the deploy and after chaning the DS name, I edited
the server RDL and found that not all of the DS names had been updated. I
then edited the names in the RDL and uploaded manually thinking this was the
problem but the DD list still does not work.
Thanks for any help!Are you using a shared data source? This could be a credentials problem. I
always use a shared datasource. Personally, I don't see much of a reason to
ever not use a shared data source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
> This is my first attempt at using a second dataset to fill a drop-down
> parameter. This works fine inside of the designer but when I deploy to the
> report server, there are no values in the drop down.
> I thought perhaps it was because the data source name on the server is
> different. In the past I would just update it on the server after I deploy
> and it worked fine. After the deploy and after chaning the DS name, I
> edited the server RDL and found that not all of the DS names had been
> updated. I then edited the names in the RDL and uploaded manually thinking
> this was the problem but the DD list still does not work.
> Thanks for any help!
>|||Bruce
Thanks for your reply
Yes, I have deployed other reports to the same folder, reset them to the
same data source and it always has worked fine. What is different is this is
the first time I used a second dataset in a report (to drive the lookup drop
down). It may not be the datasource, that was just a guess on my part. Are
there othe issues that could affect a query driven lookup such that it works
in the designer but not when deployed?
Thanks
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
> Are you using a shared data source? This could be a credentials problem. I
> always use a shared datasource. Personally, I don't see much of a reason
> to ever not use a shared data source.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names had
>> been updated. I then edited the names in the RDL and uploaded manually
>> thinking this was the problem but the DD list still does not work.
>> Thanks for any help!
>|||It could be a security issue on the database side of things. I've been
burned by that. I use one ID in development and then production is using
another one and the table/store procedure does not have the correct rights
for the production credentials. Take a look on the database at the table the
query is going against and see what the security access rights are set to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:ulgIyGijFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Bruce
> Thanks for your reply
> Yes, I have deployed other reports to the same folder, reset them to the
> same data source and it always has worked fine. What is different is this
> is the first time I used a second dataset in a report (to drive the lookup
> drop down). It may not be the datasource, that was just a guess on my
> part. Are there othe issues that could affect a query driven lookup such
> that it works in the designer but not when deployed?
> Thanks
> Mike
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using a shared data source? This could be a credentials problem.
>> I always use a shared datasource. Personally, I don't see much of a
>> reason to ever not use a shared data source.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names had
>> been updated. I then edited the names in the RDL and uploaded manually
>> thinking this was the problem but the DD list still does not work.
>> Thanks for any help!
>>
>|||Bruce
The security seems ok and the main query for dataset-1 works fine. The same
data source is used for dataset-1 and dataset-2 which drives the param
drop-down list. Maybe it is not the data source since I am not getting any
erorrs. This is what happens: the the drop down list is horizontally
truncated and there are no values in displayed when you select it. It just
displays 'Select Value'.
Thanks
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23fTmvMijFHA.3608@.TK2MSFTNGP12.phx.gbl...
> It could be a security issue on the database side of things. I've been
> burned by that. I use one ID in development and then production is using
> another one and the table/store procedure does not have the correct rights
> for the production credentials. Take a look on the database at the table
> the query is going against and see what the security access rights are set
> to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:ulgIyGijFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Bruce
>> Thanks for your reply
>> Yes, I have deployed other reports to the same folder, reset them to the
>> same data source and it always has worked fine. What is different is this
>> is the first time I used a second dataset in a report (to drive the
>> lookup drop down). It may not be the datasource, that was just a guess on
>> my part. Are there othe issues that could affect a query driven lookup
>> such that it works in the designer but not when deployed?
>> Thanks
>> Mike
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using a shared data source? This could be a credentials problem.
>> I always use a shared datasource. Personally, I don't see much of a
>> reason to ever not use a shared data source.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names
>> had been updated. I then edited the names in the RDL and uploaded
>> manually thinking this was the problem but the DD list still does not
>> work.
>> Thanks for any help!
>>
>>
>

parameter

Hi,

I am using a stored proc which takes in a parameter from a webpage. I am using freetext and have to pass in a parameter inside quotes so that freetext treats the phrase as a single word.

something similar to this.

freetext(SITE_NAME,' " '+ @.Search+' " ')

this is giving error.

Please Help

try using the char function passing it the numeric ascii code for double quotes in your sql statement: char(39)

|||

Hi bendJoe,

I am using freetext and have to pass in a parameter inside quotes so that freetext treats the phrase as a single word

Could you please give us an example what the @.Search parameter could be? Also, why do you want FreeText function treate your search words as a singe world?

Based on my understanding, we can pass in more than one words into FreeText predicate and those words will be seperated and assigend a weight value automatically. And that's how FreeText function is designed. See the defination in Books Online:

Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on thefreetext_string, assigns each term a weight, and then finds the matches.

Separates the string into individual words based on word boundaries (word-breaking).

Generates inflectional forms of the words (stemming).

Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

paramater values inside IN clause

I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql