[dba-SQLServer]Is this possible?

Michael, I doubt this is it, but when I execute a sql statement at the end of sproc that I have built dynamically in the sproc, I don't use the 'sp_executesql'.  My syntax looks like this:

Exec (@nvchSQL)

--Susan


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael Brösdorf
Sent: Friday, February 13, 2004 2:23 AM
To: dba-sqlserver at databaseadvisors.com
Subject: AW: [dba-SQLServer]Is this possible?


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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com