[dba-SQLServer]Is this possible?

David McAfee DMcAfee at haascnc.com
Thu Feb 12 16:02:38 CST 2004


You can do it with dynamic SQL but its generally frowned upon as a possible security risk (you would have to trap the input parameters to prevent "--", "'" and other syntax) and not to mention that it is not compiled.

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

Declare @MySQL as VARCHAR(3000)

SET @MySQL = 'select ' + @MyField + ' from ' + @MyTable +' WHERE....'

EXEC (@MySQL)


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Michael
Brösdorf
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


More information about the dba-SQLServer mailing list