January 4, 2012 at 12:57 pm
Hi
I'm having problems forming a working dynamic sql statement using openrowset. I know variables can't be used directly in openrowset, so I'm trying to get this to work by putting the whole statement in a variable.
With this version of my code, I get the following error (@cfrc_id is in fact a null) :
Conversion failed when converting the nvarchar value 'INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=C:\temp\surveyRecruiting_1_4_2012.xls',
'SELECT * FROM [export_template$]')
SELECT *
FROM survey_answers_recruiting
WHERE cfrc_id = isnull(' to data type int.
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@filename+''',
''SELECT * FROM [export_template$]'')
SELECT *
FROM survey_answers_recruiting
WHERE cfrc_id = isnull('+@cfrc_id+',cfrc_id)
ORDER BY cfrc_id, survey_date;'
And with this version I get the following error :
Must declare the scalar variable "@cfrc_id".
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@filename+''',
''SELECT * FROM [export_template$]'')
SELECT *
FROM survey_answers_recruiting
WHERE cfrc_id = isnull(@cfrc_id,cfrc_id)
ORDER BY cfrc_id, survey_date;'
How do I use the isnull function in this context ?
Thanks
January 4, 2012 at 1:17 pm
Im assuming that @cfrc_id is of type int, If @cfrc_id is null, then you have to compare the field [cfrc_id] with its own value, something redundat but necesary:
WHERE cfrc_id=cfrc_id
Otherwise, you need to compare cfrc_id with the given value of @cfrc_id:
WHERE cfrc_id= 5
This could be implemented on this way:
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@filename+''',
''SELECT * FROM [export_template$]'')
SELECT *
FROM survey_answers_recruiting
WHERE cfrc_id =' + case when @cfrc_id is null then 'cfrc_id' else convert(varchar, @cfrc_id) end
+ ' ORDER BY cfrc_id, survey_date;'
January 5, 2012 at 8:38 am
Thank you, this helps. The last bit I need help with is with the date condition. I can't seem to get it to print out right. Either I get an error concerning conversions with the code below (Conversion failed when converting datetime from character string) or the date gets converted to words (ex Jan 1 2011 00:00:00) when I use the code cast(@date_from as nvarchar). Would anyone have some pointers for me ? Thanks
I am using these values :
set @date_from = N'2011/01/01'
set@date_to = N'2011/02/01'
declare @temp varchar(100)
set @temp = ' between ''' + @date_from + '00:00:00'' AND ''' + @date_to + '23:59:59'''
SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;DATABASE='+@filename+''',
''SELECT * FROM [export_template$]'')
SELECT *
FROM survey_answers_recruiting
WHERE cfrc_id = ' + isnull(cast(@cfrc_id as nvarchar),'cfrc_id') + ' AND
survey_date ' + @temp + ' ORDER BY cfrc_id, survey_date;'
print @sql
exec (@sql)
January 5, 2012 at 9:07 am
Ok, I'm assuming that @date_from and @date_to are of type varchar. If so, you only need to add an space before the hour part in the @temp assingment:
set @temp = ' between ''' + @date_from + ' 00:00:00'' AND ''' + @date_to + ' 23:59:59'''
But if @date_from and @date_to are of type datetime, you should use CONVERT instead of CAST to specify a date format:
set @temp = ' between ''' + CONVERT(varchar, @date_from, 102) + ' 00:00:00'' AND '''
+ CONVERT(varchar, @date_to, 102) + ' 23:59:59'''
Best regards!
January 9, 2012 at 6:02 am
Thank you, option #2 has solved all my query problems.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply