[dba-SQLServer] Reference table in different db

Hello John,

I can only answer half this query.

*FIRST HALF*

You need to build a string, which you can this execute.

For example, you can build

delcare @SQLtoExexcute nvarchar(2000)
Set @SQLtoExexcute = 'Update Customers Set Limit = 100'
Exec (@SQLtoExexcute)    -- Check the exact syntax of this.

As you can see, I have just dynamically created and ran an sql statement.

So here is another

Drop usp_UpdateCustomerLimits -- you can change this to check for its
existance first if you like
Delcare @SQLtoExexcute nvarchar(2000)
Set @SQLtoExexcute = 'Create Proc usp_UpdateCustomerLimits as Update
Customers Set Limit = 100'
Exec (@SQLtoExexcute)    -- Check the exact syntax of this.
-- Now you have a dynamically created sproc

Exec usp_UpdateCustomerLimits

so once you have the concept of programatically creating an sql string you
have the two options of
a) build the string within my sproc and execute it
b) actually drop and create a sproc dynamically and run it each time as if
it were a static sproc

I know that the text in the cursor you want to work with is longer than my
examples but it should work the same.


*SECOND HALF*
I do not know whether  you can use just a small piece of what I must
outlined above to create the cursor only.  IOW, if you can dynamically just
build one line which is the like that creates the cursor, it will save you
creating the entire sproc, but I cannot say as I have never done that.  If
you get that bit working, let us know.

Is that any help?

thanks

Mark




On 1 April 2010 14:02, jwcolby <jwcolby at colbyconsulting.com> wrote:

> Guys,
>
> I am building dynamic SQL in a stored procedure.  I pass in the name of the
> database, table etc.
>
> This is what I currently do:
>
>        Declare FieldCursor CURSOR FAST_FORWARD FOR
>        SELECT * FROM tblCriteriaFieldData
>        WHERE maxlength is not Null
>
> This is what I need to do:
>
>        Declare FieldCursor CURSOR FAST_FORWARD FOR
>        SELECT * FROM @SomeDb.tblCriteriaFieldData
>        WHERE maxlength is not Null
>
> Where @SomeDB is a passed in database name.  Obviously this syntax is not
> correct.
>
> I tried to declare an @SQL statement and then build up the select into that
> @SQL and then use:
>
>        Declare FieldCursor CURSOR FAST_FORWARD FOR
>        @SQL
>
> but that doesn't work either.
>
> So I need to know how to open a cursor for a table in another database
> where the database name is
> not hard coded.
>
> Any assistance is greatly appreciated.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>