How can i get XML using a Dynamic Query ?

i have a function like this, but i alwasys says wrong:

alter function GROUP_CONCAT(@.tableName varchar(100),@.groupByColumn varchar(100),@.targetColumn varchar(100),@.targetValue varchar(100))

returns varchar(1000)

as

begin

declare @.back varchar(1000)

declare @.sql varchar(1000)

set @.sql = 'select '+@.targetColumn+' from ' + @.tableName + ' where '+@.groupByColumn + ' = '''+@.targetValue + ''' for xml auto, root(''root'')'

declare @.x xml

set @.x = exec(@.sql)

return @.sql

end

I want to generate XML using a exec(...) , that is a Dynamic Query, but SQL Server 2005 always say something wrong with exec(....)

How can i fix it? Thanks

The reason is that you cannot use exec() as an expression. It is a statement that cannot be assigned.

If you just want to return it over TDS, do not assign it to a variable, otherwise assign it to a temp table inside the constructed node and then retrieve it from there.

Also, if you assign the FOR XML to a column or variable of type XML, it is better to add the type directive to the FOR XML as in FOR XML PATH, TYPE, ROOT('root'). That avoids unnecessary parsing.

Best regards

Michael

How can I get a table reference knowing his name inside a system f

I want to call the system function
DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
parameter a @.TableName varchar variable represanting the table's name.
DBCC CHECKIDENT( ) requires as input the table object, while I have the
table string name.
Is there any way that I can get the table object using its string name
inside the DBCC CHECKIDENT() function ?
example
Create procedure procName (@.TableName varchar)
begin
.......
DBCC CHECKIDENT( theTableObject , RESEED, 100)
......
end;Aigiris, Try using the OBJECT_ID function. From the BOL below. - RLF
OBJECT_ID
Returns the database object identification number.
Syntax
OBJECT_ID ( 'object' )
"Argiris Petromelidis" <Argiris Petromelidis@.discussions.microsoft.com>
wrote in message news:AC4CE899-E310-47C3-9101-B12779C2FDA8@.microsoft.com...
>I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @.TableName varchar variable represanting the table's name.
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
> example
> Create procedure procName (@.TableName varchar)
> begin
> ........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ......
> end;|||Hi
You may want to try using dynamic SQL e.g.
CREATE PROCEDURE MyCheck ( @.objectname sysname )
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@.objectname)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
AND EXISTS ( SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(@.objectname)
AND COLUMNPROPERTY(id,name,'IsIdentity') = 1)
BEGIN
DECLARE @.cmd varchar(8000)
SET @.cmd = 'DBCC CHECKIDENT( ''' + QUOTENAME(@.objectname) + ''', RESEED,
100)'
EXEC (@.cmd)
END
John
"Argiris Petromelidis" wrote:

> I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @.TableName varchar variable represanting the table's name.
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
> example
> Create procedure procName (@.TableName varchar)
> begin
> ........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ......
> end;