onverting from a character string to uniqueidenti

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)

onverting from a character string to uniqueidenti

Author

Topic

saturnius
Starting Member

2 Posts

Posted - 2003-01-16 : 06:10:49

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.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-16 : 06:30:56

guid should work happily comparing with string
create table #a (id uniqueidentifier)
insert #a select newid()
insert #a select newid()
declare @s varchar(100)
select @s = (select top 1 id from #a)
select * from #a where id = @s

You will need to compare with a string though
exec('select '+@tmpuid+' = uid from '+@strDynTable+' where id ='+@id)
s.b.
exec('select '+@tmpuid+' = uid from '+@strDynTable+' where id ='''+@id + '''')
also @tmpuid won't be available to the exec batch nor returned from it. To do this you need sp_executesql
declare @sql nvarchar(1000)
select @sql = 'select @tmpuid = uid from '+@strDynTable+' where id ='''+@id + ''''
exec sp_executesql @sql, N'@tmpuid uniqueidentifier output', @tmpuid output

==========================================
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.

saturnius
Starting Member

2 Posts

Posted - 2003-01-17 : 07:48:33

Hello,
Thank you very much for your help.
Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources