How can i output XQuery's key words such like "<,>" when using XQuery in SQL Se

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints "&gt;" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate &gt; (as you noticed we do). If you load the XML document that contains &gt; into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'&gt;','>')

I used replace ... convert ... , thus replace all the "&gt;" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

sql

How can i output XQuery's key words such like "<,>" when using XQuery in SQ

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints "&gt;" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate &gt; (as you noticed we do). If you load the XML document that contains &gt; into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'&gt;','>')

I used replace ... convert ... , thus replace all the "&gt;" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

How can I insert the results from an EXEC command

Declare @.pTable varchar(30)
DECLARE @.TotRec integer
select @.pTable = 'Salaires'
SELECT @.TotRec EXEC ('SELECT Count(*) FROM ' + @.pTable)
GO
Print @.TotRec
How can I insert the results from an EXEC command?[QUOTE][SIZE=1]Originally posted by ericjean
Declare @.pTable varchar(30)
DECLARE @.TotRec integer
select @.pTable = 'Salaires'
SELECT @.TotRec = EXEC ('SELECT Count(*) FROM ' + @.pTable)
GO
Print @.TotRec

Like that is not correct!|||Unless you are just presenting a simplified example, there is no need to use dynamic sql to get rowcounts. You can look them up directly in the system tables (sysindexes where indid in (0, 1)):
-------------
set @.TotRec =
(select sysindexes.Rowcnt
from sysobjects
inner join sysindexes on sysobjects.id = sysindexes.id
where sysobjects.name = @.pTable and sysobjects.xtype = 'U' and sysindexes.indid in (0, 1)

Print @.TotRec
-------------

blindman|||Good idea!

Thank's|||I need to do like this

Declare @.pTable varchar(30)
DECLARE @.tmp integer
select @.pTable = 'Salaires'
SELECT @.tmp = exec('SELECT count(*) FROM ' + @.pTable + ' WHERE ID = 2)|||Declare @.pTable varchar(30)
DECLARE @.tmp integer
select @.pTable = 'Salaires'
SET @.tmp = ('SELECT count(*) FROM ' + @.pTable + ' WHERE ID = 2)

@.tmp now has the count of that table

how can I have this package execute in loop....I want to change every lines that are not c

declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')Without a problem description, including table DDL and sample data, a
meaningful response is not likely.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:eZTmg.15162$XT2.164998@.wagner.videotron.net...
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>

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 this function be working?

How can I get this function be working?

CREATE FUNCTION MyFunc

(

@.MyDate as datetime,

@.MyTableName varchar(50),

)

RETURNS TABLE

AS

RETURN

SELECT * FROM @.MyTableName Where myDate=@.MyDate

? You can't -- passing a table name dynamically would require dynamic SQL, and dynamic SQL is not supported in UDFs... Why do you want to pass a table name dynamically, anyway? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:d565e7f4-4b5a-4e7f-989d-3e6299fbda2c@.discussions.microsoft.com... How can I get this function be working? CREATE FUNCTION MyFunc ( @.MyDate as datetime, @.MyTableName varchar(50), ) RETURNS TABLE AS RETURN SELECT * FROM @.MyTableName Where myDate=@.MyDate|||

Ok. Thanks for the reply.

I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?

EXECUTE sp_executesql @.SQLSelectString

-- forward the result set in a table so that other procedures can read it.

|||? There are a few methods. I recommend that you read the following article: http://www.sommarskog.se/share_data.html -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:f9f7fbde-732a-43bf-a322-001a6f668f95@.discussions.microsoft.com... Ok. Thanks for the reply. I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it? EXECUTE sp_executesql @.SQLSelectString -- forward the result set in a table so that other procedures can read it.

How can I get this function be working?

How can I get this function be working?

CREATE FUNCTION MyFunc

(

@.MyDate as datetime,

@.MyTableName varchar(50),

)

RETURNS TABLE

AS

RETURN

SELECT * FROM @.MyTableName Where myDate=@.MyDate

Can you describeexactly what your problem is andexactly where you are trying to use the function. Are you getting error messages? If so, show them.

My guess is that the problem is you are trying to use a parameter as the table name. You cannot do that.

|||

Ok. Thanks for the reply.

I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?

EXECUTE sp_executesql @.SQLSelectString

-- forward the result set in a table so that other procedures can read it.

How can I get the @x?

I want to get the @.x's value:
declare
@.sql varchar(8000),
@.p1 varchar(100),
@.p2 varchar(100),
@.x int
select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
set @.sql='
select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
EXEC (@.sql)
print(@.x)
But the error message:
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@.x".
There are some conditions:must use exec;do not use temp table or procodure.
Any help would be appreciated.
luyan wrote:
> I want to get the @.x's value:
> declare
> @.sql varchar(8000),
> @.p1 varchar(100),
> @.p2 varchar(100),
> @.x int
> select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
> set @.sql='
> select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
> EXEC (@.sql)
> print(@.x)
> But the error message:
> Msg 137, Level 15, State 1, Line 2
> Must declare the scalar variable "@.x".
> There are some conditions:must use exec;do not use temp table or
> procodure. Any help would be appreciated.
Use sp_executesql instead. For example:
Declare @.Var1 int
Declare @.sql nvarchar(1000)
Declare @.x int
Set @.Var1 = 10
Set @.sql = N'Select @.x = count(*) From dbo.sysobjects Where id > @.Var1'
Exec sp_executesql @.sql, N'@.x int OUTPUT, @.Var1 int', @.x OUTPUT, @.Var1
Select @.x
David Gugick
Quest Software
www.quest.com

How can I get the @x?

I want to get the @.x's value:
declare
@.sql varchar(8000),
@.p1 varchar(100),
@.p2 varchar(100),
@.x int
select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
set @.sql='
select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
EXEC (@.sql)
print(@.x)
But the error message:
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@.x".
There are some conditions:must use exec;do not use temp table or procodure.
Any help would be appreciated.luyan wrote:
> I want to get the @.x's value:
> declare
> @.sql varchar(8000),
> @.p1 varchar(100),
> @.p2 varchar(100),
> @.x int
> select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
> set @.sql='
> select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
> EXEC (@.sql)
> print(@.x)
> But the error message:
> Msg 137, Level 15, State 1, Line 2
> Must declare the scalar variable "@.x".
> There are some conditions:must use exec;do not use temp table or
> procodure. Any help would be appreciated.
Use sp_executesql instead. For example:
Declare @.Var1 int
Declare @.sql nvarchar(1000)
Declare @.x int
Set @.Var1 = 10
Set @.sql = N'Select @.x = count(*) From dbo.sysobjects Where id > @.Var1'
Exec sp_executesql @.sql, N'@.x int OUTPUT, @.Var1 int', @.x OUTPUT, @.Var1
Select @.x
David Gugick
Quest Software
www.quest.com

How can I get the @x?

I want to get the @.x's value:
declare
@.sql varchar(8000),
@.p1 varchar(100),
@.p2 varchar(100),
@.x int
select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
set @.sql='
select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
EXEC (@.sql)
print(@.x)
But the error message:
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@.x".
There are some conditions:must use exec;do not use temp table or procodure.
Any help would be appreciated.luyan wrote:
> I want to get the @.x's value:
> declare
> @.sql varchar(8000),
> @.p1 varchar(100),
> @.p2 varchar(100),
> @.x int
> select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
> set @.sql='
> select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
> EXEC (@.sql)
> print(@.x)
> But the error message:
> Msg 137, Level 15, State 1, Line 2
> Must declare the scalar variable "@.x".
> There are some conditions:must use exec;do not use temp table or
> procodure. Any help would be appreciated.
Use sp_executesql instead. For example:
Declare @.Var1 int
Declare @.sql nvarchar(1000)
Declare @.x int
Set @.Var1 = 10
Set @.sql = N'Select @.x = count(*) From dbo.sysobjects Where id > @.Var1'
Exec sp_executesql @.sql, N'@.x int OUTPUT, @.Var1 int', @.x OUTPUT, @.Var1
Select @.x
David Gugick
Quest Software
www.quest.com

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;