Code as a string - problems with ' and +

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 2005 Forums

Transact-SQL (2005)

Code as a string - problems with ' and +

Author

Topic

rdw72777
Starting Member

14 Posts

Posted - 2012-06-28 : 14:25:45

I have the following code that works just fine as a select statement. The issue I have is that this code is actually going to be used to update a view via a stored procedure, and the only way i can think of updating a view through an SP is via a literal text string.

When I try to pass this code through to a string, it has issues with tick marks (') and plus signs (+). So i tried to set the date '2050-12-31 23:59:00' as a variable called @maxEndDate and pass that through, but the code translates it to 'Dec 31 2050 11:59PM' even though I've defined @maxEndDate as datetime.

Secondly, in the code I have effective date key set to be the a vlaue created by adding the year and the month together (where year and month are both found using cast and convert functions on the 'yyyymm' data field. I'm not sure how to add two items together without a plus sign. I'm not sure if I can concatenate using left/right either but I'm too frustrated at this point in time.

Can anyone help me translate the code below to a text string (i.e. re-write it to get around or eliminate tick marks and plus signs?



select 
		  [foreign exchange key],[currency key], [to usd], [from usd], [effective date], 
		  [expiration date] = 
				case when [effective date] = 
					  convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then '2050-12-31 23:59:00' else [expiration date] end
		  ,[is current] 
		  ,[effective date key] = year([effective date]) * 10000 + month([effective date])*100
		  ,[expiration date key] = 
				year(
				case when [effective date] = 
					  convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then '2050-12-31 23:59:00' else [expiration date] end) * 10000 
				+ month(case when [effective date] = 
					  convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then '2050-12-31 23:59:00' else [expiration date] end)* 100 + 99  
	from crs..[dim foreign exchange]
	where [effective date] <= convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8)))

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-28 : 15:02:51

If I understood you correctly, you want to pass this SQL string as a parameter to a stored procedure. Before I say anything more, I want to say that this is a VERY BAD idea. The risk of SQL injection is very high.

I don't know enough about the update you are trying to do to say how you should do it, but generally speaking, you want to pass the variables as parameters and then use those to do the update in a query in the stored proc.

Having said that, if you want to do it AMA, replace all the single quotes with a pair of single quotes. Like this:

declare @x varchar(max);
set @x = 'select 
		  [foreign exchange key],[currency key], [to usd], [from usd], [effective date], 
		  [expiration date] = 
				case when [effective date] = 
					  convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ''2050-12-31 23:59:00'' else [expiration date] end
		  ,[is current] 
		  ,[effective date key] = year([effective date]) * 10000 + month([effective date])*100
		  ,[expiration date key] = 
				year(
				case when [effective date] = 
					  convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ''2050-12-31 23:59:00'' else [expiration date] end) * 10000 
				+ month(case when [effective date] = 
					  convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ''2050-12-31 23:59:00'' else [expiration date] end)* 100 + 99  
	from crs..[dim foreign exchange]
	where [effective date] <= convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8)))';
	
EXEC YourStoredProc @x;

But at the risk of being sounding naggy and repetitive, please don't do it.

rdw72777
Starting Member

14 Posts

Posted - 2012-06-28 : 15:18:16

Sunita,

Thanks for the suggestion, Ill try implementing it and get back to you.

As for your concerns, I'm not sure I'm doing what you say or not (nor am I aware of what SQL injection is).

Here is my code in it's entirety. I'm passign the code through as a variable and executing it all in the same SP, so i'm not sure if that is bad or good. I do know that it works if I can get around these ' and + issues as I can run it outside a stored procedure successfully.

The SP is called by a C# module, and C# passes in the @date parameter value.

USE [AMORT_T]
GO
/****** Object: StoredProcedure [dbo].[proc_fx_rollback] Script Date: 06/28/2012 15:15:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[proc_fx_rollback] @date int as

--@date is yyyymm format

declare @sql_v_fx as varchar(max), @sql_dim_for_exc as varchar(max)
declare @maxEndDate as datetime

set @maxEndDate = '2050-12-31 23:59'

--remove prior date in [zstbl_fx_rollback]
truncate table zstbl_fx_rollback

--insert the user entered month
--specify in the VS report whether it's Balance Sheet or Income Statement date
insert into [zstbl_fx_rollback] (yyyymm) select @date

--alter the v_fx view to into account the date in the [zstbl_fx_rollback] table

select @sql_v_fx =
'
Alter view [dbo].[v_fx] as
select
[foreign exchange key],[currency key], [to usd], [from usd], [effective date],
[expiration date] =
case when [effective date] =
convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ' + @maxEndDate + ' else [expiration date] end
,[is current]
,[effective date key] = year([effective date]) * 10000 + month([effective date])*100
,[expiration date key] =
year(
case when [effective date] =
convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ' + @maxEndDate + ' else [expiration date] end) * 10000
+ month(case when [effective date] =
convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ' + @maxEndDate + ' else [expiration date] end)*100 + 99
from crs..[dim foreign exchange]
where [effective date] <= convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8)))
'

exec(@sql_v_fx)
--EXEC sp_refreshview v_fx

--alter the [dim foreign exchange] view to into account the date in the [zstbl_fx_rollback] table
select @sql_dim_for_exc =
'
alter view [dbo].[Dim Foreign Exchange] as
select
[foreign exchange key],[currency key], [to usd], [from usd], [effective date],
[expiration date] =
case when [effective date] =
convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8))) then ' + @maxEndDate + ' else [expiration date] end
,[is current], [etl task run key]
from crs..[dim foreign exchange]
where [effective date] <= convert(datetime,cast((select isnull(max(yyyymm),205012) from zstbl_fx_rollback) * 100 + 1 as char(8)))
'

exec(@sql_dim_for_exc)

rdw72777
Starting Member

14 Posts

Posted - 2012-06-28 : 15:40:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources