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

Pagination question - retrieve only records for each page

I want to provide paged access to data. Rather than displaying the
entire contents of a report or query to an end user, I want to show
only a subset of records per web page, with controls for moving from
page to page. I have a large result set and only want to selectively
retrieve only those records that should be shown for the particular
page. I know how to do this in ASP.NET but I'm new to Report Server.
Can anyone point me in the right direction?
ThanksThis is default Reporting Services behavior.
RS will give you the functions to move from page to page.
You have to design the report to use parameters, and then add formatting to
the report to make it break over several pages.
If you have the SQL Server 2005 setup available, install the Report Designer
on your workstation and play around with it. (Or download a trial version of
SS 2005) There are lots of good articles to get you going. A good place to
start is with William Pearson's articles about Reporting Services at
http://www.databasejournal.com/article.php/1459531 (scroll down to find
them).
Kaisa M. Lindahl Lervik
"fparc" <fparc@.aol.com> wrote in message
news:1165461353.254431.13270@.j72g2000cwa.googlegroups.com...
>I want to provide paged access to data. Rather than displaying the
> entire contents of a report or query to an end user, I want to show
> only a subset of records per web page, with controls for moving from
> page to page. I have a large result set and only want to selectively
> retrieve only those records that should be shown for the particular
> page. I know how to do this in ASP.NET but I'm new to Report Server.
> Can anyone point me in the right direction?
> Thanks
>|||Thanks for the link. What a great resource. I was thinking of
pagination in terms of record retrieval instead of page breaks. I want
to query 50 records at a time so I don't tax the database. After
looking at it a bit I think I will try to use the TimeStamp field as
the index. So I put a text box that holds the Last timestamp of the
group of TOP 50. I make the textbox a 'Jump to URL' and pass the last
timestamp on the end of the URL to open the new report page. One
problem; I can't get the parameterize URL to work in Report Server. I'm
using the wrox Professional SQL Server 2005 Reporting Services book but
the URL format they give is wrong(a pre-release book no doubt). So now
I need to find a good simple test to see if I can get this working
first. Know of any good tutorials on passing params in URL for Report
Server?|||I got the parameterize URL to work in Report Server. I was wrong about
the wrox Professional SQL Server 2005 Reporting Services book being
wrong. I was using "http://server/SQLReports/Pages/Folder.aspx?"
instead of "http://server/ReportServer?/folder/file" in my Jump URL.
Now I have 12 parameters including the TimeStamp the user can choose
from. By choosing the Allow Blank Value property from the Report
Parameters Dialog Box, the user can fill in all or none of the
parameter text boxes for various searches - except the Timestamp which
defaults to =Now.
My SQL pulls TOP 50 records. When the user clicks on the 'Jump to URL'
text box the timestamp param of the 50th record is passed in the URL to
pull the next 50.
Now I have a type text column filled with XML in which there are 2
things we need to pull - an email and/or an event_code. My next step is
to change the table column from text to the XML type to speed up the
searches on that column. Here's what I've found so far.
http://www.developer.com/db/article.php/3531196
http://www.15seconds.com/issue/050803.htm
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://www.ftponline.com/vsm/2005_06/magazine/features/rjennings/