|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT @Variabe = Dynamic string HOW???CREATE FUNCTION dbo.CreateList (@Table varchar(50), @ListField varchar(50), @Query varchar(500)) RETURNS nvarchar(1000) AS BEGIN DECLARE @List nvarchar(1000) SELECT @List = COALESCE(@List + '', '', '''') + @ListField + ' FROM ' + @Table + ' WHERE ' + @Query + ' ORDER BY ' + @ListField RETURN @List END ------- All it is supposed to do is take the Table, ListField and the Query (where) and return a comma separated list. It will be used to create a comma separated list of roles that is returned as a column value in a resultset. The problem is that the above code fills the @List with the text "COALESCE..." instead of running a select. If I actually write it out with the full query and not variables and no + signs everything is fine and the function returns correctly So my question is, how do I create a dynamic string of SQL and then have it fill a variable from that string being executed? I've tried EXEC() but it doesn't work. Thanks for any help you can provide! James Hancock In order to accomplish this, you will have to use dynamic sql, but you can
not use "exec('...')" neither "sp_executesql" inside a user defined function. The Curse and Blessings of Dynamic SQL http://www.sommarskog.se/dynamic_sql.html May be using a stored procedure with an output parameter call help you to accomplish it. AMB Show quote "James Hancock" wrote: > Here's a user defined function I'm working on: > > CREATE FUNCTION dbo.CreateList (@Table varchar(50), @ListField varchar(50), > @Query varchar(500)) RETURNS nvarchar(1000) AS > BEGIN > DECLARE @List nvarchar(1000) > SELECT @List = COALESCE(@List + '', '', '''') + @ListField + ' FROM ' + > @Table + ' WHERE ' + @Query + ' ORDER BY ' + @ListField > RETURN @List > END > > ------- > All it is supposed to do is take the Table, ListField and the Query (where) > and return a comma separated list. > > It will be used to create a comma separated list of roles that is returned > as a column value in a resultset. > > The problem is that the above code fills the @List with the text > "COALESCE..." instead of running a select. > > If I actually write it out with the full query and not variables and no + > signs everything is fine and the function returns correctly > > So my question is, how do I create a dynamic string of SQL and then have it > fill a variable from that string being executed? I've tried EXEC() but it > doesn't work. > > Thanks for any help you can provide! > > James Hancock > > > Is there not a way to use the SELECT command with a constructed string????
Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:8A08884A-4D5D-40ED-B15B-EE8D91E75CC2@microsoft.com... > In order to accomplish this, you will have to use dynamic sql, but you can > not use "exec('...')" neither "sp_executesql" inside a user defined > function. > > The Curse and Blessings of Dynamic SQL > http://www.sommarskog.se/dynamic_sql.html > > May be using a stored procedure with an output parameter call help you to > accomplish it. > > > AMB > > "James Hancock" wrote: > >> Here's a user defined function I'm working on: >> >> CREATE FUNCTION dbo.CreateList (@Table varchar(50), @ListField >> varchar(50), >> @Query varchar(500)) RETURNS nvarchar(1000) AS >> BEGIN >> DECLARE @List nvarchar(1000) >> SELECT @List = COALESCE(@List + '', '', '''') + @ListField + ' FROM ' + >> @Table + ' WHERE ' + @Query + ' ORDER BY ' + @ListField >> RETURN @List >> END >> >> ------- >> All it is supposed to do is take the Table, ListField and the Query >> (where) >> and return a comma separated list. >> >> It will be used to create a comma separated list of roles that is >> returned >> as a column value in a resultset. >> >> The problem is that the above code fills the @List with the text >> "COALESCE..." instead of running a select. >> >> If I actually write it out with the full query and not variables and no + >> signs everything is fine and the function returns correctly >> >> So my question is, how do I create a dynamic string of SQL and then have >> it >> fill a variable from that string being executed? I've tried EXEC() but it >> doesn't work. >> >> Thanks for any help you can provide! >> >> James Hancock >> >> >> James,
> Is there not a way to use the SELECT command with a constructed string???? No, there is no way to do it as you want to,AMB Show quote "James Hancock" wrote: > Is there not a way to use the SELECT command with a constructed string???? > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > news:8A08884A-4D5D-40ED-B15B-EE8D91E75CC2@microsoft.com... > > In order to accomplish this, you will have to use dynamic sql, but you can > > not use "exec('...')" neither "sp_executesql" inside a user defined > > function. > > > > The Curse and Blessings of Dynamic SQL > > http://www.sommarskog.se/dynamic_sql.html > > > > May be using a stored procedure with an output parameter call help you to > > accomplish it. > > > > > > AMB > > > > "James Hancock" wrote: > > > >> Here's a user defined function I'm working on: > >> > >> CREATE FUNCTION dbo.CreateList (@Table varchar(50), @ListField > >> varchar(50), > >> @Query varchar(500)) RETURNS nvarchar(1000) AS > >> BEGIN > >> DECLARE @List nvarchar(1000) > >> SELECT @List = COALESCE(@List + '', '', '''') + @ListField + ' FROM ' + > >> @Table + ' WHERE ' + @Query + ' ORDER BY ' + @ListField > >> RETURN @List > >> END > >> > >> ------- > >> All it is supposed to do is take the Table, ListField and the Query > >> (where) > >> and return a comma separated list. > >> > >> It will be used to create a comma separated list of roles that is > >> returned > >> as a column value in a resultset. > >> > >> The problem is that the above code fills the @List with the text > >> "COALESCE..." instead of running a select. > >> > >> If I actually write it out with the full query and not variables and no + > >> signs everything is fine and the function returns correctly > >> > >> So my question is, how do I create a dynamic string of SQL and then have > >> it > >> fill a variable from that string being executed? I've tried EXEC() but it > >> doesn't work. > >> > >> Thanks for any help you can provide! > >> > >> James Hancock > >> > >> > >> > > > There's a saying in my country, roughly translated as "going around your ass
to get to the pocket". What exactly are you trying to achieve? ML Basically I have a need to list all of the roles of a person in a grid that
is databound in .net. I need all of the rolls in a comma separated list in one field returned in the result set (datatable). I was able to get a stored procedure to work and be generic (Yes I know I could easily do this if I just wrote the function and made it specific to the specific table, but I know I am going to have to use this in other places, and wanted to make it generic) but not a UDF. I can't get it so that the stored procedure runs specifically for each row in the resultset and returns as a column like I can with a UDF. Am I missing something? Thanks! Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:CA632AA9-4EE7-43CE-80F1-A8B715F7CA9F@microsoft.com... > There's a saying in my country, roughly translated as "going around your > ass > to get to the pocket". > > What exactly are you trying to achieve? > > > ML A procedure cannot be used in-line inside a query, other than inside an
INSERT...EXECUTE statement. What you need should really be done on the client (in the application layer), rather than doing it on the server. However, if you really cannot (or prefer not to) concatenate these values on the client, you'd need to use a function. Unfortunately, this cannot be a generic function - functions are not meant to support the same complexity as store procedures. Designing a special function to handle specific situations will prove much more efficient. Look at this: http://milambda.blogspot.com/2005/07/return-related-values-as-array.html I still urge you to consider concatenating results for presentation purposes on the client. ML Get the resultset to a procedural language and create the string in the
desired format. Methods to create concatenated strings with complicated routines as you seem to be doing often result in complex code and maintenance nightmares. -- Anith  |
|||||||||||||||||||||||
Other interesting topics