CODEWITHSUNDEEP
sql-serverstored-proceduresparameter-passing
jestges
jestges

Reputation: 3740

Parameter passing to stored procedure in an efficient way

Hi what is the efficient way to pass parameters to stored procedure in SQL Server?

Actually my parameter value contains some thing like

@empids = '1,2,3,4,5,6,7,8,9'

some thing like this up to 1000

So I tried to pass 1000 comma-separated values to a single parameter and it is working fine.

But is there any other best solutions than this?

Thank you

Upvotes: 2

Views: 709

Answers (2)

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

You can pass parameter using xml also

like as 

string CustomerIdXML ="<NewDataSet><Table1><Id>56</Id></Table1><Table1><Id>57</Id></Table1></NewDataSet>";

SqlParameter[] _param = new SqlParameter[1];
_param[0] = new SqlParameter("@XDoc", CustomerIdXML);

SqlDataAccess.ExecuteNonQuery(SqlDataAccess.ConnectionString, CommandType.StoredProcedure, "sp1", _param);

You can retrive id in sp using followin way

CREATE PROC [dbo].[sp1]        
  @xDoc NTEXT   
As       

DECLARE @docHandle INT          

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xDoc          
BEGIN TRANSACTION          

UPDATE Tbl SET ISDelete = 1 WHERE Id IN (SELECT Id FROM OPENXML(@docHandle, '/NewDataSet/Table1', 3) WITH (Id BIGINT))  

IF @@ERROR <> 0         
    BEGIN         
        ROLLBACK TRANSACTION        
        RETURN @@ERROR       
    END          
COMMIT TRANSACTION          

EXEC sp_xml_removedocument @docHandle          
RETURN 1

Hopw you like this way

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

Using Sql Server 2008 you can use a Table-Valued Parameters

Other than that, yes, that is the accepted solution.

You can also have a look at the XML data type

Upvotes: 2

Related Questions