A Question on the multi selected, is the intention to have the @Item variable store the results of the selection by the user?

Ninja is correct about an Indexted Temp table...

Jnrstevej (10/7/2011)

---

@ Jason

When i try running the script i get the following error which i cant seem to fix

Msg 102, Level 15, State 1, Procedure Waterfall_version2, Line 22

Incorrect syntax...

Ninja's_RGR'us (10/6/2011)

---

Well it compiles on my system but obviously doesn't run without the tables.

Can you run the sp alone in ssms?

If yes, you can try dumping the dataset and redoing...

Sorry I've been quite busy with a some other work.

I seems like a lot of effort to get a distinct list of Items.

A couple of suggestions, dont use SELECT....INTO...

No problem. 😀

I take it you're allowing muliple selects in the SSRS, one thing to remember is that SSRS will send multiple items as a CSV string. eg 'A102,A103'.

So what you need...

Are you physically defining your temp table with a create or being lazy and doing a SELECT....INTO?

There are issues with SSRS (and SSIS) where by if you return data from...

You could use the INFORMATION_SCHEMA.COLUMNS

SELECT

'ALTER TABLE [' + Table_Schema+'].['+Table_Name

+'] Alter Column ['+Column_Name+'] varchar('

+Convert(nvarchar(5),Character_Maximum_Length)+');'

+ ' Update ['+ Table_Schema+'].['+Table_Name

...

I've never come across this syntax in 10 years of T-SQL programing, but its good to see it, and the explination for it.

I think 98% of people would written...

Vishal Singh (8/4/2011)

---

Hi Pals,

Not sure wwhatswrong with this query but whenever I am running this, I am getting all those rows with State='closed' which it should ignoring.

SELECT [ID],Priority,Title,[Created By],[Assigned To],...

Its something very similar yes, skcadavre, though i think you're missing an | in the last regex expression.

No problem Paula, I've been asked to do similar things in the...

Paula

you may have a problem with the way you are stripping the start of the postal code, especially those that only have a single letter and single digit at...

Stefan, I dont think CELKO was having a go at you more the Original Poster.

I can validate that based on the sameple data that you provided in the original post Chris's method works as intended.

The averages, for each row is 11.33,26.33,44.00,55.00.

I think the method with the SIGN functions should read :

(SIGN(ISNULL(Col1,0))+SIGN(ISNULL(Col2,0))+SIGN(ISNULL(Col3,0))+SIGN(ISNULL(Col4,0)))