June 13, 2003 at 2:46 am
Hi,
Have got a problem with a stored procedure. Lets see if I can make this understandable.
My stored procedure retrieves a recordest containing field names of another table. (This is because depending on the user, these field names can change).
The sp loops through this rs and uses these fields from this other table and does something based on the content retrieved.
Bascally I do the following
...retrieve rs with list of field names then...
@FieldValue = SELECT @FieldName FROM Jobs WHERE ID = @ID
...do something else depending on the content retrieved...
My problem is that when I do SELECT @FieldName I actually retrieve the Fields Name as my result, not the VALUE of the field I carry in the @FieldName variable.
However if I hard code the field name:
@FieldValue = SELECT Goods FROM Jobs WHERE ID = @ID
I do get the value of the Goods field.
Obviously I'm a bit new to this sort of thing so your patience is appreciated!
How the hell can I use my variable @FieldName to get the value of the Field instead of just retrieving the field name?!!
Windows 2008 Server | SQL Server 2008
June 13, 2003 at 4:04 am
Looking at you post, when you do the query
@FieldValue = SELECT @FieldName FROM Jobs WHERE ID = @ID
SQL sees @FieldName as a constant not a identifier. You need to make this dynamic sql i.e. Create the string as a variable and use Replace to change @FieldName with the column name.
@SqlBase = 'SELECT [@FieldName] FROM Jobs WHERE ID = @ID
'
set @sqlRun = replace( @sqlBase, '@Fieldname', @Fieldname)
set @sqlRun = Replace( @SqlRun, '@id', @id )
Exec @FieldValue = ( @sqlRun )
June 13, 2003 at 4:25 am
Hey, thanks for that.
I see what your're doing there. This is where I'm up to at the moment:
EXEC( 'SELECT ' + @FieldName + ' AS RetrievedValue FROM Jobs WHERE ID ='''+ @ID +'''')
This retrieves the Values that I am looking for, but I now want to assign that value to @RetrievedValue and have no idea how to access it.
Any suggestions?
Windows 2008 Server | SQL Server 2008
June 13, 2003 at 7:04 am
Wrap all your EXEC dynamic statements into a stored prcoedure. Then, store the result of your store procedure in a temp table:
DECLARE @RetrievedValue CHAR(10) -- ? Data type
CREATE TABLE #temptable (fields...)
INSERT INTO #temptable
EXEC my_store_proc [args]
SELECT @RetrievedValue = Column1 FROM #temptable
HTH,
jay
June 13, 2003 at 7:10 am
Thanks too jpipes. Think that would work too.
I just figured this out and it seems to work, this is what I've done:
....Get the field names into a rs as @FieldName then loop....
DECLARE @RetrievedValue nvarchar(50) ,
@sql nvarchar(1000),
@col sysname,
@colVal varchar(50)
SELECT @col = @FieldName
SELECT @sql = N'SELECT @colVal =' + @col + ' FROM Jobs WHERE ID = '''+ @ID +''''
EXEC sp_executesql @sql, N'@colVal varchar(50) OUTPUT', @colVal OUTPUT
SELECT @RetrievedValue=@colVal
....do stuff based on what I got back in @RetrievedValue then loop around again.....
Really appreciate people helping out, thanks guys.
Windows 2008 Server | SQL Server 2008
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply