y*9
2 楼
I have a need to declare variable dynamically, i.e. variable name and type
will be known at run time; I am trying to use sp_executesql. But the
following test failed:
use testdata
go
exec sp_executesql 'declare @col_1 int' --failed
-- declare @col_1 int -- this is ok
set @col_1=1
print @col_1
Any idea why it fails or any suggestions on how to do declaring variable
dynamically?
For example, I want to do:
declare @col_1 int
declare @col_2 varchar(10)
declare @col_3 varchar(100)
but the number of variable and type will be know at run time.
Thanks
will be known at run time; I am trying to use sp_executesql. But the
following test failed:
use testdata
go
exec sp_executesql 'declare @col_1 int' --failed
-- declare @col_1 int -- this is ok
set @col_1=1
print @col_1
Any idea why it fails or any suggestions on how to do declaring variable
dynamically?
For example, I want to do:
declare @col_1 int
declare @col_2 varchar(10)
declare @col_3 varchar(100)
but the number of variable and type will be know at run time.
Thanks
c*u
3 楼
最近读了几篇实证文章，发现这个模型用的很多，很想学习一下,感觉这个方法要
是掌握了，应该可以攒出篇文章来。看了一篇paper，这个模型应该是来自于统计中的
因子分析（Factor Analysis)，我理解就是假设这个不可观测到的因子有一个滞后项，
当然后来又有很多变形。
我主要迷惑是：这个模型跟Kalman filter，state-space model是什么关系？这两
个模型我只是大概知道一点，但感觉好像有关系（有篇文章里说是用Kalman filter方
法来估计这个dynamic factor model）。估计这种模型的常用软件是什么？
请高手指点，或者应该看哪些材料就可以更清楚？不盛感激！
是掌握了，应该可以攒出篇文章来。看了一篇paper，这个模型应该是来自于统计中的
因子分析（Factor Analysis)，我理解就是假设这个不可观测到的因子有一个滞后项，
当然后来又有很多变形。
我主要迷惑是：这个模型跟Kalman filter，state-space model是什么关系？这两
个模型我只是大概知道一点，但感觉好像有关系（有篇文章里说是用Kalman filter方
法来估计这个dynamic factor model）。估计这种模型的常用软件是什么？
请高手指点，或者应该看哪些材料就可以更清楚？不盛感激！
I*l
4 楼
bless
【在 c*****t 的大作中提到】
: 新的一个月,大家都要开开心心的哇
【在 c*****t 的大作中提到】
: 新的一个月,大家都要开开心心的哇
s*0
5 楼
declare @sqlcmd varchar(8000)
set @sqlcmd = 'declare @col_1 int; set @col_1 = 3; print @col_1'
exec (@sqlcmd)
set @sqlcmd = 'declare @col_1 int; set @col_1 = 3; print @col_1'
exec (@sqlcmd)
s*n
6 楼
很多变量是unobservable的，比如inflation target，建立state－space model可以用
kalman filter估计出来。这些unobserved variables可以写成dynamic factor model
的形式，比如F_t=Phi*F_t-1+e_t.
建议你去看Kim and Nelson的书（可以直接用google搜），这本书比较好懂，而且他们
的书上有gauss 的code。你如果熟悉matlab的话，用matlab直接写也是不错的选择。
kalman filter估计出来。这些unobserved variables可以写成dynamic factor model
的形式，比如F_t=Phi*F_t-1+e_t.
建议你去看Kim and Nelson的书（可以直接用google搜），这本书比较好懂，而且他们
的书上有gauss 的code。你如果熟悉matlab的话，用matlab直接写也是不错的选择。
c*t
7 楼
thanks a lot!!
【在 I*l 的大作中提到】
: bless
【在 I*l 的大作中提到】
: bless
y*9
8 楼
Thank you for your input, but it seems not what I want. basically I am doing:
fetch next from mycur
into @variable1,@variable2 ...
However I need to build the variable list dynamically.
The whole procedure looks like this:
1. pass a query text to a procedure (like: select * from sys.objects)
2. In this procedure I open a cursor for the query
3. I need to process this cursor row by row
4. for each row, i want to refer to each column to do sth on the column.
You can see the column names will be only known at run time. For this I can
get info about column name and type from sp_describe_cursor_columns
Now I stuck with step 3 as the fetch has to know the @variable. I build the
declare statements, but seems run it with sp_executesql does not help.
BTW, there is no application requirement. I just want to accomplish above
task as a way to learn T-SQL a little bit.
【在 s**********0 的大作中提到】
: declare @sqlcmd varchar(8000)
: set @sqlcmd = 'declare @col_1 int; set @col_1 = 3; print @col_1'
: exec (@sqlcmd)
l*0
9 楼
you too.
i*a
10 楼
LS has the correct info.
you need to construct your @sqlcmd fully before you execute.
do "print @sqlcmd", copy the result and execute it in new window
doing:
can
【在 y****9 的大作中提到】
:
: Thank you for your input, but it seems not what I want. basically I am doing:
: fetch next from mycur
: into @variable1,@variable2 ...
: However I need to build the variable list dynamically.
: The whole procedure looks like this:
: 1. pass a query text to a procedure (like: select * from sys.objects)
: 2. In this procedure I open a cursor for the query
: 3. I need to process this cursor row by row
: 4. for each row, i want to refer to each column to do sth on the column.
you need to construct your @sqlcmd fully before you execute.
do "print @sqlcmd", copy the result and execute it in new window
doing:
can
【在 y****9 的大作中提到】
:
: Thank you for your input, but it seems not what I want. basically I am doing:
: fetch next from mycur
: into @variable1,@variable2 ...
: However I need to build the variable list dynamically.
: The whole procedure looks like this:
: 1. pass a query text to a procedure (like: select * from sys.objects)
: 2. In this procedure I open a cursor for the query
: 3. I need to process this cursor row by row
: 4. for each row, i want to refer to each column to do sth on the column.
c*t
11 楼
re
【在 c*****t 的大作中提到】
: 新的一个月,大家都要开开心心的哇
【在 c*****t 的大作中提到】
: 新的一个月,大家都要开开心心的哇
y*9
12 楼
Thanks for the above hint, it helps. I am finally close to my goal. As an
Oracle DBA, I am used to use command line. What I try to do is to simulate
Tom kyte's print rows as colum procedure in Oracle.
I did two test cases, usp_p is my procedure which is to display rows as
column for an input sql.
1> exec usp_p @s='select top 5 id, clustered_data, scattered_data from big_
table'
2> go
----------------------------------------------
id: 1
clustered_data: 0
scattered_data: 0
----------------------------------------------
id: 2
clustered_data: 0
scattered_data: 1
----------------------------------------------
id: 3
clustered_data: 0
scattered_data: 2
----------------------------------------------
id: 4
clustered_data: 0
scattered_data: 3
----------------------------------------------
id: 5
clustered_data: 0
scattered_data: 4
----------------------------------------------
1> exec usp_p @s='select top 5 name, object_id, type_desc from sys.objects'
2> go
----------------------------------------------
name: sysrscols
object_id: 3
type_desc: S
----------------------------------------------
name: sysrowsets
object_id: 5
type_desc: S
----------------------------------------------
name: sysallocunits
object_id: 7
type_desc: S
----------------------------------------------
name: sysfiles1
object_id: 8
type_desc: S
----------------------------------------------
name: syspriorities
object_id: 17
type_desc: S
----------------------------------------------
The second test case show the type_desc is not displayed correctly, I will
try to fix later. Once I fix all the data type, I will publish the code in
my blog if anyone is insterested to look at the code.
【在 i****a 的大作中提到】
: LS has the correct info.
: you need to construct your @sqlcmd fully before you execute.
: do "print @sqlcmd", copy the result and execute it in new window
:
: doing:
: can
n*u
13 楼
bless
y*9
14 楼
The procedure source code can be found from my new blog post:
http://oracle-study-notes.blogspot.com/2011/09/sql-server-proce
http://oracle-study-notes.blogspot.com/2011/09/sql-server-proce
s*i
15 楼
bless
g*l
16 楼
这个就是DYNAMIC SQL吧
m*n
17 楼
re
【在 c*****t 的大作中提到】
: 新的一个月,大家都要开开心心的哇
【在 c*****t 的大作中提到】
: 新的一个月,大家都要开开心心的哇
相关阅读
湖南国企脱困首战告捷(fwd)[转载] 两千多亿人民币粮账被亏空!!!税收与隐形经济及其它[转载]“微软”举白旗的时候到了吗？奥妙的“贞观之治”[转载] 说一说朱一 颗 没 有 闲 下 来 的 心 (转录)对内开放是扩大内需的重要途径(fwd)[某些数字的可笑]秘书写的情书(郭振亚 韩继亮)欧元作为国际货币在未来的作用试谈互联网对企业管理的冲击容易模仿的分工模式与难以模仿的创新机制中国的资本主义初级阶段粮食问题上“泛政治化”认识误区[转载] 评论：财政赤字偏大还是偏小Jokes about Economists IVTop 10 reasons to study economics.增殖税与所得税官僚资本主义——Re: “政经联盟”与“官商经济”Jokes about Economists III.