Michael Brösdorf
michael.broesdorf at web.de
Sat Feb 14 04:18:23 CST 2004
Thanks to all who responded to my question - this list is great! I was trying to create generic procedure that compares the contents of two tables. The table names, the name of the ID field, a list of field names to be compared and the name of a result tables would have been the parameters of that procedure. Simply joining the two tables with all field is no option, since that fails when a field is NULL in both tables. That's why I tried to use a cursor to loop all records of table1 and compare each field from the fieldlist and write a record in the result table if the fields are different. Since there seems no way to convert a variable to an object name or use dynamic SQL in cursors this task would take way too long to implement. Is there something readily available that does that? 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 19:06 An: dba-sqlserver at databaseadvisors.com Betreff: Re: AW: AW: [dba-SQLServer]Is this possible? 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 >> > > > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com