SQL Local Variable

Question

I use SQL Server 2005 and I have a query like this :

INSERT INTO [subject] ([sch_id],
                       [subj_from],
                       [subj_to],
)
SELECT
CASE
WHEN (SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name') IS NULL THEN NULL
ELSE (SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name')
END
AS 'sched_search_result',
   'Sample Value',
   'Sample Value'

This works fine. It inserts values inside [subject] table, The first value [sch_id] is taken from [schedule] table through a searched CASE statement.

I want to avoid code redundancy, so I tried to store the result of the SELECT statement in a local variable and evaluate it using an ISNULL function. Looks like this :

DECLARE @sched INT
INSERT INTO [subject] ([sch_id],
                       [subj_from],
                       [subj_to],
)
SELECT
CASE ISNULL((SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name'), 0)
WHEN 0 THEN NULL
ELSE @sched
END
AS 'sched_search_result',
   'Sample value',
   'Sample value'

But my code doesn't work. SQL Server 2005 says the error is coming from the line where I used the local variable (@sched). What is wrong in my query?

score 3 · Answer 1 · answered Aug 10 '11 at 07:20

3

I think this will do what you want.

INSERT INTO [subject] ([sch_id], [subj_from], [subj_to])
SELECT (
          SELECT [sch_id]
          FROM [schedule]
          WHERE [sch_name] = 'Search Schedule Name'
       ), 
      'from value',
      'to value'

answered Aug 10 '11 at 07:20

Mikael Eriksson

136,425
22
210
281

Subqueries are not allowed on insert statements. Only scalar expressions are allowed. - Says SQL Server. – ELM Aug 10 '11 at 07:30
@March - Works just fine when you have a `select` statement as the source of your data. You can't use sub-queries when you use `values`. – Mikael Eriksson Aug 10 '11 at 07:46
Oh I see. Gonna try out that one. :) – ELM Aug 10 '11 at 07:51
When the main SELECT pulls various values from different tables, then a subquery might be the best way to go, but in this particular case, when only one table is used as the source for some values (one value in fact) and other values are just constants, why use a subquery? `INSERT … SELECT sch_id, 'from value', 'to value' FROM …` should do just the same, if I'm not missing anything. – Andriy M Aug 10 '11 at 16:26
1

@Andriy M - Using a sub query will give you a null value in the column if there are no match and an exception if there are more than one match. Using the table as the source in a from clause would give you no rows added for no match and more than one row added for multiple matches. So... There is a difference. – Mikael Eriksson Aug 10 '11 at 16:40

score 1 · Accepted Answer · answered Aug 10 '11 at 07:15

1

this should tell you exactly what you need: http://msdn.microsoft.com/en-us/library/aa259186(v=sql.80).aspx

answered Aug 10 '11 at 07:15

Baz1nga

15,485
3
35
61

This is actually my first time using local variables. Lol! Thanks for the info mate! :) – ELM Aug 10 '11 at 07:45

SQL Local Variable

2 Answers2