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.
Author |
Topic |
ZMike
Posting Yak Master
110 Posts |
Posted - 2013-03-15 : 14:04:46
|
I have the followingDECLARE @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))+ ' ' ,' ' ) + ' + ' + @DistinctSearchFROM AIt 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 OptimizerTG |
|
|
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 ? |
|
|
|
|
|
|
|