Prev: How to copy stored procedures (in SQL 2005)?
Next: T-Sql question
From: John Barr on 30 Jun 2008 20:19
Trying to get single quotes are + lvc_Column + in inner Dynamic SQL Loop
below. If I add '' to either side, when I execute, I get

Wednesday_02 = case when right(' + @lvc_Column + ', 2) = c.Today then
c.Course

and it should be

Wednesday_02 = case when right('Wednesday_02', 2) = c.Today then c.Course

use AELN
go

if (object_id('RMS.upsResourceManagement') is not null)
and (objectProperty(object_id('RMS.upsResourceManagement'), 'IsPRocedure') =
1)
begin
raiserror('Dropping and recreating procedure ''%s''', 0, 1,
'upsResourceManagement')
drop procedure RMS.upsResourceManagement
end
go

/*==============================================================================

Procedure: upsResourceManagement

Schema: RMS

Database: AELN

Owner: dbo

Application: N/A

Inputs: Start Date : Datetime
End Date : datetime

Outputs: N/A

Result Set: 1 to Many Roster Records

Usage: declare @idt_StartDate datetime
,@idt_EndDate datetime

select @idt_StartDate = '4-1-2008'
,@idt_EndDate = '4-30-2008'

exec RMS.upsResourceManagement @idt_StartDate
,@idt_EndDate

Description: This Procedure is used to show what classes instructors are
teaching between the specified date range.

Version: 1.00.00

Compatability: SQL Server 2005 (90)

Created By: John M. Barr

Created On: 02-05-2007

================================================================================
History: (Format)

When Who Version Member Tag What
---------- ----------- ------- ---------
--------------------------------
02-05-2007 John Barr 1.00.00 (None) Initial Revision
==============================================================================*/
create procedure RMS.upsResourceManagement (@idt_StartDate datetime
,@idt_EndDate datetime) as

/*==============================================================================
Variable Declarations & Temporary Tables
==============================================================================*/
declare @lvc_SQL varchar(max)

declare @ltbl_Recource table (MemberID int
,Instructor varchar(128)
,DidacticID int
,ClassDate datetime
,Flag bit)

/*==============================================================================
Initialize Environment
==============================================================================*/

set nocount on

/*==============================================================================
Code Section
==============================================================================*/

-- Build Temporary Table
select @lvc_SQL = 'create table #ltbl_Temp (MemberID int, ' + char(13) +
'Instructor varchar(128), ' +
char(13)

while (@idt_StartDate <= @idt_EndDate)
begin

if (@idt_StartDate = @idt_EndDate)
select @lvc_SQL = @lvc_SQL + datename(dw, @idt_StartDate) + '_' +
case when Day(@idt_StartDate) < 10 then '0' + cast(Day(@idt_StartDate) as
varchar) + ' varchar(128)'

else cast(Day(@idt_StartDate) as varchar) + ' varchar(128)'
end
else
select @lvc_SQL = @lvc_SQL + datename(dw, @idt_StartDate) + '_' +
case when Day(@idt_StartDate) < 10 then '0' + cast(Day(@idt_StartDate) as
varchar) + ' varchar(128),' + char(13)

else cast(Day(@idt_StartDate) as varchar) + ' varchar(128),' + char(13)
end

select @idt_StartDate = @idt_StartDate + 1

end

select @lvc_SQL = @lvc_SQL + ') ' + char(13) + char(13)

-- Populate Unique List of Instructors
select @lvc_SQL = @lvc_SQL + 'insert into #ltbl_Temp (MemberID,
Instructor) ' + char(13) +
' select t.MemberID
,m.FirstName + case when
isnull(m.MiddleInitial, '''') = '''' then '' '' + m.LastName
else + '' '' +
m.MiddleInitial + '' '' + m.LastName
end
from RMS.Trainer t with (nolock) inner
join Staff.Member m with (nolock)

on m.MemberID = t.MemberID

and m.IsDeleted = 0
inner
join RMS.Didactic d with (nolock)

on d.DidacticID = t.DidacticID

and d.IsDeleted = 0
where not exists (select top 1 1
from RMS.Roster r
where r.DidacticID =
d.DidacticID
and
r.CertificationDate is null
and
r.TerminationDate is null
and r.ReleaseDate
is null)
and t.IsDeleted = 0
group by t.MemberID
,m.FirstName
,m.MiddleInitial
,m.LastName ' + char(13)

-- Grab List of Members, Day of the month and class they were teaching.
select @lvc_SQL = @lvc_SQL + 'create table #ltbl_Class (MemberID int
,Today varchar(2)
,Course
varchar(128)) ' + char(13)

select @lvc_SQL = @lvc_SQL + ' insert into #ltbl_Class (MemberID, Today,
Course) ' + char(13) +
' select t.MemberID
,case when Day(t.StartDate) < 10
then ''0'' + cast(Day(t.StartDate) as varchar)
else cast(Day(t.StartDate)
as varchar)
end
,c.Course
from RMS.Trainer t with (nolock) inner
join Staff.Member m with (nolock)

on m.MemberID = t.MemberID

and m.IsDeleted = 0
inner
join RMS.Didactic d with (nolock)

on d.DidacticID = t.DidacticID

and d.IsDeleted = 0
inner
join RMS.Course c with (nolock)

on c.CourseID = d.CourseID

and c.IsDeleted = 0
where t.IsDeleted = 0 ' + char(13)

-- Update Instructor Classes
select @lvc_SQL = @lvc_SQL + 'declare @lvc_iSQL varchar(max)
,@lvc_Column varchar(128)

create table #ltbl_Table (ColumnName
varchar(128)
,Flag bit)

insert into #ltbl_Table(ColumnName, Flag)
select name, 0
from tempdb.sys.columns c
where object_id =
object_id(''tempdb.dbo.#ltbl_Temp'')
and name not in (''MemberID'',
''Instructor'')

select @lvc_iSQL = ''update t
set ''

while (select top 1 1
from #ltbl_Table t
where t.Flag = 0) = 1
begin

select top 1 @lvc_Column = t.ColumnName
from #ltbl_Table t
where t.Flag = 0

if exists (select top 1 1
from #ltbl_Table t
where t.Flag = 0
and @lvc_Column <>
t.ColumnName)
select @lvc_iSQL = @lvc_iSQL +
@lvc_Column + '' = case when right('' + @lvc_Column + '', 2) = c.Today then
c.Course

end, ''
else
select @lvc_iSQL = @lvc_iSQL +
@lvc_Column + '' = case when right('' + @lvc_Column + '', 2) = c.Today then
c.Course

end ''

update t
set t.Flag = 1
from #ltbl_Table t
where t.ColumnName = @lvc_Column

end
'

select @lvc_SQL = @lvc_SQL + 'select @lvc_iSQL = @lvc_iSQL +
''from #ltbl_Temp t inner join #ltbl_Class c
on c.MemberID
= t.MemberID''

'

select @lvc_SQL = @lvc_SQL + 'execute(@lvc_iSQL)'


select @lvc_SQL = @lvc_SQL + 'select * from #ltbl_Temp
select * from #ltbl_Class'

execute(@lvc_SQL)


print @lvc_SQL



go
From: Eric Isaacs on 30 Jun 2008 21:49
John,

It looks like you have in-line SQL that's building more in-line SQL.
This is very difficult to understand. Unless you're looking for job
security, there might be a better way to do this without in-line SQL
at all. You're results are going into TEMP tables anyway, so if you
just use regular SQL to insert into the temp tables, and use regular
SQL to select from them, you can probably do this in a more straight
forward way avoiding the inline SQL all together.

Can you please include the DDL for the tables involved and some sample
data from those tables. Insert statements for the sample data into
the tables involved would be ideal. If you provide this, we can
probably give you a solution that will not require any in-line SQL.

If you really just want to figure out how to get the Wednesday_02 to
display correctly, just realize that for every pair of quotes in the
inline SQL, it represents a single quote in that string. Since it
looks like you have in-line sql building more in-line SQL, you'll need
lots of single quotes to pass those two single quotes through to the
next execute statement.

DECLARE @SQL AS VARCHAR(8000)
SET @SQL = '''' --4 quotes

....this will set the @SQL variable to one single quote, two to delimit
the string and another two to represent the single quote character.

DECLARE @SQL AS VARCHAR(8000)
SET @SQL = 'DECLARE @Insanity AS VARCHAR(8000)

SET @Insanity = ''''''''

PRINT @Insanity'

EXEC(@SQL)

....that's about how many single quotes you need to set the inner
@Insanity string to a single quote. I hope you can see from this
little example that it's just going to be too difficult for someone
else to understand and maintain it.

Please tell us why you're opting to use in-line sql and maybe we can
help you find a workaround.

I hope that helps,

-Eric Isaacs


From: John Barr on 30 Jun 2008 22:24
The primary temp table is built dynamically based on the date range passed
into the procedure, and becasue of that I couldnt just use straight sql. The
field names have to be in a format of DayOfWeek_Day for a process that is
already existing. I know about the '' '''' but when I added the second set of
single ticks, it stops interpreting the column name variable being passed.

"Eric Isaacs" wrote:

> John,
>
> It looks like you have in-line SQL that's building more in-line SQL.
> This is very difficult to understand. Unless you're looking for job
> security, there might be a better way to do this without in-line SQL
> at all. You're results are going into TEMP tables anyway, so if you
> just use regular SQL to insert into the temp tables, and use regular
> SQL to select from them, you can probably do this in a more straight
> forward way avoiding the inline SQL all together.
>
> Can you please include the DDL for the tables involved and some sample
> data from those tables. Insert statements for the sample data into
> the tables involved would be ideal. If you provide this, we can
> probably give you a solution that will not require any in-line SQL.
>
> If you really just want to figure out how to get the Wednesday_02 to
> display correctly, just realize that for every pair of quotes in the
> inline SQL, it represents a single quote in that string. Since it
> looks like you have in-line sql building more in-line SQL, you'll need
> lots of single quotes to pass those two single quotes through to the
> next execute statement.
>
> DECLARE @SQL AS VARCHAR(8000)
> SET @SQL = '''' --4 quotes
>
> ....this will set the @SQL variable to one single quote, two to delimit
> the string and another two to represent the single quote character.
>
> DECLARE @SQL AS VARCHAR(8000)
> SET @SQL = 'DECLARE @Insanity AS VARCHAR(8000)
>
> SET @Insanity = ''''''''
>
> PRINT @Insanity'
>
> EXEC(@SQL)
>
> ....that's about how many single quotes you need to set the inner
> @Insanity string to a single quote. I hope you can see from this
> little example that it's just going to be too difficult for someone
> else to understand and maintain it.
>
> Please tell us why you're opting to use in-line sql and maybe we can
> help you find a workaround.
>
> I hope that helps,
>
> -Eric Isaacs
>
>
>
From: Eric Isaacs on 30 Jun 2008 22:49
To get you're existing code to work, you probably just need more
single quotes strategicly placed within the inner inline sql. Buy
anytime you need one quote within the inner-inline SQL, you should be
adding about 8 to the outside string.
From: Paddy on 1 Jul 2008 04:04
I often use a variable to hold the quote, which makes the code more
readble.

Declare @q varchar(1)
Set @q = char(39)

so


Wednesday_02 = case when right(' + @lvc_Column + ', 2) = c.Today then

becomes

Wednesday_02 = case when right(' + @q + @lvc_Column + @q + ', 2) =
c.Today then

Paddy
 |  Next  |  Last
Pages: 1 2
Prev: How to copy stored procedures (in SQL 2005)?
Next: T-Sql question