SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to use a table parameter in an sProc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/06/2005 :  17:50:30  Show Profile  Reply with Quote
I can figure out how to pass a table parameter to a sProc. Here's my current code and results.

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'spMetaPageWrite' 
	   AND 	  type = 'P')
    DROP PROCEDURE spMetaPageWrite
GO

------------------ spMetaPageWrite ----------------------

CREATE PROCEDURE spMetaPageWrite 
	@par_uSection uniqueidentifier,
	@par_sPage varchar(40),
	@par_tRows table 
		(TXT_Property varchar(50),
		TXT_Language char(2),
		TXT_Value nvarchar(4000)),
	@par_sFriendlyName nvarchar(4000)=NULL,
	@par_bLocal bit=0

WITH RECOMPILE
AS
-- 	DECLARE @uGlobal uniqueidentifier
-- 	SET @uGlobal = CAST(CAST('GLOBAL' AS binary(32)) AS uniqueidentifier)
--      More will go here

	SELECT * FROM @par_tRows
GO


------------------------
RESULTS:
Server: Msg 156, Level 15, State 1, Procedure spMetaPageWrite, Line 7
Incorrect syntax near the keyword 'table'.
Server: Msg 137, Level 15, State 1, Procedure spMetaPageWrite, Line 51
Must declare the variable '@par_tRows'.


I am trying to pass a data table from asp.net to an sProc/UDF. How could it be done if not via a parameter? Should asp.net create a global temp table (can it?) and my sProc work with it?

Edited by - Marioi on 12/06/2005 18:07:47

saglamtimur
Yak Posting Veteran

Turkey
91 Posts

Posted - 12/06/2005 :  18:57:06  Show Profile  Reply with Quote
You cannot pass a datatable from asp.net to sql (doesnt metter SP or UDF) directly. DataTable object in asp.net, and table object (or variable) in sql are totally different. Only similarity is they hold data, but the way is different. You can pass csv string to sp, or XML string and then parse it in sp for your needs, or you can open a connection, loop through DataTable rows within asp.net and do what you want using this single connection.
Go to Top of Page

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/07/2005 :  09:46:46  Show Profile  Reply with Quote
quote:
you can open a connection, loop through DataTable rows within asp.net and do what you want using this single connection.


Could you clarify this point? Your suggestion assumes that the processing is done on the asp.net side, right? I need to do it on the SS side.
Go to Top of Page

saglamtimur
Yak Posting Veteran

Turkey
91 Posts

Posted - 12/07/2005 :  16:00:11  Show Profile  Reply with Quote
Your suggestion assumes that the processing is done on the asp.net side, right?

- Yes.

On the SS side, may be the best solution is passing xml string to sproc then parsing it. If you search for "parsing xml" @ sqlteam.com, you can find lots of sources.
Go to Top of Page

Marioi
Posting Yak Master

USA
132 Posts

Posted - 12/07/2005 :  19:20:13  Show Profile  Reply with Quote
quote:
Originally posted by saglamtimur
On the SS side, may be the best solution is passing xml string to sproc then parsing it. If you search for "parsing xml" @ sqlteam.com, you can find lots of sources.


I started a new thread that better explains what I am trying to do and why, and shows a technique involving a temp table.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58778
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000