SP:TableName to a procedure 2

[Mdavis123]

I need to pass a TableName to a SP so that I can build a cursor and traverse the given table.

MySp(@TblName varchar(30)) AS

Declare myCsr Cusor for
Select * from @TblName

....
Can this be done?
Thanks In advance
MikeD

 

[cmmrfrds]

all of the select statement needs to be combined and then executed dynamically. Something like.

declare @sql varchar(2000)
Set @sql = 'Select * from ' + @TblName
''include in cursor
EXEC ('DECLARE myCsr CURSOR FOR ' + @sql)

 

[Mdavis123]

Not sure why it worked, but it did.
I was expecting problems with the scope of myCsr.
Declaring the cursor within an Execute statement gives it the scope of the SP.

Thank You for the timely response.
Miked

 

[cmmrfrds]

Hi MikeD. FYI. On the issue of scope, if you start nesting stored procedures, then the cursor names must be different. If you called another stored procedure from the SP you are in and used the same cursor name it would give an error.