Stored Procedure Returning Value

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)

Stored Procedure Returning Value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-23 : 09:17:00

Mitesh writes "i have got a function that i use extensively in VB to which i pass 3 parameters and based on that it will return me a single value.

Return Value could be String,Numeric,DateTime, or basically any supported DataType.

Three Parameters are
[Table Name]
[Field Name]
[Where Condition]

based on above parameters this function will open a recordset and return me the value of Field i have passed.

Now i want to replace this function by a stroed Procedure, Can you tell me how i can achieve that.

I am good at programming in VB but not very good at Stored Procedures?

Mitesh"

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-23 : 09:29:18

Check out the 'CREATE PROCEDURE' section in Books Online. Your returned record set will be created by issuing a SELECT statement incorporating the logic you require using the input parameters.

Raymond

Mits
Starting Member

48 Posts

Posted - 2004-02-23 : 10:58:23

If i have to generate a recordset again then my function in VB already does that, then whats the point of having a Stored Procedure?

Mitesh

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-23 : 11:04:10

Returning the recordset is optional, you can just RETURN the value. Apologies for misreading your question.

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 11:41:07

quote:
Originally posted by raymondpeacock

Returning the recordset is optional, you can just RETURN the value. Apologies for misreading your question.

Raymond

Well RETURN van only pass back int

I think this is what you're driving at...but if you're going to code sprocs, you would want to "know" what your getting and write queries that do specific things...

And yes, I'd say if you wanted to do this, then theres little benefit


USE Northwind
GO

CREATE PROC mySproc99 
	  @TableName	sysname
	, @ColumnName	sysname
	, @Where 	varchar(500)
	, @RS 		varchar(8000) OUTPUT
AS

BEGIN
	DECLARE @SQL varchar(8000)
	CREATE TABLE #myTable99(Col1 varchar(8000))
	SELECT @SQL = 'INSERT INTO #myTable99(Col1) SELECT TOP 1 ' + @ColumnName + ' FROM ' + @TableName + ' '+ @Where
	EXEC(@SQL)
	SELECT @RS = CONVERT(varchar(8000),Col1) FROM #myTable99
	DROP TABLE #myTable99
END
GO

DECLARE	  @TableName	sysname
	, @ColumnName	sysname
	, @Where 	varchar(500)
	, @RS 		varchar(8000)

SELECT 	  @TableName	= 'Orders'
	, @ColumnName	= 'MAX(OrderDate)'
	, @Where 	= 'WHERE EmployeeID = 5' 

EXEC mySproc99 @TableName, @ColumnName, @Where, @RS OUTPUT 

SELECT @RS
GO

DROP PROC mySproc99
GO

Brett

8-)

Mits
Starting Member

48 Posts

Posted - 2004-02-24 : 04:37:42

Thanks Brett and Raymond,

" Well RETURN van only pass back int"

I was wondering if stored procedure can not return values of other data types, then how about UDF, is it possible to get return value of any data type or same functionality can be achieved by UDF?
I have to say i am just asking question rather then doing the reaserch on my own. At the moment i am really tied up with different projects.

It would be great if you guys can push me in direction.

Mitesh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 04:53:11

Stored procedures can output any data type. RETURN uses INT. But you can also specify an OUTPUT parameter to return data. See Brett's example for OUTPUT parameter. He is outputting a VARCHAR(8000) column.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources