July 30, 2012 at 8:27 am
I am trying to build my where section on the fly... going to place this inside of a stored proc that a report calls.. does anyone know how to format this so that it works?
declare @thisdate as datetime,
@stmt_top as varchar(500),
@stmt_where as varchar(1000),
@stmt_bottom as varchar(500),
@stmt_01 as varchar(2000)
set @thisdate = getdate()
set @stmt_top = 'Select Name, create_date , count (name) as Delivery_CNT
from (
SELECT Name, create_date
FROM sys.tables AS ztb_Carrier_Delivery_Metrics_1
'
set @stmt_Where = 'WHERE (NOT (NAME LIKE ' ')) and LTRIM(RTRIM (NAME)) like 'Estes Express' '
set @stmt_Bottom = '
) A
GROUP BY Name, create_date
Order by create_date '
set @stmt_01 = @stmt_top+@stmt_where+@stmt_bottom
print @stmt_01
exec @stmt_01
-- Note that I modified this to use sys.tables so we could use something that we all have in our database.. well Estes Express will need to be replaced with something that works. Note in the table that I am realy running off.. the data can have spaces before/after and also NULL is just a group of spaces... thus the strange start to the WHERE. Depending on how the report is run.. I will be adding a between statement for the date.. and other AND's to filter more things out.
July 30, 2012 at 8:35 am
You need double-quotes (i.e. 2 single quotes) for the strings:
set @stmt_Where = 'WHERE (NOT (NAME LIKE '' '')) and LTRIM(RTRIM (NAME)) like ''Estes Express'' '
July 30, 2012 at 8:36 am
Hmm that is even simplier than I thought it might be.... thanks.
July 30, 2012 at 8:41 am
Ok.. how do you get SQL to execute the statment I have built... the statement it is printing is correct, and runs if you copy and paste.. but this code errors out..
Msg 203, Level 16, State 2, Line 20
July 30, 2012 at 8:42 am
TRY EXEC (@SQL)
EXEC (@stmt_01) that is....
July 30, 2012 at 8:44 am
Yep that works, thanks. Wonder why it needs the brackets.. oh well, thanks for your help.
July 30, 2012 at 8:50 am
It's needed to tell EXEC it's a character string.
See this:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/bc806b71-cc55-470a-913e-c5f761d5c4b7.htm
both formats 2 & 3 need it - only format 1 doesn't....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply