Stored Procedure Call - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Call

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-04 : 11:16:06

hi,

i created a stored procedure and below is the code segment of it;

create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS

Declare @SQL_INS VarChar(1000)

SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'

Exec (@SQL_INS)

Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-04 : 11:30:24

Would u mind having more than 2 parameters (to reduce complications) ?

What is ur input of @price ? is it a field name ?
What do u want as output @price ? the data ?

Give Sample data and expected results

Srinika

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-04 : 11:34:05

hi,

@price = colum1
@table = table1

select column1 from table1

sample result set is ,
1.23
1.11
1.34
...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 11:56:13

I don't think you want any output parameters. Output parameters can not be result sets - only single values. Instead, what you have is roughly right (if you remove the output keyword)...

create table tbltest (column1 money, column2 money)
insert tbltest
          select 1.23, 2.34
union all select 1.11, 2.22
union all select 1.34, 2.56
go

create procedure test1 @price as sysname, @table as sysname = '' AS
declare @SQL_INS varchar(1000)
set @SQL_INS = 'select ['+@price+'] from ['+@table+']'
exec (@SQL_INS)
go

exec test1 'column1', 'tbltest'
exec test1 'column2', 'tbltest'

go
drop table tbltest
drop proc test1
go

You should be able to call the sproc from your app and get the result set.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-05 : 06:06:25

Passing object names as parameters is not advisible
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-05 : 12:04:04

Dude, what's with all the dynamic SQL? Do you even have a purpose for all this?

Here...be done with it...you will only need one sproc


USE Northwind
GO

CREATE PROC mySproc99 
	@sql varchar(8000)
AS
	EXEC(@sql)
GO

EXEC mySproc99 'SELECT * FROM Orders'
GO

DROP PROC mySproc99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources