[dba-SQLServer]Is this possible?

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



More information about the dba-SQLServer mailing list