function error (newbie) - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

function error (newbie)

Author

Topic

khencykurian
Starting Member

3 Posts

Posted - 2007-06-16 : 02:40:56

eh guys ma kind of newbie n tis ... k like wrote a function like

CREATE FUNCTION [dbo].[GetDetailText]
{
@PackingID int,
@PackingType int
}
RETURNS varchar(100)

AS

BEGIN

DECLARE @DetailText varchar(100)

IF (@PackingType = 1)
BEGIN

SET @DetailText = (SELECT
'Tickness' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.thickness) + '' + CONVERT(varchar, KF_Unit_3.Symbol)
+ ',' + 'Width' + ': ' + CONVERT(varchar, dbo.KF_PackMaterial.width) + '' + CONVERT(varchar, KF_Unit_1.Symbol)
+ ',' + 'InnerDia' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.india) + '' + CONVERT(varchar, KF_Unit_5.Symbol)
+ ',' + 'OuterDia' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.outdia) + '' + CONVERT(varchar, KF_Unit_6.Symbol)
+ ',' + 'Recycle' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.recycle) + ', ' + 'Shade' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.shade)
+ ',' + 'Weight' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.weight) + '' + CONVERT(varchar, KF_Unit_4.Symbol)
+ ',' + 'Perforation' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.perforation) + ' (' + CONVERT(varchar, dbo.KF_PackingMaterial.PackingTypeName)
+ ')'
FROM dbo.KF_CartonImageList RIGHT OUTER JOIN
dbo.KF_PackMaterial INNER JOIN
dbo.KF_PackType ON dbo.KF_PackMaterial.type = dbo.KF_PackType.PackID INNER JOIN
dbo.KF_Unit KF_Unit_1 ON dbo.KF_PackMaterial.widthunit = KF_Unit_1.UnitID INNER JOIN
dbo.KF_PackingMaterial ON dbo.KF_PackMaterial.packmaterialid = dbo.KF_PackingMaterial.Packingid LEFT OUTER JOIN
dbo.KF_Color ON dbo.KF_PackMaterial.ColorId = dbo.KF_Color.ColorId LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_6 ON dbo.KF_PackMaterial.outdiaunit = KF_Unit_6.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_5 ON dbo.KF_PackMaterial.indiaunit = KF_Unit_5.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_4 ON dbo.KF_PackMaterial.weightunit = KF_Unit_4.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_3 ON dbo.KF_PackMaterial.thicknessunit = KF_Unit_3.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_2 ON dbo.KF_PackMaterial.lengthunit = KF_Unit_2.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_7 ON dbo.KF_PackMaterial.heightunit = KF_Unit_7.UnitID ON
dbo.KF_CartonImageList.CDImageId = dbo.KF_PackMaterial.CDImageId
WHERE (dbo.KF_PackMaterial.id=@PackingID AND dbo.KF_PackMaterial.packmaterialid=@PackingType))
END

return @DetailText
END

buts its throwing syntax error like
"[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"

... ne idea ????????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 02:58:22

Do you want to generate query and return it?

Madhivanan

Failing to plan is Planning to fail

khencykurian
Starting Member

3 Posts

Posted - 2007-06-16 : 03:05:10

i just need a string ie combined from several fields as u can see, to use for another stored procedure...

for example the result i m expecting is
"Tickness:1micron,Width: 1micron,InnerDia:1m,OuterDia:1m,Recycle:1, Shade:mild yellow,Weight:1K.G,Perforation:1 (POLYETHYLENE)" as a single string

khencykurian
Starting Member

3 Posts

Posted - 2007-06-16 : 03:39:15

OK OK I GOT IT

SORRY Folks... it was just a silly mistake like
i used { } instead of () ...... so sorry.....

later guys

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources