Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Parameter as Column Data

Author  Topic 

ZMike
Posting Yak Master

110 Posts

Posted - 2013-03-15 : 14:04:46
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-15 : 16:09:48
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
Go to Top of Page

ZMike
Posting Yak Master

110 Posts

Posted - 2013-03-15 : 16:28:11
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
   

- Advertisement -