SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Parameter as Column Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ZMike
Posting Yak Master

110 Posts

Posted - 03/15/2013 :  14:04:46  Show Profile  Reply with Quote
I have the following

DECLARE @DistinctSearch VARCHAR(MAX)
SET @DistinctSearch = (SELECT Search FROM #AC)

As an example @DistinctSearch coming from the table =
COALESCE([SQLQuery1] + ' ' , ' ') + COALESCE([SQLQuery2] + ' ' , ' ') + COALESCE([SQLQuery3] + ' ' , ' ')


SELECT
ReportID
, ReportSourceID
, ReportServer
, ReportName
, AltReportName
, ReportDescription
, AltReportDescription
, ReportPath
, FullPath
, Search = COALESCE(CAST([ReportID] AS VARCHAR (10)) + ' ' ,' ' )
+ COALESCE(CAST([ReportSourceID] AS VARCHAR(50))+ ' ' ,' ' )
+ COALESCE(CAST([ReportServer] AS VARCHAR(100)) + ' ' ,' ' )
+ COALESCE(CAST([ReportName] AS VARCHAR(200))+ ' ' ,' ' )
+ COALESCE(CAST([AltReportName] AS VARCHAR(200))+ ' ' ,' ' )
+ COALESCE(CAST([ReportDescription] AS VARCHAR(300))+ ' ' ,' ' )
+ COALESCE(CAST([AltReportDescription] AS VARCHAR(300))+ ' ' ,' ' )
+ COALESCE(CAST([ReportPath] AS VARCHAR(200))+ ' ' ,' ' )
+ COALESCE(CAST([FullPath] AS VARCHAR(300))+ ' ' ,' ' )
+ ' + '
+ @DistinctSearch

FROM A


It doesnt see the @DistinctSearch Properly. However if I copy and paste the results directly into the query it works. How to I make the query see it properly ?

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/15/2013 :  16:09:48  Show Profile  Reply with Quote
The only way you can execute a string of T-SQL is with dynamic sql.
So something like this:
exec('SELECT <all that stuff>' + @DistinctSearch + ' From A')

Be One with the Optimizer
TG

Edited by - TG on 03/15/2013 16:11:07
Go to Top of Page

ZMike
Posting Yak Master

110 Posts

Posted - 03/15/2013 :  16:28:11  Show Profile  Reply with Quote
TG, Thanks ! I was thinking the same but I didn't know if that was the best way. So I'll have to take the SELECT Statement and insert extra tick marks for the COALESCE statements correct ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000