paramater values inside IN clause

microsoft.public.sqlserver.programming

23 Jun 2006 4:34 PM

Pedro

I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn´t 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?

23 Jun 2006 4:46 PM

Arnie Rowland

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_co***@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´t 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?

23 Jun 2006 4:57 PM

Pedro

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:
Show quote

> 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_co***@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´t 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?

23 Jun 2006 5:04 PM

Arnie Rowland

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_co***@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:
Show quote

> 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_co***@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´t 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?

23 Jun 2006 5:21 PM

Pedro

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:
Show quote

> 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_co***@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_co***@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´t 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?

23 Jun 2006 5:30 PM

Arnie Rowland

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_co***@sapo.pt> wrote in message
news:1151083291.840423.149320@r2g2000cwb.googlegroups.com...

> 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_co***@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_co***@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´t 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?

23 Jun 2006 5:57 PM

Pedro

Sorry, but I' ve supllied wrong information:
@strService('dsmt','merch')
and
@strBrand ('111,222')

Arnie Rowland escreveu:
Show quote

> 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_co***@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_co***@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_co***@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´t 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?

23 Jun 2006 6:28 PM

Arnie Rowland

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_co***@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:
Show quote

> 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_co***@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_co***@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_co***@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´t 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?

23 Jun 2006 6:02 PM

Anith Sen

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

23 Jun 2006 6:59 PM

Arnie Rowland

That's not completely true. Using dynamic SQL, try the following: (@EmpList is easily a passed in param)

USE Northwind
GO

DECLARE
     @EmpList varchar(50)
   , @SQL     nvarchar(500)

SET @EmpList = '2,5'

SET @SQL = 'SELECT
               EmployeeID
             , LastName
            FROM Employees
            WHERE EmployeeID IN (' + @EmpList + ')'

EXECUTE( @SQL )

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:%23KYWq8ulGHA.884@TK2MSFTNGP05.phx.gbl...
> 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
>
>

23 Jun 2006 7:08 PM

Stu

But that's not quite the same thing; you're passing in @EmpList to
finish building a string, which is then parsed and interpreted as a
whole SQL statement. You're not providing a parameter for the IN();
you're adding characters to a varchar.

Arnie Rowland wrote:
Show quote

> That's not completely true. Using dynamic SQL, try the following: (@EmpList is easily a passed in param)
>
>
> USE Northwind
> GO
>
> DECLARE
>      @EmpList varchar(50)
>    , @SQL     nvarchar(500)
>
> SET @EmpList = '2,5'
>
> SET @SQL = 'SELECT
>                EmployeeID
>              , LastName
>             FROM Employees
>             WHERE EmployeeID IN (' + @EmpList + ')'
>
> EXECUTE( @SQL )
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Anith Sen" <an***@bizdatasolutions.com> wrote in message news:%23KYWq8ulGHA.884@TK2MSFTNGP05.phx.gbl...
> > 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
> >
> >

23 Jun 2006 7:16 PM

Arnie Rowland

True, but still the effect is to pass a 'simulated' array into the IN().

Pedro asked for help in accomplishing his task. While not orthodox, this
will do what he asks without significant re-engineering of his entire
process.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Stu" <stuart.ainswo***@gmail.com> wrote in message
news:1151089688.888430.17860@r2g2000cwb.googlegroups.com...
> But that's not quite the same thing; you're passing in @EmpList to
> finish building a string, which is then parsed and interpreted as a
> whole SQL statement. You're not providing a parameter for the IN();
> you're adding characters to a varchar.
>
>
> Arnie Rowland wrote:
>> That's not completely true. Using dynamic SQL, try the following:
>> (@EmpList is easily a passed in param)
>>
>>
>> USE Northwind
>> GO
>>
>> DECLARE
>>      @EmpList varchar(50)
>>    , @SQL     nvarchar(500)
>>
>> SET @EmpList = '2,5'
>>
>> SET @SQL = 'SELECT
>>                EmployeeID
>>              , LastName
>>             FROM Employees
>>             WHERE EmployeeID IN (' + @EmpList + ')'
>>
>> EXECUTE( @SQL )
>>
>> --
>> Arnie Rowland, YACE*
>> "To be successful, your heart must accompany your knowledge."
>>
>> *Yet Another Certification Exam
>>
>>
>> "Anith Sen" <an***@bizdatasolutions.com> wrote in message
>> news:%23KYWq8ulGHA.884@TK2MSFTNGP05.phx.gbl...
>> > 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
>> >
>> >
>

23 Jun 2006 10:41 PM

Erland Sommarskog

Arnie Rowland (ar***@1568.com) writes:

> True, but still the effect is to pass a 'simulated' array into the IN().
>
> Pedro asked for help in accomplishing his task. While not orthodox, this
> will do what he asks without significant re-engineering of his entire
> process.

Without significant? You have an odd sense of humour! Requiring the
entire query to be put into a dynamic SQL, is indeed a significant
change. The code becomes more complex to maintain, and you lose some
of the benefits with stored procedures. Most importantly, you can run
into issues with permissions. There are also performans issues.

The proper solution is to use a function that transform the list into
a table, and join with that function. That has minimal impact on the
query, and is a clean solution.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

23 Jun 2006 6:45 PM

Mike C#

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/microsoft.public.sqlserver.programming/msg/f46e060fc982e849?dmode=source&hl=en

Here's more methods: http://www.sommarskog.se/arrays-in-sql.html

"Pedro" <p_co***@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´t 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?

23 Jun 2006 7:01 PM

Arnie Rowland

Mike,

As in all things, it depends. Check my response to Anith (above).

People do interesting things to make something work the way they want rather
than the way it 'should' be.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Mike C#" <x**@xyz.com> wrote in message
news:Oz05lUvlGHA.3528@TK2MSFTNGP02.phx.gbl...
> 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/microsoft.public.sqlserver.programming/msg/f46e060fc982e849?dmode=source&hl=en
>
> Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
>
>
>
> "Pedro" <p_co***@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´t 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?
>
>

23 Jun 2006 7:19 PM

Mike C#

But he's calling a stored procedure and that's one of the parameters. He
can't get there from here dynamically building the query string on the
client-side :) He could build it dynamically on the server side inside his
SP, but then he faces the VARCHAR or NVARCHAR size limitations and loses a
lot of the benefits of having an SP in the first place...

Show quote

"Arnie Rowland" <ar***@1568.com> wrote in message
news:ueZzidvlGHA.836@TK2MSFTNGP02.phx.gbl...
> Mike,
>
> As in all things, it depends. Check my response to Anith (above).
>
> People do interesting things to make something work the way they want
> rather than the way it 'should' be.
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Mike C#" <x**@xyz.com> wrote in message
> news:Oz05lUvlGHA.3528@TK2MSFTNGP02.phx.gbl...
>> 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/microsoft.public.sqlserver.programming/msg/f46e060fc982e849?dmode=source&hl=en
>>
>> Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
>>
>>
>>
>> "Pedro" <p_co***@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´t 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?
>>
>>
>
>

23 Jun 2006 7:47 PM

Arnie Rowland

[Message not available]

23 Jun 2006 8:00 PM

Mike C#

[Message not available]

24 Jun 2006 1:00 PM

--CELKO--

>> Can anyone help? <<

Not in a Newsgroup.

0) You do not know what a parameter is in a compiled language. You are
expecting SQL to parse a string as if you were writing BASIC or kluding
something in dynamic SQL.

But that is not your real problem. You are still writing BASIC and not
SQL. This is a text book exmaple of bad programming.

1) You violate ISO-11179 rules for names. How many totally different
things in this code are called "name"? name of what? Unless they have
one row, why are table namwes singular?

Look at this mess:

AND facturationtype.code = request.invoice_type
AND request.destination_entity = entity.code
AND request.service = service.code
AND request.code = requestitem.request
AND item.code = requestitem.item
AND request_tem.brand = brand.code

So, you have a code for entities (what a vague word! what do you have
that is NOT an entity? Only relationships), the same "code" for
services, for requests, and for brands. If they were different data
elements, they would have different names. Or do you confuse area
codes and zip codes in your data?

And notice how they are all interchangable -- an item compares to a
code to a request to a type, etc. In short, these data element have no
consistent meaning. You have no data model!

2) You put the data type as a prefix, just like BASIC required in the
1960's. We do not do that in SQL on strongly typed languages.

3) You avoid BETWEEN for things like (effective_date BETWEEN
@begin_date AND @end_date) because it not exist in BASIC. It makes the
code easier to read and maintain.

4) You avoid the IN() predicate for things like (requeststate IN
('expd', 'done', 'closed', 'atrib'), but want to use it to parse a
string.

5) All those (state = 1) predicates imply that you are using bit flags
in SQL. We don't do that; that was punch cards and assembly language
programming. It also begs the quesiion "state" of what??

6) You use temp tables. SQL programmers aovid that bit of "code
museum" in favor of dreived tables and CTEs today. What you are doing
is faking a scratch tape in a sequential, procedural design. SQL is
non-procedural and set-oriented.

7) Why did you avoid aliases in the FROM clauses that would have made
the code more readable? Yet you have them in the SELECT because your
data element names were too vague.

I would start over with a data model and begin a serious re-factoring
of this schema.