SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 passing date to dynamic sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 06/24/2014 :  15:10:40  Show Profile  Reply with Quote
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

Edited by - aakcse on 06/24/2014 16:04:52

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/24/2014 :  15:16:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/24/2014 :  15:33:44  Show Profile  Reply with Quote
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).
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 06/24/2014 :  15:57:38  Show Profile  Reply with Quote
my apology Tara & Lamprey I've corrected the code now.. and posted the error message as well


-Neil
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/24/2014 :  15:59:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 06/24/2014 :  16:09:57  Show Profile  Reply with Quote
I tried casting it to varchar but still same error at runtime.



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


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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/24/2014 :  16:12:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 06/24/2014 :  16:38:44  Show Profile  Reply with Quote
I made this change but still same error

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


-Neil
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/24/2014 :  16:45:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/24/2014 :  17:33:11  Show Profile  Reply with Quote
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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/24/2014 :  17:38:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 06/25/2014 :  11:05:54  Show Profile  Reply with Quote
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

Edited by - aakcse on 06/25/2014 11:06:30
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/25/2014 :  12:59:42  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000