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
>