[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