Using Single Quotes in a dynamic SQL

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

Transact-SQL (2008)

Using Single Quotes in a dynamic SQL

Author

Topic

silvino90
Starting Member

7 Posts

Posted - 2014-03-30 : 02:32:17

please some help...what is wrong in the next syntax? Thanx

DECLARE @Output nvarchar(max)
set @Output=N''

SET @Output =
'select * from
(
SELECT
'+@pData+' as Data
,pp.prestatorid as Marca
,pp.[ID]
,pp.[ClientID]
,pp.[IsValid]
,pp.[Frecventa]
,pp.[CuEfort]
,pp.[CuTarif]
,isnull(pp.[Tarif],0) as Tarif
,pp.[DataStart]
,pp.[DataEnd]
,isnull(act.[Description], ' + char(39)+''+char(39) +') as Description
,pp.[validat]
,pp.[dataop]
,pp.[operator]
,null as Anul
,null as Luna
,c.companyNEW as Company
,pp.SubProdusId as ActivityId
,case when @lang = ' + char(39)+''+char(39) +' or @lang is null or @lang = ' + char(39)+'ro-ro'+char(39) +'
then s.Produs
else s.ProdusEn end as Activity
,0 AS TotalTime
,( select pr.Tip from tbl_sla_prestator pr where cod = '+@pMarca+') as Tip
FROM tbl_SLA_PrestatorProdus pp
left join tbl_sla_Activities act on act.PrestatorProdusId = pp.Id
and @tLuna = month(act.Date)
and @tAnul = year(act.Date)
left join tbl_sla_subprodus s on pp.SubProdusId = s.Id
inner join tbl_sla_company c on pp.ClientId = c.id_company
where
pp.prestatorid = '+@pMarca+'
and (pp.Frecventa = @Frecventa)
and DATEADD(dd,0,datediff(dd,0,dateadd(dd,1-day(pp.[DataStart]), pp.[DataStart]))) <= @fiterDate
and @fiterDate <= DATEADD(dd,0,datediff(dd,0,dateadd(dd,1-day(pp.[DataEnd]), pp.[DataEnd])))
--and (@ClientId is null or pp.ClientID = @ClientId)
--order by clientID, iD --Activity,
) as toate'

ERRORS GENERATED
Conversion failed when converting datetime from character string.
Incorrect syntax near ','.
Incorrect syntax near the keyword 'as'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-30 : 03:08:16

what is the data type for those variables ? Anyone of those is a datetime ?

KH
[spoiler]Time is always against us[/spoiler]

silvino90
Starting Member

7 Posts

Posted - 2014-03-30 : 06:10:39

declare @pData as datetime
declare @pMarca as nvarchar(100)
declare @UserAccount as nvarchar(50)
declare @lang varchar(MAX)
declare @Frecventa nvarchar(50)
declare @ClientId numeric(18,0)

DECLARE @idList varchar(500)
SELECT @idList = STUFF(( SELECT
'],[' + ltrim(str(id_company))
FROM tbl_SLA_Company
ORDER BY '],[' + ltrim(str(YEAR(id_company)))
FOR XML PATH('')
), 1, 2, '') + ']'

DECLARE @Output nvarchar(max)
set @Output=N''

i have managed to solve error with... Conversion failed when converting datetime from character string
but the other two errors no.
Incorrect syntax near ','.
Incorrect syntax near the keyword 'as'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-30 : 09:27:56

[code]SELECT
'''+ convert(varchar(10), @pData , 112) +''' as Data
,pp.prestatorid as Marca[/code]

KH
[spoiler]Time is always against us[/spoiler]

silvino90
Starting Member

7 Posts

Posted - 2014-04-01 : 05:53:28

pls help...how i put this:

SELECT
case when @lang = ' ' or @lang is null or @lang = 'ro-ro'
then s.Produs
else s.ProdusEn end as Activity
FROM tbl_sla_subprodus s
----------------------------------------------------------------------

INTO THIS

declare @lang varchar(MAX)
set @lang = 'ro-ro'
DECLARE @Output nvarchar(max)
set @Output=''

'
SELECT
case when @lang = ' ' or @lang is null or @lang = 'ro-ro'
then s.Produs
else s.ProdusEn end as Activity
FROM tbl_sla_subprodus s
'
exec(@Output)

silvino90
Starting Member

7 Posts

Posted - 2014-04-01 : 06:23:04

by the way...THANK YOU VERY MUCH..so far :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-01 : 07:50:49

escape the single quote with another single quote


set @Output=''

'
SELECT
case when @lang = '' '' or @lang is null or @lang = ''ro-ro''
then s.Produs
else s.ProdusEn end as Activity
FROM tbl_sla_subprodus s
'

KH
[spoiler]Time is always against us[/spoiler]

silvino90
Starting Member

7 Posts

Posted - 2014-04-01 : 09:34:35

done like these and works

SET @Output =
'
SELECT
case when ''' +@lang+ ''' = '''' or ''' +@lang+ ''' is null or ''' +@lang+ ''' = ''ro-ro''
then s.Produs
else s.ProdusEn end as Activity
FROM tbl_sla_subprodus s
'

THANK YOU :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-02 : 04:03:56

Also refer this http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources