Query View Spec...

• Sahasam

  Ten Centuries

  Points: 1001

  More actions

  October 4, 2010 at 11:52 am

  #224705

  Dynamic SQL Stored Proc:

  create procedure dbo.USP_USR_GETATTRIBUTEVALUE

  (

  @AttributeName nvarchar(200),

  @ID uniqueidentifier,

  @AttributeValue nvarchar(200) output

  )

  AS

  BEGIN

  Declare @ATTRIBUTEVIEWNAME nvarchar(200)

  Declare @sql nvarchar(4000)

  set @ATTRIBUTEVIEWNAME = (select 'V_QUERY_ATTRIBUTE'+ convert(varchar(50),REPLACE(AC.ID,'-','')) from ATTRIBUTECATEGORY AC where AC.NAME=@AttributeName)

  SET @sql='SET @ATTRIBUTEVALUE = (SELECT TOP 1 VALUE FROM '+@ATTRIBUTEVIEWNAME+' WHERE PARENTID = @ID ORDER BY DATEADDED DESC)'

  exec sp_executesql @sql,N'@ID uniqueidentifier,@ATTRIBUTEVALUE nvarchar(200) output',@ID ,@ATTRIBUTEVALUE output

  END

  View:

  Declare @NLDHLPostalNumbersAttrValue nvarchar(100)=''

  Declare @NLTNTPostalNumbersTypeAttrValue nvarchar(100)=''

  exec dbo.USP_USR_GETATTRIBUTEVALUE 'NL DHL Postal Numbers',@CONSTITUENTID,@NLDHLPostalNumbersAttrValue output

  exec dbo.USP_USR_GETATTRIBUTEVALUE 'NL TNT Postal Numbers',@CONSTITUENTID,@NLTNTPostalNumbersTypeAttrValue output

  SELECT DISTINCT C.ID AS 'CONSTITUENTID',

  @NLDHLPostalNumbersAttrValue as [NL DHL Postal Numbers],

  @NLTNTPostalNumbersTypeAttrValue as [NL TNT Postal Numbers]

  FROM CONSTITUENT C

  --WHERE C.ID = @CONSTITUENTID

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 5, 2010 at 3:11 am

  #1231533

  Is there a question? :unsure:

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Nevyn

  SSChampion

  Points: 14210

  More actions

  October 5, 2010 at 6:40 am

  #1231632

  LutzM (10/5/2010)

  ---

  Is there a question? :unsure:

  Looks like he put the question in the subtitle (I missed it the first time too)

  Here it is:

  I want to use the values executed by a dynamic sql in a view. Can this be possible? Can you please look at my dynamic sql stored procedure as well as sql server Query View below. Help me with the actual query view?