converting from a character string to uniqueidentifier

[saturnius]

Hello,
I have several tables with the same structure e.g. for each single process. I pass two parameters from VB.NET:
- a dynamic variable '@strDynTable'
- an ID varible '@id'
I'd like to read an Uniqueidentifier (Colunm 'uid') from the current table:

CREATE PROCEDURE spProcess
(
@strDynTable char (30),
@id int,
AS
declare @tmpuid uniqueidentifier;
exec('select '+@tmpuid+' = uid from '+@strDynTable+' where id ='+@id);

/* Here are some other trials ...
declare @tmpuid char(50);
select @tmpuid = uid from process_a where id=@id ->funktioniert
exec @tmpuid = spGet_uid @strDynTable, @id;
select @tmpuid = exec('select uid from '+@strCDynTable+' where id ='+@id);
*/

I think the problem is that I have to assign the select command dynamic as a string but the Uniqueidentifier is incompatible with string or at least I did not find how ... :-(
Does anyone have an idea how I could
read a Uniqueidentifier depanding on the variables @strDynTable and @ID?
Many thanks in advance! Cheers.

[JBane]

Sat,
You need to use sp_executesql, not EXEC when doing this. I've created a test table to demonstrate:


create table UITable
(idn int,
col1 uniqueidentifier)


insert into UITable
values
(1, newid())


declare @id int
declare @strDynTable nvarchar(20)
declare @sqlString nvarchar(100)
select @id = 1, @strDynTable = N'UITable'
declare @tmpuid nvarchar(50)
select @sqlstring = N'SET @tmpuid = (select col1 from '+ @strDynTable + ' where idn = ' + cast(@id as nvarchar) + ')'
EXEC sp_executesql @sqlstring,
N'@tmpuid nvarchar(50) OUTPUT', @tmpuid OUTPUT
select @tmpuid as col1


See BOL for more details.
Jeff

[saturnius]

Firstly, thank you for your answer. It works fine. But finally I need a result as uniqueidentifier. How can I Cast/Convert Char into Uniqueidentifier?

[saturnius]

Hi,
Problem solved. Thanks a lot for you help.
Cheers!

[meniel]

Hi,
Problem solved. Thanks a lot for you help.
Cheers!

Can You explain How you managed to do this?