带有远程服务器,登录名和密码的存储过程 查询范例

@Database NVARCHAR(100) ,
@TableName NVARCHAR(100) = 'XX_Common_Data' , 
@Server NVARCHAR(100) = '' ,
@UserID NVARCHAR(100) = '' ,
@Password NVARCHAR(100) = '' 

DECLARE @execSQL 	NVARCHAR(4000)

SELECT @execSQL = 'INSERT INTO dbo.XX_Common_Data ([Emp_No], '
		+ '[A1], [B1], [C1], '
		+ '[D1], [E1], [F1], [G1], '
		+ '[H1], [I1], [J1], [K1], [L1])'

		+ '	SELECT                                                                   '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(40), ISNULL([Emp_No], '''')))),         '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([A2], '''')))),     '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([B2], '''')))),       '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([C2], '''')))), 	 '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(100), ISNULL([D2], '''')))),        '
		+ '		[E2],                                                        '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([F2], '''')))),      '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([G2], '''')))),       '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(40), ISNULL([H2], '''')))), '
		+ '		CASE 										'
		+ '			WHEN ISNULL([I2], 0) < 0	'
		+ '				THEN 0								'
		+ '			ELSE ISNULL([I2], 0)		'
		+ '		END	,										'
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(10), ISNULL([J2], '''')))), '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([K2], '''')))), '
		+ '		LTRIM(RTRIM(CONVERT(NVARCHAR(50), ISNULL([L2], ''''))))   '

IF @Server = ''
BEGIN
	SELECT @execSQL = @execSQL + '	FROM ' + @Database + '.dbo.' + @TableName + ' '
END
ELSE
BEGIN
	SELECT @execSQL = @execSQL + '	FROM OPENROWSET(''SQLOLEDB'', ''' + 
		@Server + '''; ''' + @UserID + '''; ''' + @Password + 
		''', ''SELECT * FROM ' + @Database + '.dbo.' + @TableName + ''') AS a      '
END

EXEC (@execSQL)