Non Dynamic option?:

User Defined Function

Create a UDF to take in a string list of ID parameters and cycle through them, adding them to a table variable before returning that table....

Alternative:

declare @a int

declare @b-2 int

set @a = (select Max(Col_Id) from Table)

set @b-2 = (select cast(rand()*@a as int))

select * from table where col_id=@b

...

I have found that if you declare a table variable, and then try to use it in a dynamic piece of SQL later on, it will return a 'must declare...

You may find you have a problem using table variables in dynamic sql. I did try this but found a temp table the only option. Table variables have several limitations,...

-- Columns into Rows:

-- Conditions:

-- will need to read from Table with standard column headers

-- Will need to specify which RowId to read

-- Will need to specify number of columns

--...

Hi

Thanks for the quick response.

The SP does not contain Dynamic SQL.

Following on from your comments, a bit more investigation revealed the issue, it was connected to Database ownership.

The SP's are...

Thanks

I had never heard of it, but it seemed like something that might have been possible, as you would have thought that having a variable column header name would not...