SQL

Question

hi How to pass an array to SQL SERVER 2008 stored procedure and update all the values of array into a table...? not insert only update Thanks

Jignesh Trivedi · Answer

hi Try... CREATE TYPE dbo.TableValuedTypeExample AS TABLE ( Id INT NOT NULL ) GO CREATE PROC TestStoreProc (@TempTable AS dbo.TableValuedTypeExample READONLY) AS BEGIN Declare @Id int Declare MYdata CURSOR FOR Select Id from @TempTable OPEN MYdata FETCH NEXT FROM MYdata INTO @Id WHILE @@FETCH_STATUS = 0 BEGIN --Write your logic print @Id FETCH NEXT FROM MYdata INTO @Id END CLOSE MYdata DEALLOCATE MYdata END -- code for test your store procedure in query window declare @p as dbo.TableValuedTypeExample insert into @p values (1) insert into @p values (2) insert into @p values (3) exec TestStoreProc @p hope this will help you.

Varsha B · Answer

hi jignesh I have used table value parameter and got the results. But still are requirement is not to mark So need to know how can i use this type in cursor. Actually 1) Created a type as table 2) Now need to create sp where i have to use that type in cursor for insert and update. Thanks

Jignesh Trivedi · Answer

hi Table value parameter can help you to achieve this... Please refer my article http://www.c-sharpcorner.com/UploadFile/ff2f08/table-value-parameter-use-with-C-Sharp/ hope this will help you.