Complex use of apostrophes

Hello,

could someone help with this query in a stored proc.?

SET @.SQL='SET '''+ @.avgwgt+''' = '

'(SELECT AVG(AverageWeight)

FROM CageFishHistory where CageID IN ('

+ @.cagearray+')

and ItemDate ='''

+CONVERT(varchar(23),@.startdate)+''')'

EXEC @.SQL

I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @.avgwgt line it works fine, but otherwise I'm getting this error:

"Incorrect syntax near '(SELECT AVG(AverageWeight)

FROM CageFishHistory where CageID IN ('."

It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.

please, try like this.

SELECT @.avgwgt=AVG(AverageWeight) FROM CageFishHistory where CageID IN ('+ @.cagearray+') and ItemDate ='''+CONVERT(varchar(23),@.startdate)+''')'

Regards,

Omer Kamal

www.friendspoint.de

|||

I tried this Omer,and got

Conversion failed when converting the varchar value ' + @.cagearray + ' to data type int.

|||

Seems to be working now with the Split() Function I found - I'm posting in case it will help others...

SELECT

@.avgwgt=AVG(AverageWeight)FROM CageFishHistorywhere CageIDIN(SELECT ItemFROM dbo.Split(@.cagearray,','))and ItemDate=''+CONVERT(varchar(23),@.startdate)

====================================================================

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

go

CREATE

FUNCTION [dbo].[Split]

(

@.ItemList

NVARCHAR(4000),

@.delimiter

CHAR(1)

)

RETURNS

@.IDTableTABLE(ItemVARCHAR(50))

AS

BEGIN

DECLARE @.tempItemListNVARCHAR(4000)SET @.tempItemList= @.ItemListDECLARE @.iINTDECLARE @.ItemNVARCHAR(4000)SET @.tempItemList=REPLACE(@.tempItemList,' ','')SET @.i=CHARINDEX(@.delimiter, @.tempItemList)WHILE(LEN(@.tempItemList)> 0)BEGINIF @.i= 0SET @.Item= @.tempItemListELSESET @.Item=LEFT(@.tempItemList, @.i- 1)INSERTINTO @.IDTable(Item)VALUES(@.Item)IF @.i= 0SET @.tempItemList=''ELSESET @.tempItemList=RIGHT(@.tempItemList,LEN(@.tempItemList)- @.i)SET @.i=CHARINDEX(@.delimiter, @.tempItemList)ENDRETURN

END