I'm very new to stored procedures so this is probably something very dumb. I want to pass a name of a table to be created to my stored procedure. I'm using the variable @tableName in the code below but I'm getting an error:
Server: Msg 170, Level 15, State 1, Procedure usp_CodeGreyData, Line 22
Line 22: Incorrect syntax near '@tableName'.
Server: Msg 137, Level 15, State 1, Procedure usp_CodeGreyData, Line 29
Must declare the variable '@tableName'.
-
SET QUOTED_IDENTIFIER ON
-
GO
-
SET ANSI_NULLS ON
-
GO
-
-
-
/*
-
Name: usp_CodeGreyData
-
Description: This stored procedure is used to pull some data from the
-
codegreydiv table to display code grey diversion and status information.
-
Author: Doug Morand
-
Modification Log: Change
-
-
Description Date Changed By
-
Created procedure 11/10/2008 Doug Morand
-
*/
-
ALTER PROCEDURE usp_CodeGreyData
-
--declare variables to be passed
-
@startDate datetime, -- start date to search from
-
@endDate datetime, -- end date to search from
-
@tableName char --name of newly created table
-
AS
-
BEGIN
-
--declare local variables if any are needed
-
CREATE TABLE @tableName(
-
id int not null primary key
-
cgstatus varchar(50),
-
divstatus varchar(50),
-
date datetime,
-
time datetime
-
)
-
-
INSERT into @tableName(cgstatus,divstatus,date,time)
-
SELECT
-
case codegreystatus
-
when 0 then ''
-
when 1 then 'Active'
-
end AS cgstatus,
-
case diversionstatus
-
when 0 then ''
-
when 1 then 'Active'
-
end AS divstatus,
-
date,time
-
from tblCodeGreyDiv
-
where (date >= @startDate and date <= @endDate)
-
-
END
-
-
-
-
-
GO
-
SET QUOTED_IDENTIFIER OFF
-
GO
-
SET ANSI_NULLS ON
-
GO
-
-
-
