Return cursor from stored procedure

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

Old Forums

CLOSED - General SQL Server

Return cursor from stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-05 : 08:06:34

Apurba writes "I have a table containg the details of the user.
UserID, Name.
I want a stored procedure that would take the name of the fields and return the details from the table. Suppose I want only the data in the UserID, then only that should be returned. If I require both the fields then I should get both the field's result."

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-02-05 : 09:22:53

Well,

You could return everything and ignore what you don't want.

Else

You could use dynamic sql to build the sproc as you see fit, however
I think that eliminates most of the performance gain of a sproc.

good luck

________________________________________________
A recent study shows that Southern Germany may have the best beer in the world.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-05 : 14:28:27

You could use this (and the extension of which would also consider the table). It's not the most elegant thing written, but it does what you want....OR you could just pass in dynamic SQL yourself. Either, even though I wrote this, I don't reccommend it. To take full advantage of the optimizer you'll want compiled code.

Good Luck

Brett

8-)

Drop Proc usp_PROC1
GO

CREATE PROC usp_PROC1
@TBName sysname, @ReqColsDelimited varchar(4000)
AS
Declare @strSQL varchar(4000),@y int

SELECT @y = CHARINDEX(',',@ReqColsDelimited,1)-1

Select @strSQL = 'SELECT ' + Substring(@ReqColsDelimited,1,@y)

Select @ReqColsDelimited = Substring(@ReqColsDelimited,@y+2,Len(@ReqColsDelimited)-(@y+1))

While @y > 0
BEGIN
Select @y = CHARINDEX(',',@ReqColsDelimited,1)-1

If @y > 0
BEGIN
Select @strSQL = @strSQL + ', ' + Substring(@ReqColsDelimited,1,@y)
Select @ReqColsDelimited = Substring(@ReqColsDelimited,@y+2,Len(@ReqColsDelimited)-(@y+1))
END
END

Select @strSQL = @strSQL + ', '+RTrim(@ReqColsDelimited)+' FROM ' + @TBName

Select @strSQL

Exec(@strSQL)

GO

Execute usp_PROC1 'sysobjects','name,type'
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-05 : 17:37:02

As always, why use a stored procedure in this case?

Just create a read-only view and SELECT what you need.

If you are passing table names or field names to a stored proc to get what you want, then the stored proc is not serving the "middle-tier" purpose of hiding your physical storage model, so why not just create a view or give read-only access to the data and it's much easier.

Then, users can run the ultimate flexible built-in "stored proc" -- the SELECT statement -- to get exactly what they need.

- Jeff

Edited by - jsmith8858 on 02/05/2003 17:37:50

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources