Thursday, March 29, 2012

cursor select and variables

I have problems to place my variable into the select statement.

DECLARE @.DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @.DB_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @.DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @.DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @.DB_Name in my select ?

the object against which you run a query cannot be a variable. You need use dynamic SQL by first constructing your SQL query as a string and then using the EXEC command or the sp_execute_sql system stored procedure. e.g.

declare @.sql VARCHAR(4000)

set @.sql = 'Select NAME, FILEDIRECTORY FROM ' + @.DB_NAME + '.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE ''%\\%'');'

EXEC (@.sql)

No comments:

Post a Comment