How to use sp_executesql to retrieve data

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 sp_executesql to retrieve data

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2003-07-16 : 05:55:08

Hi i am using SQL Server , i have this store procedures

declare @squery AS varchar (500)
declare @param as varchar(500)
declare @id as varchar (10)

/*This parameter is the hard code , originally it suppose to be pass from an asp page*/

set @param = 'id , name , phone '
set @id = '12'

set @squery = N' select ' + @param + N' from profile_secondary where id = ' + '''' + @id + ''''

EXECUTE sp_executesql @squery

/***
my questions is that how to i retrieve the data from the selected column i define . I would like to retrieve id , name , and phone using sp_executesql

***/

kt

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-16 : 07:03:29

declare @i int
exec sp_executesql
N'select @i=id from profile_secondary', N'@i int output', @i output

select @i

But seems you can retrieve values only from one ('last') record...

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-16 : 07:49:53

(I meant the case when your select returns more than one record)

BTW, you may rewrite your @param = 'id , name , phone '
as
'cast(id as varchar(50)) + ',' + name + ',' + phone '

- Vit

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-17 : 00:41:43

Using that technique you are setting an output variable and so can only get one value. To get a recordset insert into a temp table - you can just use exec for that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources