AW: [dba-SQLServer]Is this possible?

Then your best option is to create temporary table, load the data into it, then populate your cursor with the data from the temp table.  A potential problem with this is knowing for certain what the data type of @MyField is.  Therefore, you can do a lookup on the system tables to get the data type information.  Another problem I ran into was that it wouldn't allow me to create #MyTable, it must go out of scope or something.  Someone else here can probably explain that.  So I had to create and drop a local table MyTable.

Create procedure MyProc @MyTable nvarchar(255), @MyField nvarchar(255) 
AS

Declare @MyVar as int
Declare @sql as varchar(4000)
Declare @datatype as varchar(50)

set @datatype = (select st.name + '(' + cast(sc.length as varchar(10)) + ')'
						from sysobjects so inner join syscolumns sc on so.id = sc.id
							inner join systypes st on sc.xtype = st.xtype
						where so.name = @MyTable and sc.name = @MyField)
print @datatype

SET @sql = 'CREATE TABLE MyTable (MyField ' + @datatype + ')'
	print @sql
	EXEC (@sql)

SET @sql = 'SELECT ' + @MyField + ' FROM ' + @MyTable
	
INSERT INTO MyTable(MyField)
	EXEC (@sql)

Declare MyCursor cursor for
	SELECT * FROM MyTable

open MyCursor

Fetch next from MyCursor into @MyVar

While (@@Fetch_status=0)
begin
	...
	Print @MyVar
	...
	Fetch next from MyCursor into @MyVar
end

deallocate MyCursor
drop table MyTable



Ken Stoker
Technology Commercialization
Information Systems Administrator
PH: (509) 375-3758
FAX: (509) 375-6731
E-mail:  Kenneth.Stoker at pnl.gov 


-----Original Message-----
From: Michael Brösdorf [mailto:michael.broesdorf at web.de] 
Sent: Friday, February 13, 2004 8:56 AM
To: dba-sqlserver at databaseadvisors.com
Subject: AW: AW: [dba-SQLServer]Is this possible?


Hi,

the sproc will be called from a scheduled job on the server for certain maintenance tasks, so there is no user interaction at all.

Replacing sp_ExecuteSql with Exec (@nvchSQL) didn't help either - same error message!

Michael

-----Ursprüngliche Nachricht-----
Von: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von Francisco H Tapia
Gesendet: Freitag, 13. Februar 2004 17:22
An: dba-sqlserver at databaseadvisors.com
Betreff: Re: AW: [dba-SQLServer]Is this possible?


Another reason for why dynamic sql is frowned upon is that if "endUsers" from some application will be running the sproc, then you'll need to assign select rights to the "table" instead of just execute rights on the sproc.

and as susan mentioned in another message, drop the sp_ExecuteSQL


--
-Francisco

Michael Brösdorf wrote:
> This is exactly what I tried:
>
> ...
> set @nvchSQL='SELECT ' + @nvchIDField + 'AS MyID FROM ' + @nvchT1
>
>
> Declare curT1 cursor for
> exec sp_ExecuteSQL @nvchSQL
>
> ...
>
>
> Unfortunately it comes up with this error message:
>
> Server: Msg 156, Level 15, State 1, Line 15
> Incorrect syntax near the keyword 'exec'.
>
>
> It would certainly be possible to put the whole thing in a 
> sp_ExecuteSQL, but I would like to avoid that if there is any other 
> way...
>
> Michael
>
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von 
> Stoker, Kenneth E
> Gesendet: Freitag, 13. Februar 2004 00:40
> An: dba-sqlserver at databaseadvisors.com
> Betreff: RE: [dba-SQLServer]Is this possible?
>
>
> I ran into this problem a couple months ago and found this statement 
> right from BOL:
>
> 	"Variables can be used only in expressions, not in place of object 
> names
or
> keywords."
>
> Using what I did then, you could do something like this (not tested):
>
>
> Create procedure MyProc @MyTable nvarchar(255), @MyField nvarchar(255) 
> AS
>
> Declare @MyVar as int
> Declare @sql as varchar(4000)
>
> SET @sql = 'SELECT ' + @MyField + ' FROM ' + @MyTable
>
> Declare MyCursor cursor for
> Exec sp_execsql @sql
>
> Fetch next from MyCursor into @MyVar
>
> While @@Fetchstatus=0
> begin
> 	...
> 	Print @MyVar
> 	...
> end
>
> Hope something like this works/helps you.
>
>
> Ken Stoker
> Technology Commercialization
> Information Systems Administrator
> PH: (509) 375-3758
> FAX: (509) 375-6731
> E-mail:  Kenneth.Stoker at pnl.gov
>
>
> -----Original Message-----
> From: Michael Brösdorf [mailto:michael.broesdorf at web.de]
> Sent: Thursday, February 12, 2004 1:43 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer]Is this possible?
>
>
> Hi,
>
> i would like to pass a table name and a field name to a stored 
> procedure. The stored procedure has to create a cursor that fetches 
> all records from the table and performs certain operations based on 
> the content of the specified field.
>
> Something like this:
>
> Create procedure MyProc @MyTable nvarchar(255), @MyField nvarchar(255) 
> AS
>
> Declare @MyVar as int
>
> Declare MyCursor cursor for
> select <@MyField> from <@MyTable>
>
> Fetch next from MyCursor into @MyVar
>
> While @@Fetchstatus=0
> begin
> 	...
> 	Print @MyVar
> 	...
> end
>
>
> The problem is of course that I cannot simply put the variables 
> containg table and field name into the select statement of the cursor.
>
> Is there any way this can be done?
>
> Michael
>



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com