CODEWITHSUNDEEP
sql-server
Sahil Sharma
Sahil Sharma

Reputation: 4247

SQL Server: How to pass input variable of variable length? The length may go up to any limit?

I have comma separated numbers which we need to pass as input to a stored procedure.

The input parameter looks like:

@CustStockLogIdList varchar(1000)

I am sending input to this parameter like 1,2,3,4,5,6,7,8 and so on. The list could be any length.

I know in this case if it exceeds 1000 characters, it would cut down the passed value to 1000 chars and do rest of processing.

But I am not sure of the length.

As I read limit of varchar is 8000, @CustStockLogIdList varchar(MAX) or @CustStockLogIdList varchar(8000) would also allow length to be 8000 characters but I want more than that. I am not sure about maximum length I need.

Upvotes: 1

Views: 323

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

You can use VARCHAR(MAX) which allows to send upto 2GB of data.

But I will suggest you to use Table valued parameter as procedure input parameter instead of comma separated list as input 

Create type udt_CustStockLogIdList as table
(
id int
)

You need to alter the procedure to accept this udt_CustStockLogIdList as parameter. 

Alter procedure prc_proc_name(@CustStockLogIdList_TT udt_CustStockLogIdList READONLY)
As 
Begin
..
End

To call the procedure 

Declare @CustStockLogIdList_TT udt_CustStockLogIdList

Insert into @CustStockLogIdList_TT
values (1),(2),(3),..

Exec prc_proc_name CustStockLogIdList_TT

Upvotes: 3

Related Questions