Database

select 'select [name], [year], [date] from '+table_schema+'.'
       +table_name+' union all '
       from INFORMATION_SCHEMA.TABLES

select 'create view MyHelperView as '
union all
select 'select [name], [year], [date], '
       +''''+table_name+''' as tablename from '+table_schema+'.'
       +table_name+' union all '
       from INFORMATION_SCHEMA.TABLES

select tablename, name from MyHelperView where date = ...

INSERT INTO @tblTemp
(
    ContactTypeCodeFK,
    ContactTypeDesc,
    PartFK
)
SELECT ContactTypeCodeFK,
ContactTypeDesc,
PartFK, 
PartMaster.PartDesc
FROM tbl_Correspondence CORES
INNER JOIN tbl_PartCode 
ON CORES.PartCodeFK = tbl_PartCode.PartCodePK
WHERE CORES.ProjectFK = @pProjectPK

kalyan_vb wrote:

"'+@pProjectPK+'")'

declare @test table ( MyId int )
insert into @test values (5)

declare @stmt varchar(128)
set @stmt = 'insert into @test values (6)'
exec( @stmt )

CREATE FUNCTION GetPartNames (@nCorrCode int)  
RETURNS  varchar(1000) AS  
BEGIN 

DECLARE @cPartNames varchar(1000)
DECLARE @cPartName varchar(50)

SELECT @cPartNames = ''

DECLARE  partcursor CURSOR FOR 
SELECT DISTINCT PartName 
FROM table2
WHERE correspondentcodefk = @nCorrCode

OPEN partcursor
FETCH NEXT FROM partcursor INTO @cPartName 
WHILE @@FETCH_STATUS = 0
BEGIN
	select @cPartNames = @cPartNames + @cPartName
	FETCH NEXT FROM partcursor INTO @cPartName 
END

CLOSE partcursor
DEALLOCATE partcursor
RETURN(@cPartNames)
END

SELECT correspondentcodepk, contactcode, dbo.GetPartNames(correspondentcodepk) AS PartNames FROM table1

SELECT * 
FROM localservername.databasename.dbo.localtablename AS local
INNER JOIN remoteservername.databasename.dbo.remotetablename AS remote
ON local.pk = remote.fk