AW: [dba-SQLServer]Is this possible?

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