October 1, 2002 at 12:44 pm
I am trying to create a dynamic IN with a string variable and its telling me error on line 1 which does not tell me anything well except its broken.
my sql statement looks like this:
exec ('SELECT unitnum,drivernum,drivername,comchekcardnum,transactiondate,truckstopname,truckstopcity,truckstopstate,tripnum,fuelchrg,tractorcost,CashAdvAmt,cashadvchrg,totamtdue,oilcost from fuelinvoice where transactiondate between ' + @date1 + ' and ' + @date2 + ' and drivernum in (' + @alldrivernum + ') order by transactiondate,drivernum')
the @alldrivernum variable will look something similar to:
'8512','37394','12103','35482','15711','36129','8943','8514'
in the db drivernum is a varchar so I need the '.
Any suggestions to what is wrong?
Matt
October 1, 2002 at 1:01 pm
Wonder if the @date1 and @date2 variables could be causing the problems. What format are they and what is there value?
Normally when I get a error in dynamic SQL I always do this
declare @CMD varchar(4000)
set @cmd = 'SELECT unitnum,drivernum,drivername,comchekcardnum,transactiondate,truckstopname,truckstopcity,truckstopstate,tripnum,fuelchrg,tractorcost,CashAdvAmt,cashadvchrg,totamtdue,oilcost from fuelinvoice where transactiondate between ' + @date1 + ' and ' + @date2 + ' and drivernum in (' + @alldrivernum + ') order by transactiondate,drivernum'
print @cmd
exec (@cmd)
This way I know exactly the command that is going to be executed. This allows me to review the Dynamic Code, and also test it.
Hope this might help.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2002 at 1:16 pm
hmmm once I did that you are right it tells me it cant convert datetime to a character string. I tried casting the 2 date fields as datetime. The database has these values in a smalldatetime field. They are being inputted as standard USA dates like 01/01/02.
So, what must I do to get around this issue? Shouldnt the dates stay as datetime fields?
Matt
October 1, 2002 at 1:25 pm
Since you are building a string to be executed you need to change your datetime varables to a string. Use the CAST function like this:
cast(@date1 as char)
Also you will need to probably put some quotes around those dates string values.
Hope this helps.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2002 at 1:51 pm
yeah that solved the problem... I accepted the date as a varchar and in the string itelf I put in a Cast to convert it to a datetime and now it works properly.
Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply