convert to string to execute ?

need some help converting this to a string which can be executed in a stored
procedure
========================================
========================
SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
FROM invoices, Projects
WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause>> need some help converting this to a string which can be executed in a
The code snippet you posted is not valid SQL. It is risky to use such
undocumented statements which relies on physical implementations that may
break down under various circumstances.
Anith|||what part is not valid :
example from here http://www.sqlteam.com/item.asp?ItemID=2368
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23KAM896aFHA.3840@.tk2msftngp13.phx.gbl...
> The code snippet you posted is not valid SQL. It is risky to use such
> undocumented statements which relies on physical implementations that may
> break down under various circumstances.
> --
> Anith
>|||>> what part is not valid : example from here
That seems like an old article and often people post such stuff here as
well.
First read the first sentence under the CAUSE section at:
http://support.microsoft.com/defaul...kb;en-us;287515
Also see (watch for wrapping):
http://groups-beta.google.com/group...d9?dmode=source
http://groups-beta.google.com/group...77?dmode=source
http://groups-beta.google.com/group...d7?dmode=source
Anith|||These all seem to relate to using "order by" or "group by" clauses ,neither
of which apply to this problem.
the problem I'm sing ot resolve is how to append a whereclause to a
string so it can be executed.
The code works fine if I place the where clause in statically, but returns
null if I try to use it in a dynamic format such as:
...
SET @.SQLString1 = 'SELECT ' + @.List + ' = COALESCE( ' + @.List + ' + '', '',
'''') + CAST(InvoiceID AS varchar(5))'
SET @.SQLString1 = @.SQLString1 + ' FROM invoices, Projects '
SET @.SQLString1 = @.SQLString1 + ' WHERE Projects.ProjectID =
Invoices.IDProject ' + @.WhereClause
SET @.S1 = CAST(@.SQLString1 as NVarchar(1000))
-- return results
-- ----
---
EXECUTE sp_executesql @.S1
return @.list|||This is a terrible idea. You are executing a query without looking at
it, and
if you let a user create the @.WhereClause variable, you are executing a
query
someone else wrote without looking at it. It's about as smart as a
self-serve
cash register nobody watches.
At the very least, one way to find out what this isn't working is to
actually
look at the query instead of executing it and puzzling over the error
message
or wrong results.
Usually that helps.
There are at least two reasons why
EXECUTE sp_executesql @.s1
will never modify the value of @.List, and you should notice at least one
of them: that the query you execute doesn't say @.List in it.
Steve Kass
Drew University
TJS wrote:

>These all seem to relate to using "order by" or "group by" clauses ,neither
>of which apply to this problem.
>
>the problem I'm sing ot resolve is how to append a whereclause to a
>string so it can be executed.
>The code works fine if I place the where clause in statically, but returns
>null if I try to use it in a dynamic format such as:
>...
>SET @.SQLString1 = 'SELECT ' + @.List + ' = COALESCE( ' + @.List + ' + '', '',
>'''') + CAST(InvoiceID AS varchar(5))'
>SET @.SQLString1 = @.SQLString1 + ' FROM invoices, Projects '
>SET @.SQLString1 = @.SQLString1 + ' WHERE Projects.ProjectID =
>Invoices.IDProject ' + @.WhereClause
>SET @.S1 = CAST(@.SQLString1 as NVarchar(1000))
>-- return results
>-- ---
---
>EXECUTE sp_executesql @.S1
>return @.list
>
>|||thanks for replying
Just trying to get a solution and haven't found any other way of getting to
the outcome
I know what values are going into the query, but there are a matrix of
options and I need to build a whereclause to return a correct set of comma
delimited values. When the string @.S1 is executed isn't @.list recognized as
being in there since it was created as part of the string ?
"Steve Kass" <skass@.drew.edu> wrote in message
news:u20DGl%23aFHA.3684@.TK2MSFTNGP12.phx.gbl...
> This is a terrible idea. You are executing a query without looking at it,
> and
> if you let a user create the @.WhereClause variable, you are executing a
> query
> someone else wrote without looking at it. It's about as smart as a
> self-serve
> cash register nobody watches.
> At the very least, one way to find out what this isn't working is to
> actually
> look at the query instead of executing it and puzzling over the error
> message
> or wrong results.
> Usually that helps.
> There are at least two reasons why
> EXECUTE sp_executesql @.s1
> will never modify the value of @.List, and you should notice at least one
> of them: that the query you execute doesn't say @.List in it.
> Steve Kass
> Drew University
> TJS wrote:
>|||
TJS wrote:

>thanks for replying
>Just trying to get a solution and haven't found any other way of getting to
>the outcome
>I know what values are going into the query, but there are a matrix of
>options and I need to build a whereclause to return a correct set of comma
>delimited values. When the string @.S1 is executed isn't @.list recognized a
s
>being in there since it was created as part of the string ?
>
>
No.
SK

>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u20DGl%23aFHA.3684@.TK2MSFTNGP12.phx.gbl...
>
>
>