Problem with variables in dynamic SQL statement.
-
I would relaly appreciate it if someone would tell me what I'm doing wrong. The stored procedure below throws the following error, even when passed SQL SMALLDATETIME variables. (You can see my test script just below the procedure.) I think the problem lies in the way I'm embedding my variables in the SQL string, but I don't know how else to do it.
................
THE ERROR:
................
"Syntax error converting character string to smalldatetime data type."
..................................
THE STORED PROCEDURE:
..................................
CREATE PROCEDURE rp_RPT_DSA_OrderTax
@startDate SMALLDATETIME,
@endDate SMALLDATETIME,
@strGroupBy VARCHAR(15)
AS
DECLARE @sqlString NVARCHAR(500), @chStatus CHAR(1)
SET @chStatus = 'X'
SET @sqlString = N'
INSERT INTO DSA2 (rpt_column, date, amount, key1)
SELECT 6, order_date, SUM(sales_tax), ' + @strGroupBy + '
FROM SYSOENT
WHERE order_status != ' + @chStatus + '
AND order_date BETWEEN ' + @startDate + ' AND ' + @endDate + '
GROUP BY order_date, ' + @strGroupBy + '
ORDER BY order_date '
EXEC (@sqlString)
GO
-- END PROCEDURE
.........................
THE TEST SCRIPT:
.........................
DECLARE @DATE1 SMALLDATETIME, @DATE2 SMALLDATETIME
SET @DATE1 = GETDATE()
SET @DATE2 = GETDATE()
EXEC rp_RPT_DSA_OrderTax @DATE1, @DATE2, 'batch_no' ;
SELECT * FROM DSA2 ;
-- END TEST SCRIPT
...................................................
Ideas/suggestions would much appreciated,
Greg
-
AND order_date BETWEEN ' + @startDate + ' AND ' + @endDate + '
You aren't single-quoting your dates.
Anytime you have a problem with dynamic SQL, PRINT it instead of EXECuting it and you'll see the issues.
-
Also, avoid using nvarchar unless you deal with foreign language content in your database. nchar and nvarchar take two bytes for every byte a char would take. Of course, in a little stored procedure it probably won't make much of a diff, but this definitely would fall into a 'best practices' sort of thing.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I really appreciate the advice about NVARCHAR (which I picked up from examples on BooksOnline, and figured if they did it, it must be a good idea. Wrong.)
But as far as "You aren't single-quoting your dates", when I quote them, I get another error: Invalid column name '@startDate'
Like I said in my post, I don't know how else to do it. I've tried about 20 different configurations of quotes in my code, and everything I try generates errors.
Would you please show me an example of how this is done?
And how come it doesn't work when I pass '2005-01-01' as an argument? It's a quoted date.
-
Greg,
First, yeah, Wayne is absolutely correct about NVARCHAR vs VARCHAR...
PW was correct, as well, about the single quoted dates except that PW didn't take it far enough.... SQL doesn't, for some reason, like to do the instrinsic conversion of dates to VARCHAR in concatenated strings.
The following example fails with exactly the same error message the code from your original post did....
USE PUBS
GO
DECLARE @StartDate SMALLDATETIME
SET @StartDATE = '06/15/1992'
DECLARE @SQLString VARCHAR(8000)
SET @SQLString =
'
SELECT *
FROM dbo.Sales
WHERE Ord_Date = ' + @StartDATE
PRINT @SQLString
Server: Msg 295, Level 16, State 3, Line 6
Syntax error converting character string to smalldatetime data type.
Compare the above to the following that works...
USE PUBS
GO
DECLARE @StartDate SMALLDATETIME
SET @StartDATE = '06/15/1992'
DECLARE @SQLString VARCHAR(8000)
SET @SQLString =
'
SELECT *
FROM dbo.Sales
WHERE Ord_Date = ''' + CONVERT(VARCHAR(10),@StartDATE,101) + ''''
PRINT @SQLString
(results from above)
SELECT *
FROM dbo.Sales
WHERE Ord_Date = '06/15/1992'
The code that works had to have an EXPLICIT conversion of the SMALLDATETIME to a VARCHAR as well as adding extra quotes. Note that for single quotes to show up in a string, there has to be two of them to make each as well as the "outside" single quotes that contain the overall string.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just to add:
When working with DATETIMES you should use a language and settings independant save format. A pretty good overview can you find here:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
And a little further addition.....
Make sure you CAST / CONVERT to a format and length that is going to fit in with your query. e.g:
SET @SQLString = 'SELECT * FROM dbo.Sales WHERE Ord_Date = ''' + CONVERT(VARCHAR(10),@StartDATE,101) + ''''
Gives:
'SELECT * FROM dbo.Sales WHERE Ord_Date = '06/15/1992''
SET @SQLString = 'SELECT * FROM dbo.Sales WHERE Ord_Date = ''' + CAST(@StartDATE AS varchar(255)) + ''''
Gives:
'SELECT * FROM dbo.Sales WHERE Ord_Date = 'Jun 15 1992 12:00AM''
Have fun
Steve
We need men who can dream of things that never were.
-
Greg,
Steve is right... ya gotta watch the variable length when converting dates. The "101" date format I used is a shorty for just date and only requires 10 characters (hence the VARCHAR(10) in the CONVERT). The longest canned date format SQL has to offer is 26 characters. Most folks I know like round numbers so they just use VARCHAR(30) unless output space is at a premium for some reason.
To see all of the date formats, look up CONVERT in "Books on Line". And, the URL Frank posted is definately worth the read!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
T H A N K Y O U S O M U C H ! ! !
I am so relieved to finally have this working. What amazes me though, is that if I use the exact conversion format that Jeff used, and pass it this date parameter '2000-01-01' it works just fine, even thought the order is different, as are the delimiters.
Frank, thanks for the article. I'm printing it, so I can read it in bed tonight. (Yes, I'm a geek who reads programming books in bed.)
I have a question for all of you. What part of the world do each of you live in, and if you live in the U.S., what state?
Thanks a million,
Greg
-
One more thing...
The article Frank recommended is just the ki8nd of thing I'm always looking for. If anyone has any other aritcles to recommend that cover a topic thoroughly, I would be veruy greateful for the tip (of interest to developers, not server admin stuff).
In the meantime, I'm going to try searching forums for article recommendations from the past. I might turn up some interesting stuff. Of course, there are a million tutorials on the WWW, but unfortunately 95% of them are geared toward the beginner who doesn't know a loop from a variable. It's difficult to find real-world examples.
Thanks again, and please let me know where you all reside.
-Greg
-
(Yes, I'm a geek who reads programming books in bed.)
LOL. Nothing wrong with this at times. I guess you're in very good company here with this habit.
I for myself live in Germany somewhere near Cologne and Düsseldorf.
As for your request for good articles. It's funny nobody mentioned it right now, but with respect to dynamic sql questions, the articles by SQL Server MVP Erland Sommarskog http://www.sommarskog.se are very frequently referenced. They are long and sometimes funny to read, since Erland is a non-native english speaker, like me, but they are definitely worth the read!
Here's a quick collection of other sources I find useful in no particular order:
http://www.users.drew.edu/skass/sql/
http://www.sqljunkies.com/weblog/amachanic/
But honestly, most things you can read in BOL, which is the premier source of information.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nothing wrong with this at times. I guess you're in very good company here with this habit. - Too many home truths here for my liking.....
Quick note on the NVARCHAR thang.
I am reasonably certain - without going back and re-reading everything - that if you are going to be using a lot of dynamic sql, you are going to end up using sp_executesql a lot. This sproc only accepts unicode constants or constants that can be implicitly converted to NTEXT.
So, unfortunately, even if it isn't recomended as good practice - you might as well get used to using NVARCHARS.
But then again - dynamic sql is pretty much alienated as bad practice anyway so how much can NVARCHARS hurt? 
I'm from sunny Bolton, just outside Manchester in the UK.
And - you have found your way to the best resource after BOL - sqlservercentral.com. There are some excellent guys in here, always willing to help - with a response time second to none.
Have fun
Steve
We need men who can dream of things that never were.
-
Sunny Bolton? Try Phoenix, AZ, USA some time if you want sunny! 🙂 Just don't come in July through September or you might melt.
I can't help but to smile and giggle a little at Bolton, it provides instant flashbacks to Monty Python's Dead Parrot sketch and the infamous Notlob.
One of these days I'd love to spend a few weeks in the UK and go on Python Location Shoot tour.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply