/ Forward Slash Escape Character? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

/ Forward Slash Escape Character?

Author

Topic

petebob796
Starting Member

35 Posts

Posted - 2008-02-19 : 08:05:55

im having trouble getting this to work:

alter proc [ProGeneral_College_Structure] @Year nvarchar(4)
as
begin
DECLARE @SQLStatement nvarchar(1000)

Set @SQLStatement = 'SELECT School AS Level1Code, DIVISIONS.Div AS Level2Code,
DIVISIONS.ProgArea AS Level3Code, DIVISIONS.progName AS LevelName
, ' + SUBSTRING(@Year,1,2) + '/' + SUBSTRING(@Year,3,2) + ' AS AcademicYearID FROM DIVISIONS
WHERE (((DIVISIONS.[' + @Year + '])=1))
ORDER BY DIVISIONS.School, DIVISIONS.Div, DIVISIONS.ProgArea'

EXEC(@SQLStatement)
end

It's something to do with the / concatenation I think, is it an escape character or something tried // obviously and CHAR(47).
before I get comments I know it's dynamic sql and it's not great but I can't edit the divisions table so have to use a dynamic column.

petebob796
Starting Member

35 Posts

Posted - 2008-02-19 : 08:08:06

Should have put the AcademicYearID comes up as zero for any input year e.g. @Year = 0708 (academic year) and I need it to be 07/08 on output.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-19 : 08:16:07

What error are you getting?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-19 : 08:21:05

I would think you'd get an error at the "WHERE (((DIVISIONS.[' + @Year + '])=1))" if the the value that is associated with "@year" is not a valid column

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 08:29:36

Can you post the error message you got? Then it will be helpful for people to help you out on this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 08:44:47

Why are you passing column names as parameter?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:08:31

[code]ALTER PROCEDURE dbo.uspProGeneral_College_Structure
(
@Year VARCHAR(4)
)
AS

DECLARE @SQL NVARCHAR(1000)

SET @SQL = '
SELECT School AS Level1Code,
Div AS Level2Code,
ProgArea AS Level3Code,
ProgName AS LevelName,
''' + STUFF(@Year, 3, 0, '/') + ''' AS AcademicYearID
FROM DIVISIONS
WHERE [' + @Year + '] = 1
ORDER BY School,
Div,
ProgArea'

--PRINT @SQL
EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:13:52

Take this as a lesson. ALWAYS do a PRINT and you will see what is wrong...
What you did was to divide 07 with 08 with is 0 (with integer math).

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:15:43

Also read this excellent page
http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:18:31

This statement is better regarding SQL Injection.

SET	@SQL = '
SELECT		School AS Level1Code,
		Div AS Level2Code,
		ProgArea AS Level3Code,
		ProgName AS LevelName,
		' + QUOTENAME(STUFF(@Year, 3, 0, '/'), '''') + ' AS AcademicYearID
FROM		DIVISIONS
WHERE		[' + @Year + '] = 1
ORDER BY	School,
		Div,
		ProgArea'

E 12°55'05.25"
N 56°04'39.16"

petebob796
Starting Member

35 Posts

Posted - 2008-02-19 : 09:30:05

As I said I can't change the divisions table thats why I have to pass in a column name as parameter and use dynamic sql. As for an error im not actually getting one with that code but i am getting lines such as:
1 T AE AEROSPACE ENGINEERING 0
where i am expecting:
1 T AE AEROSPACE ENGINEERING 07/08

I think I have just relised while writing this it is performing a division as in 07/08 is 0

petebob796
Starting Member

35 Posts

Posted - 2008-02-19 : 09:41:52

Thanks for the help my last post was open and the fix was there before I sent it. Thanks for the help didn't know the stuff function will be handy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources