Incorrect syntax near the keyword 'group'.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Incorrect syntax near the keyword 'group'.

Author

Topic

bsvnhll
Starting Member

6 Posts

Posted - 2009-04-13 : 06:19:59

Hi,

I am calling a function inside a stored proc and passing some parameters to the function and printing the records for all case of parameters it is working but, if i pass null as a parameter in the function, i am getting 'Incorrect syntax near the keyword 'group''. error message.

below is my code sample:
--Stored proc start
CREATE PROC dbo.rpt_SupplierParts
@entityid int,
@entitytype int,
@supplierorgid int= null,
@langid smallint = 0 ,
@language sysname = 'us_english'

AS
SET LANGUAGE @language

BEGIN
set nocount on

select @selectstr =
"
select partno,partname,description,unitprice,currencyid,leadtime,createddate,
lastmodifieddate, supplierorgid,SupplierOrgName

from f_rpt_Parts_reports (" +convert(varchar(20),@entityid)+ "," + convert(varchar(20),@entitytype)+ "," + convert(varchar(20),@supplierorgid)+",0,'us_english')"

select @selectstr

END
-- Stored proc end
Exec rpt_SupplierParts 1070013,1,null,0,'us_english'

please help me.

Thanks,
Pradeep

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 06:27:45

Post the script of f_rpt_Parts_reports

Madhivanan

Failing to plan is Planning to fail

bsvnhll
Starting Member

6 Posts

Posted - 2009-04-13 : 06:32:48

this is function 'f_rpt_Parts_reports'

CREATE FUNCTION dbo.f_rpt_Parts_reports
( @entityid int,
@entitytype int,
@supplierorgid int = null,
@langid smallint = 0 ,
@language sysname = 'us_english')
RETURNS TABLE
AS
RETURN(
SELECT mps.partno,mp.partname,mp.description,mps.unitprice,mps.currencyid,mps.leadtime,mps.createddate,
mps.lastmodified as lastmodifieddate, mps.supplierorgid,ent.name as SupplierOrgName
from master_parts_supplier mps (nolock)
join master_parts mp (nolock) on mp.buyingorgid = mps.buyingorgid
and mp.partno = mps.partno
and mp.status = 'A'
join master_parts_location mpl (nolock) on mpl.buyingorgid = mps.buyingorgid
join entity ent (nolock) on ent.entityid = mps.supplierorgid
and mpl.partno = mps.partno
where ((@entitytype = 1 AND mp.buyingorgid= @entityid)OR(@entitytype = 2 AND mps.supplierorgid = @entityid))
and mps.buyingorgid = @entityid
and (@supplierorgid is null or mps.supplierorgid = @supplierorgid)
and mps.status = 'A'
)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 08:48:06

What do you get when you print @selectstr ?

Madhivanan

Failing to plan is Planning to fail

bsvnhll
Starting Member

6 Posts

Posted - 2009-04-13 : 10:21:10

I am getting NULL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 10:29:06

Use

select @selectstr =
"
select partno,partname,description,unitprice,currencyid,leadtime,createddate,
lastmodifieddate, supplierorgid,SupplierOrgName

from f_rpt_Parts_reports (" +convert(varchar(20),@entityid)+ "," + convert(varchar(20),@entitytype)+ "," + convert(varchar(20),coalesce(@supplierorgid,''))+",0,'us_english')"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources