Saving records from a grid using SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Saving records from a grid using SP

Author

Topic

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-19 : 10:01:09

Hi all,

How do I save data from a grid in my frontend to the database using a SP? The only way I can think of is to create a SP that saves one row at a time from the grid and call it for each row. Is there a way to send the entire grid to the SP?

Thanks

Adi

-------------------------
/me sux @sql server

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-19 : 10:08:09

Depending how big it is you could pass it to an SP as a couple of csv strings (one for each column) Then you could use a split function on each and join them on the id column.

Here is a sample split function


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 

	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

So the sp would look like

Create Procedure ...
@csv1 nvarchar(1000),
@csv2 nvarchar(1000),
...
As

Select value1, value2...
From
(Select id, value1 = data From dbo.split(@csv1)) as A
Inner Join
(Select id, value2 = data From dbo.split(@csv2)) as B
On A.id = b.id
...

Corey

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-07-19 : 10:16:42

You may want to look up dataset.

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-20 : 09:10:11

Thanks Seventhnight!

Adi

-------------------------
/me sux @sql server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources