passing date to dynamic sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

passing date to dynamic sql

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-06-24 : 15:10:40

I am getting runtime error for the below simple execution of sp
even I tried casting the @dt to varchar still getting the same error.
I want to delete the table based on the 2 parameter
1. Table Name first parameter
2. InCondition is the column name which of type datetime


CREATE PROCEDURE dbo.[DeleteTable](@InTblName NVARCHAR(250),@InCondition NVARCHAR(250))
AS
BEGIN
DECLARE @DeleteSQL NVARCHAR(250)
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
SELECT @DeleteSQL = N'DELETE  FROM ' + @InTblName +' WHERE '+@InCondition+ '=''' + @Dt+ ''''
SELECT(@DeleteSQL)
EXECUTE sp_executesql  @DeleteSQL
END

I have corrected the code now, I am not getting the output it is throwing error at run time.

EXEC [DeleteTable] 'TABLE_NAME','COLUMN_NAME'

Where column_name is of datatype datetime

Msg 241, Level 16, State 1, Procedure spDeleteTable, Line 8
Conversion failed when converting date and/or time from character string.

Thanks

-Neil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 15:16:37

Please show us the output of PRINT @InputTblName so that we can help.

By the way, this is a very, very, very bad idea. Please read through this and ensure you understand it: http://www.sommarskog.se/dynamic_sql.html. Once you understand it, drop that stored procedure and figure out an alternative. There really is no point to using stored procedures if you are going to write them like this. It'd be better to just use parameterized queries inside your application instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 15:33:44

Totally agree with Tara. But, if you insist on doing this, my hint is that everything needs to be a string in order to concatenate them together (CAST/CONVERT).

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-06-24 : 15:57:38

my apology Tara & Lamprey I've corrected the code now.. and posted the error message as well

-Neil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 15:59:39

As Lamprey mentioned, you'll need to CAST/CONVERT the @Dt variable. But please stop the insanity now and drop this stored procedure!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-06-24 : 16:09:57

I tried casting it to varchar but still same error at runtime.

[CODE]

ALTER PROCEDURE dbo.[DeleteTable](@InTblName NVARCHAR(250),@InCondition NVARCHAR(250))
AS
BEGIN
DECLARE @DeleteSQL NVARCHAR(250)
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
SELECT @Dt= CAST(@Dt AS VARCHAR)
SELECT @DeleteSQL = N'DELETE FROM ' + @InTblName +' WHERE '+@InCondition+ '=''' + @Dt+ ''''
SELECT(@DeleteSQL)
EXECUTE sp_executesql @DeleteSQL
END
[/CODE]

EXEC [DeleteTable] 'TABLE_NAME','COLUMN_NAME'

Where column_name is of datatype datetime

Msg 241, Level 16, State 1, Procedure spDeleteTable, Line 8
Conversion failed when converting date and/or time from character string.

expected output is as below to delete the table based on the date value passed in
DELETE FROM TABLE_NAME WHERE COLUMN_NAME= '2014-06-24 16:02:41.660'

-Neil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 16:12:04

No, CAST/CONVERT in your @DeleteSQL. Instead of @Dt, use CAST(@Dt as varchar(30))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-06-24 : 16:38:44

I made this change but still same error


SELECT @DeleteSQL = N'DELETE  FROM ' + @InTblName +' WHERE '+@InCondition+ '= CAST('+ @Dt + 'AS VARCHAR(30))'

-Neil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 16:45:45

No, you changed what I said. Copy/paste it into your last version. Like this:

SELECT @DeleteSQL = N'DELETE FROM ' + @InTblName +' WHERE '+@InCondition+ '=''' + CAST(@Dt AS varchar(30)) + ''''

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 17:33:11

Again, I'd avoid D-SQL, but I'd suggest that you use CONVERT with a universal style (120, 121, 126 or 127) depending on your needs.

SELECT @DeleteSQL = N'DELETE FROM ' + @InTblName +' WHERE '+@InCondition+ '=' + CHAR(39) + CONVERT(VARCHAR(30), @Dt, 126) + CHAR(39)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-24 : 17:38:37

Two things.

1. Avoid the possibilty of SQL injection.
2. The datetime precision is lost when casting. Anything less than a minute is truncated.

SET	@DeleteSQL = N'DELETE FROM ' + QUOTENAME(@InTblName) +' WHERE ' + QUOTENAME(@InCondition) + ' = ' + QUOTENAME(CONVERT(VARCHAR(24), @Dt, 121), '''');

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-06-25 : 11:05:54

Thanks Peso & Lamprey,

Looks like this is working now, I am getting the desired sql.

Peso, can you please elaborate little more here on SQL Injection.. due the delete I was using

Thanks and much appreciated for your patience in reading my bad code :)

also how can I mark this thread as answered

-Neil

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-25 : 12:59:42

quote:
Originally posted by aakcse

Thanks Peso & Lamprey,

Peso, can you please elaborate little more here on SQL Injection.. due the delete I was using

Maybe this will help:
http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources