Return a value from a table for use in my SP

• She Through Whom All Data Flows

  Ten Centuries

  Points: 1209

  More actions

  August 31, 2009 at 5:39 pm

  #214473

  I want to lookup a certain value in a table, and then assign that value to a variable in my SP. I have the SQL that returns the value that I need, and it works fine:

  SELECT TOP 1

  Convert(int,Replace([ref des],'Vnone',''))+1 AS VSC

  FROM BOMOut_FPM_Fix1

  WHERE BOMOut_FPM_Fix1.[ref des] Like 'vnone%'

  And IsNumeric(Replace([ref des], 'Vnone', '')) = 1

  ORDER BY convert(int,Replace([ref des],'Vnone','')) DESC

  But now, in my SP, how do I assign this value to a variable? I was thinking it might be something simple like:

  set @i=SELECT TOP 1

  Convert(int,Replace([ref des],'Vnone',''))+1 AS VSC

  FROM BOMOut_FPM_Fix1

  WHERE BOMOut_FPM_Fix1.[ref des] Like 'vnone%'

  And IsNumeric(Replace([ref des], 'Vnone', '')) = 1

  ORDER BY convert(int,Replace([ref des],'Vnone','')) DESC

  But of course, things never seem to be that simple...? How do I do this?

  [font="Comic Sans MS"]She Through Whom All Data Flows[/font]

• myheadhurts

  Ten Centuries

  Points: 1262

  More actions

  August 31, 2009 at 6:04 pm

  #1047246

  select top 1 @i =

  Convert(int,Replace([ref des],'Vnone',''))+1 AS VSC

  FROM BOMOut_FPM_Fix1

  WHERE BOMOut_FPM_Fix1.[ref des] Like 'vnone%'

  And IsNumeric(Replace([ref des], 'Vnone', '')) = 1

  ORDER BY convert(int,Replace([ref des],'Vnone','')) DESC

  -MarkO

  "You do not really understand something until you can explain it to your grandmother" - Albert Einstein

• She Through Whom All Data Flows

  Ten Centuries

  Points: 1209

  More actions

  August 31, 2009 at 6:12 pm

  #1047247

  Thanks,

  I found that I must remove the "As VSC" to get this to work.

  Also found out that just putting parentheses around my original select statement works:

  set @i=(SELECT TOP 1

  Convert(int,Replace([ref des],'Vnone',''))+1 AS VSC

  FROM BOMOut_FPM_Fix1

  WHERE BOMOut_FPM_Fix1.[ref des] Like 'vnone%'

  And IsNumeric(Replace([ref des], 'Vnone', '')) = 1

  ORDER BY convert(int,Replace([ref des],'Vnone','')) DESC)

  [font="Comic Sans MS"]She Through Whom All Data Flows[/font]