Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 MSSQL store-proc IF-Else statement with variable, need single quote with variable

views
     
TSangelcat
post Aug 12 2015, 10:14 AM, updated 9y ago

Getting Started
**
Junior Member
78 posts

Joined: Dec 2008
QUOTE
Declare @sjid nvarchar(10), @year NCHAR(4)
SET @year=2015
Declare tmp CURSOR FOR SELECT sjid FROM SUBJECT WHERE YEAR=@year

OPEN tmp
FETCH NEXT FROM tmp INTO @sjid

WHILE @@FETCH_STATUS=0
BEGIN
    IF (SELECT COUNT(*) FROM COURSE WHERE SJID=@sjid)<0
        ......
    ELSE
        .......
......
END


The "Select COUNT(*) from course...." seem outcome
Select COUNT(*) from COURSE where SJID=MATH
which got error coz the SJID is nvarchar it should be use
Select COUNT(*) from COURSE where SJID='MATH'

i tried put SJID='@sjid' but it run it become SJID='@sjid' which is not using values of @sjid
using SJID=''''+@sjid+'''' also fail , this i had tried and success if i put whole query as string then EXEC it
but when it go to IF-Else , it cant read IF(EXEC(@string))<0
anangryorc
post Aug 12 2015, 09:28 PM

On my way
****
Senior Member
597 posts

Joined: May 2006


try :
QUOTE
Declare @sjid nvarchar(10), @year NCHAR(4), @nCount INT
SET @year=2015
Declare tmp CURSOR FOR SELECT sjid FROM SUBJECT WHERE YEAR=@year

OPEN tmp
FETCH NEXT FROM tmp INTO @sjid

WHILE @@FETCH_STATUS=0
BEGIN
    SELECT @nCount = COUNT(*) FROM COURSE WHERE SJID=@sjid
    IF @nCount < 0
        ......
    ELSE
        .......
......
END


* Seems to me cursor can be replaced with a join, is there anything which makes you choose cursor instead of join?
TSangelcat
post Aug 17 2015, 02:27 PM

Getting Started
**
Junior Member
78 posts

Joined: Dec 2008
Bcoz i had simplify the query left only the part i dont know how to do.
So is it any way to do that? i left this only part to complete the query
TSangelcat
post Aug 17 2015, 02:29 PM

Getting Started
**
Junior Member
78 posts

Joined: Dec 2008
the reason for using cursor coz i want to use the return value to update a table in horizontal way
TSangelcat
post Aug 17 2015, 02:46 PM

Getting Started
**
Junior Member
78 posts

Joined: Dec 2008
this part of code used to insert new line of data into COURSE if it cant found any match ,else update the particular line of data
ChrisJL
post Aug 21 2015, 01:02 PM

New Member
*
Junior Member
23 posts

Joined: Jul 2015
Table update can also be done with set based approach. And more Efficiently.
TSangelcat
post Aug 27 2015, 09:58 AM

Getting Started
**
Junior Member
78 posts

Joined: Dec 2008
thx, actually i found another way to solve the problem by create another table to store the data then from there update into the table i want. a lot faster than using FETCH NEXT function.

But the question still remain , what if one day i need to use IF-Else with a variable which is storing a string, how the IF statement look like?

@text nchar(10)
set @text ='abc'
IF (SELECT * FROM XXX WHERE TEXT=@text) ......

it will then read as (SELECT * FROM XXX WHERE TEXT=abc)
not (SELECT * FROM XXX WHERE TEXT='abc')
ChrisJL
post Aug 28 2015, 10:40 AM

New Member
*
Junior Member
23 posts

Joined: Jul 2015
QUOTE(angelcat @ Aug 27 2015, 09:58 AM)
thx, actually i found another way to solve the problem by create another table to store the data then from there update into the table i want. a lot faster than using FETCH NEXT function.

But the question still remain , what if one day i need to use IF-Else with a variable which is storing a string, how the IF statement look like?

@text nchar(10)
set @text ='abc'
IF (SELECT * FROM XXX WHERE TEXT=@text)  ......

it will then read as (SELECT * FROM XXX WHERE TEXT=abc)
not (SELECT * FROM XXX WHERE TEXT='abc')
*
Escape the single quote by doubling it up.

set @text = '''abc'''
TSangelcat
post Aug 29 2015, 10:45 AM

Getting Started
**
Junior Member
78 posts

Joined: Dec 2008
QUOTE(ChrisJL @ Aug 28 2015, 10:40 AM)
Escape the single quote by doubling it up.

set @text = '''abc'''
*
it wont work that here, it is a string variable used to store data from another query.
i'm using Cursor to SELECT the data then store into @text, read the 1st post that i had put my tested code


 

Change to:
| Lo-Fi Version
0.0137sec    0.15    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 02:18 AM