AW: AW: [dba-SQLServer]Is this possible?

Francisco H Tapia my.lists at verizon.net
Fri Feb 13 12:06:22 CST 2004


get rid of the cursor, why do you even have that?, dump the contents 
into a temp table and then select * from #tempTable for your cursor, 
it'll solve your headache.

-- 
-Francisco

Michael Brösdorf wrote:

> 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
>>
> 
> 
> 
> 





More information about the dba-SQLServer mailing list