insert into table var fails + create cursor for a query string 1

[calvinsinger]

Hello All,

My question has 2 parts. Please help if you can.

Help appreciated
Thanks
Calvin

Part 1
-------
SQL 2000 supports a new data type, table. However I am having trouble using it, when I use it as part of string,
which I execute.

for example

-declare @my_table TABLE (MYID int)
-set @querystring = 'insert into @my_table select MYID where ...)
-exec (@querystring) . This statement fails because of @my_table. it does not recognise the table variable.

where as if i directly do...

insert into @my_table values(1) - This works

One way to solve this problem is to use temporary table,
instead of table variable. But that does not serve my purpose.

Part 2
------
Is there a way one can use a exec with a cursor ? Eventually, I want to be able to create a cursor with a query string, instead of a select statement, but I am unable to do it.

for example we know one can do this..

declare my_crs cursor READ_ONLY for
select * from .....

But what if my select statement is a string. It has to be string because I use optional parametes, and build it dynamically, depending on what parameters are supplied
when the procedure is called

for example, my need is ..

declare @query_string varchar(100)
set @query_string = 'select * from Names where title =''' + @title + ''''

Now, having formed this @query_string, I would like to use this to create a cursor. But since I am using a @query_string, I have to use exec. And it does not appear that one can use a exec with a cursor

 

[rt63]

Part 1
The problem is the scope of the table variable. The table variable has been defined outside the execute statement. So it is not available when the statement is being executed through execute().

Part 2
You can declare a cursor using an execute of a string variable. The problem is that you will not be able to fetch values in variables through the execute statement (the same scope problem as in part 1). However, if your select column list is fixed and the only dynamic part is the selection criteria, then it can be handled.

e.g.

declare @sql varchar(1000) , @var1 <some datatype>
set @sql = 'declare xxxx cursor for select field1 from ... '
execute(@sql)
open xxxx
fetch xxxx into @var1
while @@fetch_status = 0
begin
<do something>
fetch xxxx into @user_cd
end
close xxxx
deallocate xxxx
RT

 

[calvinsinger]

Thank you, that worked quite well. One question.. If the cursor is declared within the exec statement (that is the declaration is part of the string which the exec executes),
how are we able to refer to the cursor outside the exec statement, that is, is the cursor's scope not local within the exec statement ? Must not be, must be global other wise I would not have been able to refer to it outside the exec.

I have another question as well. I am not looking for detailed answer, only want to be find out if its possible or not, is it recommended usage, and where can I find more information on it (what should I read in order to understand how to do it).

Question
---------
Can I call a existing Visual Basic function, part of .asp file, from transact sql stored procedure ? (say by dll or something) ?
Does it impact performance in -ve way compared to if I re-write the function in sql itself ?
where can I find more information on it (what should I read in order to understand how to do it).

Thank you
Calvin

 

[rt63]

I have not tried a dll but have invoked many exes using xp_cmdshell command. You can create an exe (say VB) and pass command line parameters as in DOS commands

declare @cmd
set @cmd = 'some_vb_exe parm1,parm2'
master..xp_cmdshell @cmd

This puts extra load on the server as it has to start a DOS shell and load exe etc. I feel that if you could port the function to SQL it will be better. Maybe someone has worked with the dll approach and will be able to respond better...
RT