Dynamic SQL error - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL error

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-10-14 : 18:01:49

Here is a SP that execute @SQL
,but I keep getting an error says: converting error: datetime from char data type..

Create proc SP_test ( @site int ,@startdate datetime, @enddate datetime )
As

Declare @ActiveHourly varchar(10)
select @ActiveHourly = ( select Rxutil_desc from utilization where Rxutil_Id = 1 and site = @site)

declare @sql as nvarchar(4000)

set @sql =
N' select sum(case when pay in ( '+ @ActiveHourly +') then 1 else 0 end) as ''Active Hourly'''
+ N' from f_pharmacy'
+ N' where dispense_date between ' + @startdate + ' and ' + @enddate
+ N' and site = ' + @site

exec sp_executesql @SQL

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-14 : 18:44:19

you need to convert the datetime parameter to a string before concatenation with another string
convert(varchar,@startdate,121)

eg.
select 'xyz...' + convert(varchar,getdate(),121)
select 'xyz...' + getdate()

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-10-15 : 01:51:39

... and put some extra quotes around it

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 01:48:37

More Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-17 : 01:55:34

aha.. Thanks.. madhivanan.. Great link.. :-)

Complicated things can be done by simple thinking

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-17 : 09:24:37

As always ... why do people make things more complicated than they need to be?

There is no need for dynamic SQL here. And, in fact, if any of your Rxutil_desc values have single quotes in them or anything like that, you'll get errors. Why not just do it the right way?

Also, you are being very inefficient by select all rows and using CASE(SUM..). Just select the rows you need:

SELECT COUNT(*) as ActiveHourly
FROM Pharmacy
WHERE dispense_date between @startdate and @enddate AND
Pay IN (SELECT Rxutil_desc FROM utilization WHERE Rxutil_Id = 1 and site = @site)

Isn't that a *little* easier?

Avoid dynamic sql at all costs, and especially in this situation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources