Or do you mean within the procedure?

drop PROCEDURE ttt_get_names

go

CREATE PROCEDURE ttt_get_names

@condition varchar(10)

AS

SET NOCOUNT ON

create table #A(col1 sysname, col2 int, col3 char(2))

IF @condition='test'

 BEGIN

  INSERT #A

  SELECT name, id, xtype

  FROM...

Do you mean by calling the procedure?  Then this will work:

drop PROCEDURE ttt_get_names

go

CREATE PROCEDURE ttt_get_names

@condition varchar(10)

AS

SET NOCOUNT ON

IF @condition='test'

 BEGIN

  SELECT name, id, xtype

  FROM sysobjects WHERE id=4

 END

ELSE IF @condition='test1'

 BEGIN

 ...

Try this due to a slight modification required when more with more dates:

declare @inventory table(ItemCode char(1), qty int)

insert @inventory values('A', 10)

declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases...

Try this.  You may want to use temporary tables (#change and #lowinv) so you can add some indexes on them to speed up the process with all the items.

declare @inventory...

The "FreeToSell" concept is difficult.  Here is the first part:

declare @inventory table(ItemCode char(1), qty int)

insert @inventory values('A', 10)

declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases int)

insert @purchase values...

Sorry, I missed the requirement for the matchid that includes the max-avg score.

Try this:

declare @ScoutingReport table (type int, scoutID int, matchid int, code char(1))

insert @ScoutingReport values(1, 11619, 1, 'A')

insert @ScoutingReport...

Yeah, take out the outer group by matchid:

declare @ScoutingReport table (type int, scoutID int, matchid int, code char(1))

insert @ScoutingReport values(1, 11619, 1, 'A')

insert @ScoutingReport values(1, 11619, 1, 'B')

insert @ScoutingReport values(1,...

Cameron,

Don't cross post your question to different forums.

You dont need to use 2 linked servers.  Try running this query on the support server:

INSERT INTO dbo.BLOCKED_PROCESSES

SELECT BLOCKED_DTTM, BLOCKED_SPID, BLOCKED_CONTEXT, BLOCKER_SPID, BLOCKER_CONTEXT, BLOCKER_STATUS, WAIT, DB, TABLE_NAME, INDEX_ID, LOCK_LEVEL, LOCK_REQUESTED,...

How are you connecting from the application?  What is the connection string?

Quickly looking a the query showed some poor practices:

You should use the old style join syntax (table1, table2 where table1.id = table2.id2).  You should never mix the two styles.

Use table...

Please simplify the query and post some sample data and desired output

My answers would be:

A stored procedure can't take a table datatype as an argument

Using for what query?  A table doesn't use an index, a query does.

The 2 virtual tables available...

You cannot.  Temporary tables have scope for the current procedure and any sub procedure so you can do something like the following:

create procedure proc2

as

  select sum(col1) from #a

go

create procedure proc1

as

create...

Try using a subquery like the following:

declare @ScoutingReport table (type int, scoutID int, matchid int, code char(1))

insert @ScoutingReport values(1, 11619, 1, 'A')

insert @ScoutingReport values(1, 11619, 1, 'B')

insert @ScoutingReport values(1, 11619,...