>>Hi
>>
>>If I have
>>
>>set @tablename = 'example_tablename'
>>
>>How do I select from that table
>>
>>ie select * from @tablename
>>
>>Thanks
>>
>>Nick
>
>I need to use dynamic SQL
>like
>declare @q varchar(1000)
>set @q = 'select * from ' + @tablename + ' where failure_reason is not NULL '
>exec (@q)

Look at creating a table function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION Z_CHECK_ACROSS_ROLES_PK_SK
(
	-- Add the parameters for the function here
	@MDM_BUSINESS_PARTNER_ID int
)
RETURNS 
	TABLE 
AS
	RETURN (
		SELECT
			CAST(A11_TEXT_F600.TextField AS nvarchar(15)                       AS CATEGORY,
			CAST( CAST(A2i_1_251_BP_ROLES.SubId AS float) AS int)     AS BUSINESS_PARTNER_ROLE_MDMINTERNALID

		FROM
			dbo.A2i_1 AS A2i_1_BPMain WITH (NOLOCK) 

			LEFT OUTER JOIN -- Category Flat Table join to Business Partner Main Table
			dbo.A2i_11 AS A2i_11_F606 WITH (NOLOCK) ON A2i_1_BPMain.F606 = A2i_11_F606.Id  

		WHERE
			CAST(CAST(A2i_1_BPMain.F48 AS float) AS int) = @MDM_BUSINESS_PARTNER_ID
	)
	
GO