y****9
发帖数: 144 | 1 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 | s**********0
发帖数: 266 | 2 declare @sqlcmd varchar(8000)
set @sqlcmd = 'declare @col_1 int; set @col_1 = 3; print @col_1'
exec (@sqlcmd) | y****9
发帖数: 144 | 3
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)
| i****a
发帖数: 36252 | 4 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.
| y****9
发帖数: 144 | 5
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
| y****9
发帖数: 144 | 6 The procedure source code can be found from my new blog post:
http://oracle-study-notes.blogspot.com/2011/09/sql-server-proce | g***l
发帖数: 18555 | |
|
