Single variable consists of number of characters in stored procedure?

Question

CREATE PROCEDURE Testing1
@Varaible nvarchar(50),
@value integer
AS
BEGIN
DECLARE @SUMM FLOAT
SET @SUMM=(@value*2.38/7.456)*2
PRINT @Varaible
PRINT 'EXPENSES IS'
PRINT @SUMM
END
Output is:
PETER
EXPENSES IS
24.2597

The above is my code where I am passing a single input parameter.

If I want to pass multiple values like peter,robber,licoln,mat

@varaible peter,robber,licoln,mat

@value 37 45 66 77 is it possible in SQL??

Not really sure, but you can pass string with a delimiter and then [look for splitting string in SQL](http://stackoverflow.com/questions/2647/split-string-in-sql) — Habib, Sep 13 '13 at 15:32
@marc_s My stored proc will be attached to the frent end using .net .The user will enter 3 or more ??? — vision, Sep 13 '13 at 15:34

score 2 · Answer 1 · edited May 23 '17 at 12:28

If you're only sending a few values in your delimited strings, I would suggest simply using the proper datatypes and calling the stored procedure a few times with individual values.

If, however, your delimited strings might contain hundreds or thousands of discrete values, then calling a proc that many times could be costly in terms of performance, especially if you can't send them all in one batch (I'm sure you want to use parameters rather than a giant concatenated command). If this is the case, you have a few options:

Use Table Valued Parameters. This is like passing arrays as arguments to your proc.
Pass XML to your proc, then shred and process it within the procedure.
Insert your data to staging/temp tables, then call the procedure to operate on those tables.
Take a step back and see if it makes sense to do more processing in your app. DB code usually doesn't scale nearly as well as app code.
Send these delimited strings to your proc, split/parse them, then loop over the results in SQL. This seems to be what you're asking about and is possibly the least elegant option, even though it's one of the more popular ways to abuse a relational database.

score 0 · Answer 2 · answered Sep 13 '13 at 17:28

The table valued parameters approach seems very 'approachable', but is only available as of MSSQL 2008. If you are still stuck with MSSQL 2005 then, maybe, the temporary table approach works best for you?

Your code could look something like:

-- define your stored procedure
CREATE PROC sptest1 AS 
BEGIN
  -- do some stuff with #tmp, like join it to other tables
  -- and UPDATE values in these tables with it!
  -- or simply list a processed version of #tmp:
  SELECT nam,val*(2.38/7.456)*2 FROM #tmp
END

-- prepare input values by creating a temporary table on the fly
SELECT 'Peter' nam,23 val  INTO #tmp
UNION ALL SELECT 'Paul',27
UNION ALL SELECT 'Harry',16
UNION ALL SELECT 'Mary-Ann',45;

-- and call the procedure:
EXEC sptest1

So, your frontend will have to build the SELECT ... INTO #tmp ... string. After that the rest of the processing can be done inside your stored procedure.

Single variable consists of number of characters in stored procedure?

2 Answers2