How to make this work?

Question

2.00/5 (2 votes)

See more:

Currently I have started creating a procedure, which gets the parameters and should copy the contents of one table to another with certain conditions. I have copied the codes here. It compiles good. But nothing is affected.

ALTER PROCEDURE sp_ArchiveTable1(
@StartDate datetime,
@EndDate datetime,
@DateColumn VARCHAR(100),
@TableName VARCHAR(100),
@NewTableName VARCHAR(100)
)
AS
DECLARE @NextIDs TABLE(UniqueID int primary key)
 SELECT * INTO [@NewTableName] FROM [@TableName] 
WHERE [@DateColumn] >= @StartDate AND [@DateColumn] <= @EndDate

Posted 12-May-13 23:48pm

Gokulnath007

Add a Solution

Comments

Maciej Los 13-May-13 5:53am

What you mean: "nothing is affected"? Have you any error or something?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-05-12T23:56:00

Solution 2

Please, see this article: Building Dynamic SQL In a Stored Procedure[^]

Posted 12-May-13 23:56pm

Maciej Los

Comments

CHill60 13-May-13 5:57am

Beat me to it! My +5

Maciej Los 13-May-13 5:58am

Thank you, Chill ;)

_Amy · Answer 2 · 2013-05-13T00:01:00

Try this:

SQL

ALTER PROCEDURE sp_ArchiveTable1(
	@StartDate datetime,
	@EndDate datetime,
	@DateColumn VARCHAR(100),
	@TableName VARCHAR(100),
	@NewTableName VARCHAR(100)
)
AS
BEGIN
	DECLARE @NextIDs TABLE(UniqueID int primary key)
	DECLARE @statement nchar(1000) = N'SELECT * INTO '+@NewTableName+' FROM '+@TableName+' 
	WHERE '+@DateColumn+' BETWEEN '''+CONVERT(VARCHAR(100), @StartDate)+''' AND '''+CONVERT(VARCHAR(100), @EndDate)+''''
	PRINT @statement
	EXEC sys.sp_ExecuteSQL @statement
END
go

But you'll have to be careful about SQL injection[^].
See SQL Injection: What is it?[^]

--Amit

Mohammed Hameed · Answer 3 · 2013-05-12T23:52:00

Solution 1

I think you have to use exec(here your query) command for this to get executed.

Hope this helps...

Posted 12-May-13 23:52pm

Mohammed Hameed

Comments

Gokulnath007 13-May-13 5:53am

EXEC sp_ArchiveTable1 '2008-08-21 13:22:51.280','2009-07-01 19:53:08.153','CreatedDate','EmployeeMaster','EmployeeMaster2012'

I have used like this. But nothing happens

Mohammed Hameed 14-May-13 2:50am

You have to use exec inside the Stored procedure. Like exec(your select query).

How to make this work?

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us