I forgot the parameter passing bit, so, in case it wasn't obvious...

create proc test1 @v-2 varchar(20) as

select 1, 2, @v-2 + ' world'

go

create proc test2...

Here's an example...

create proc test1 as

select 1, 2

go

create proc test2 as

select 3, 4

go

create proc test3 as

create table #t (i int, j...

If you do prefer to use sql to do you transformation, you can do something like this...

--data (create using dts, bcp or bulk insert)

declare @StagingTable table...

If calling this stored procedure from query analyser is working fine for you, but not from your asp.net - you probably need to post your problem to an asp.net forum.

We...

If I understand you correctly (and I can't believe I do), aren't you telling us that the procedure is working fine, but also asking for a modification to it?

Here's an alternative approach (for fun )...

--data

CREATE TABLE #TempControls(CtrlCode varchar(10))

INSERT #TempControls

SELECT 'TT4' UNION ALL

SELECT 'T1' UNION ALL

SELECT 'T4' UNION...

Just a word of warning on this approach from the 'SQL Server Query Optimization Team'...

http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

No, you can't do this. There is nothing wrong with the described use of a table variable, so don't worry about it.

Table variables are just like any other local variable, and...

Okay - There's only 1 model database for each instance of sql server, so it sounds like it's not appropriate for you in this case. It was worth a try, anyway......

Matt,

I assume you know about the model database, and it's not appropriate for your situation?

Well, just in case - from BOL...

System Databases and Data

model

The model database...

It's not terribly well written, but here's an article which explains how to do this programatically. The title should be 'Creating a "CREATE TABLE" script from a stored procedure", but...

CK Bhatia - If you feel the need for speed, you should use this query instead:

select s2,

 sum(case when s1 = 1 then crit else 0 end)...

This is a FAQ, and here are the usual references...

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

http://www.sql-server-performance.com/mm_list_random_values.asp

Maybe they just don't know any better!

Nice to get an insight into someone's life. Many thanks!