0

How to pass an array of event_Id which is varchar to a stored procedure, for example: 7607,8110.

CREATE PROCEDURE [dbo].[sp_Test]
    @Event_Id VARCHAR(500)

How to write the stored procedure to get record with each event_id?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nausheen Khan
  • 111
  • 1
  • 5
  • 3
    [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause/36980115#36980115) – Lukasz Szozda Dec 27 '19 at 13:29
  • 3
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Dec 27 '19 at 13:30
  • 3
    Table valued parameters, XML, JSON. Three types *designed* for holding multiple values. Instead you're using an inappropriate type (`varchar`) and then having to unmangle the data inside the stored procedure to obtain the multiple values again. – Damien_The_Unbeliever Dec 27 '19 at 13:32
  • With SQL Server 2016 and later, you can use the [STRING_SPLIT function](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql). You'll need to write T-SQL code for earlier versions such as those in [this question](https://stackoverflow.com/questions/10914576/t-sql-split-string). – Dan Guzman Dec 27 '19 at 13:39
  • As it's not been mentioned as well, you could also consider a user defined table type. Having one for integers is a pretty common "utility" table type to have in a database. – Thom A Dec 27 '19 at 14:06

0 Answers0