How to use a table parameter in an sProc

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

Transact-SQL (2000)

How to use a table parameter in an sProc

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-06 : 17:50:30

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?

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2005-12-06 : 18:57:06

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.

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-07 : 09:46:46

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.

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2005-12-07 : 16:00:11

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.

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-07 : 19:20:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources