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.
| Author |
Topic |
|
Codesensitive
Starting Member
11 Posts |
 Posted - 2002-05-03 : 06:05:20
|
| Hi, I have a stored procedure that executes a dynamic string.exec('select ' + @TableColumn + ' from ' + @TableName + ' where ' + @TableIDColumn + ' = ' + @ProductID)Is it possible to fetch the return of that select statement in the same stored procedure without doing a round trip to the asp page? |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2002-05-03 : 06:11:53
|
| The only way I could think of is to use cursors in the stored procedure, though I'm sure robvolk will have something to say about this....Tim |
 |
|
|
Codesensitive
Starting Member
11 Posts |
Posted - 2002-05-03 : 06:38:17
|
| I tried to open a cursor for the exec statement, but it doesn't work. I suppose I can return it to asp and call another stored procedure from there, but it would be nice to only have one. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2002-05-03 : 07:04:27
|
| You're right - I just tried it. The only other option would be to use a temporary table, but you could be creating a nightmare for yourself.... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-03 : 07:49:00
|
Well, you definitely should avoid cursors  but you'll have to explain a little more what you're trying to do. The EXEC statement you have will function exactly like any other SELECT statement and will return rows. I don't see what the issue is. |
|
|
|
Codesensitive
Starting Member
11 Posts |
Posted - 2002-05-03 : 08:53:25
|
| Well, the issue was that when you use a normal select statement in a stored procedure you can assign the selected columns to a variabel, but when you execute a select statement with the exec command you can't seem to assign the selected columns to a variable in the global scope of the stored procedure. Only within the executed string.I solved it like this.set @Buffer = 'declare @MyPicture varchar(250) 'set @Buffer = @Buffer + 'select @MyPicture = ' + @TableColumnset @Buffer = @Buffer + 'from ' + @TableNameset @Buffer = @Buffer + 'where ' + @TableIDColumn + '=' + convert(varchar(10), @ProductID)set @Buffer = @Buffer + ' insert into #pictures (picture_name) values (@MyPicture)'exec(@Buffer) |
|
|
|
skond
Yak Posting Veteran
55 Posts |
Posted - 2002-05-03 : 09:26:50
|
| declare @sql_str nvarchar(4000)SET @sql_str = "SELECT @count = COUNT(*) FROM " + @i_db + ".INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + @i_table + "'"EXEC @ret_val = sp_executesql @sql_str, N'@count INT out', @count out |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2002-05-03 : 10:39:44
|
| Thanks skond!You've solved a problem that's been bugging me for ages:how to get a parameter from a dynamic sql call in a stored procedure. Tim |
|
|
|
|
|
|
|
|
Getting return from exec