Results 1 to 5 of 5

Thread: converting from a character string to uniqueidentifier

  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Question converting from a character string to uniqueidentifier

    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.

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    3

    Cast/Convert Char into Uniqueidentifier

    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?

  4. #4
    Join Date
    Jan 2003
    Posts
    3

    Solved!

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

  5. #5
    Join Date
    Jan 2012
    Posts
    1
    Quote Originally Posted by saturnius View Post
    Hi,
    Problem solved. Thanks a lot for you help.
    Cheers!
    Can You explain How you managed to do this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •