[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Select records from an MS SQL 2K table using value list parameter

I'm contructing a stored proc like the following:

CREATE PROCEDURE Select_Widgets
    @WidgetsList varChar(20)
    AS
    Select * From Table1
    Where WidgetID In(@WidgetsList )

but am getting the following error from sql server 2k when running through the debugger: 'Syntax error converting the varchar value '2,5' to a column of data type int'.  The target field is an int, so I'm passing a comma delimited list of int values without quotes around each value.
0
LloydMc
Asked:
LloydMc
1 Solution
 
jdlambert1Commented:
Don't know if there's a better way, but this works:

CREATE PROCEDURE Select_Widgets
    @WidgetsList varChar(20)
AS

DECLARE @sql nvarchar(888)

SET @sql = 'Select * From Table1 Where WidgetID In ( ' + @WidgetsList + ') '

EXEC sp_executesql @sql
0
 
QlemoDeveloperCommented:
jdlambert1: I agree, this is 'the' solution, no better choices availlable ...
0
 
SjoerdVerweijCommented:
Ehm... how about

Create Procedure Select_Widgets
  @WidgetList VarChar(255)
As
Begin

  Declare @TWidget Table(ID Int)

  Declare @P Int

  While (@P > 0)
    Begin
      Set @P = CharIndex(',', @WidgetList)
      If (@P <= 0)
        Insert Into @TWidget(ID) Values(Cast(@WidgetList As Int))
      Else
        Begin
          Insert Into @TWidget(ID) Values(Cast(Left(@WidgetList, @P-1) As Int))
          Set @WidgetList = SubString(@WidgetList, @P + 1, 255)
        End
    End
 
  Select * From Table1 Where WidgetID In (Select ID From @TWidget)

End
Go

No dynamic SQL needed.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
arbertCommented:
Have to agree with SjoerdVerweij here.  Looks overly complicated, but you don't have the penalty of Dynamic SQL and you don't have to worry about the security implications of dynamic sql...
0
 
QlemoDeveloperCommented:
If security is an issue, last solution is best. If it is not, first solution is more fexible, since you can use a subselect as argument (and this is indeed the security break ...).
0
 
SjoerdVerweijCommented:
Call the first version like so:

Select_Widgets '0);select user --'

It's called a SQL injection attack.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now