declaring a temp table variable.

[techmaniac15]

This SP throws a syntax error on @players declaration. That doesn't appear to be the problem though, as debugging I've determined it's somewhere else in the code... BUT WHERE!??

ALTER procedure [dbo].[Golf_ModifyPlayers]
@paramID int,
@paramPlayerNames varchar(500)

AS
 
declare @spot int;
declare @str varchar(500);
declare @Players TABLE
	(
	pid int IDENTITY(1,1) PRIMARY KEY, 
	Player varchar(75),
	coID int
	)

BEGIN
	SELECT * INTO @Players FROM Golf_GetPlayers(@paramID)
END
BEGIN
	DELETE FROM gtPlayers WHERE gtID = @paramID
END
SET @paramPlayerNames = LEFT(@paramPlayerNames, LEN(@paramPlayerNames) - 1)
While @paramPlayerNames <> ''
      
      BEGIN 
            SET @spot = CHARINDEX(',', @paramPlayerNames) 
            IF @spot > 0 
                  BEGIN 
                        SET @str = LEFT(@paramPlayerNames, @spot - 1)
                        SET @paramPlayerNames = RIGHT(@paramPlayerNames, LEN(@paramPlayerNames) - @spot) 
                  END 
            ELSE 
                  BEGIN 
                        SET @str = @paramPlayerNames 
                        SET @paramPlayerNames = '' 
                  END
			 
			INSERT INTO gtPlayers (PlayerName, gtID) VALUES (@str, @paramID)
      END
	  if @@ERROR<>0 GOTO Error_Handling;
Error_Handling:
begin
            RAISERROR ('Transaction rolled back.', 16, 1);
            ROLLBACK TRAN;
end

 

[gmmastros]

SELECT * [!]INTO[/!] @Players FROM Golf_GetPlayers(@paramID)

Select Into is used to create a table and load it with data simultaneously. You cannot select into a table variable, you can only select into a real table or temp table.

Instead, use the Insert Into syntax, something like:

Insert Into @Players(Player) 
Select Player From Golf_GetPLayers(@paramID)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[techmaniac15]

Golf_GetPlayers is a stored procedure. I ended up just using a string select on the table instead. Thank you for the information. Just for my personal knowledge, if I'd made @Players #Players that would have made the original code work?

 

[gmmastros]

[tt][blue]I'd made @Players #Players that would have made the original code work? [/blue][/tt]

No. Because of the select into.

I thought Golf_GetPlayers was a user defined function. Now that I know it's a procedure...

Insert Into @Players(Player)
Exec Golf_GetPlayers(@paramID)

Note that Insert/Exec works with table variables starting with SQL2005 and newer. With SQL2000, you would have to use a temp table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom