July 28, 2010 at 4:47 pm
I have a stored procedure that accepts one parameter. I want to create a table using static text, the parameter value and the current date. I can create the table name value in a SYSNAME data type but when I try to use the declared variable, @TableName, in the SELECT INTO statement it errors off. What do I need to do to reference the dynamic table name? Partial code below
ALTER PROCEDURE [dbo].[procPros2ActiveProsCasesBySpecificCounty]
-- Last three numbers in County FIP, 53 for WA not needed
@pCountyFIP char(3) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TableName SYSNAME
SET @TableName = 'ProsActiveCasesFor53'
+ @pCountyFIP
+ 'For'
+ convert(varchar(2),month(getdate()))
+ '/'
+ convert(varchar(2),day(getdate()))
+ '/'
+ convert(varchar(4),year(getdate()))
-- Insert statements for procedure here
SELECT CASE_NO
INTO @TableName
FROM SEHC
WHERE FIP = @pCountyFIP
END
July 28, 2010 at 5:14 pm
You would need to use dynamic SQL.
Something like:
DECLARE @sql VARCHAR(500)
-- Insert statements for procedure here
SET @sql=
'SELECT CASE_NO
INTO [' +@TableName + ']
FROM SEHC
WHERE FIP = ' + @pCountyFIP +'''
EXEC(@sql)
Side note: do you really have a table [ProsActiveCasesFor53100For 7/29/2010]??? :pinch:
I strongly recommend to rethink your table name convention as well as your database design in general if you really have one table per DAY per Count... I'm scared...
July 30, 2010 at 8:47 am
Perfect, got what I needed. As far as your concerns these are ad hoc data requests with the user needing the date in the table name. These are temporary tables that are shipped out and deleted. Am in the process of building the front end that will allow the user to access data in real time.
Once again thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy