question

Smitha avatar image
Smitha asked

Dynamic Queries in SQL Server

When I declare a variable and set the database name to the variable and use the variable inside the dynamic query,I get the following error: Server: Msg 170, Level 15, State 1, Line 26 Line 26: Incorrect syntax near '.'.

Sample code:

	DECLARE @strQuery1 VARCHAR(8000)                    
	SET @strQuery1 = N'                    
	DECLARE @lstDatabaseName VARCHAR(1024)                    
	DECLARE @strDatabaseName VARCHAR(1024)                    
	DECLARE @intIndex INT                    
	SET @lstDatabaseName = ''db1,db2''                    
	IF (@lstDatabaseName IS NOT NULL AND @lstDatabaseName <> '''')                    
	BEGIN                    
		WHILE(LEN(@lstDatabaseName) > 0)                    
		BEGIN                    
                    
			SELECT @intIndex = CHARINDEX('','',@lstDatabaseName)                    
			IF (@intIndex > 0)                    
			BEGIN                    
				SELECT @strDatabaseName = LTRIM(RTRIM(LEFT(@lstDatabaseName, @intIndex - 1)))                    
				SELECT @lstDatabaseName = LTRIM(RTRIM(RIGHT(@lstDatabaseName, LEN(@lstDatabaseName) - @intIndex)))                    
				SELECT @intIndex = CHARINDEX('','', @lstDatabaseName)                    
			END                    
			ELSE                    
			BEGIN                    
				SET @strDatabaseName = @lstDatabaseName                    
				SET @lstDatabaseName = ''''                    
			END	                    
			PRINT @strDatabaseName                    
                    
			SELECT * FROM @strDatabaseName..table1                    
                    
		END                    
                    
	END'                    
	PRINT @strQuery1                    
	EXEC(@strQuery1)                    
dynamic
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nathan Skerl avatar image
Nathan Skerl answered

To accomplish you can use dynamic sql as in this first example:

declare @Process table (Id int identity(1,1), DatabaseName nvarchar(50))            
insert into @Process (DatabaseName)            
    select 'DB1' union all            
    select 'DB2'            
            
declare @i int, @DatabaseName nvarchar(50), @Cmd nvarchar(500)            
            
select @i = min(Id) from @Process            
            
while @i is not null            
begin            
    select	@Cmd = '',            
    		@DatabaseName = DatabaseName            
    from	@Process            
    where	Id = @i            
            
    set @Cmd = 'select * from ' + @DatabaseName + '.dbo.Table1'            
    print @Cmd            
            
    select @i = min(Id) from @Process where Id > @i            
end
Or, you can use an undomcumented feature like so:
            
sp_MSforeachdb @command1='if ''?'' in (''DB1'', ''DB2'') print ''select * from ?.dbo.Table1'''              
            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SmarterTech.Info avatar image
SmarterTech.Info answered

The fifth line from bottom is having incorrect syntax. You need to give username also after the db name.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.