January 16, 2003 at 4:09 am
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.
January 16, 2003 at 10:13 am
Try something like this:
CREATE PROCEDURE spProcess
@strDynTable char (30),
@id int
declare @sql varchar(8000)
set @sql = '
declare @tmpuid varchar(50)
select @tmpuid = uid from '+ @strDynTable+' where id = '+ cast(@id as varchar(30)) + '
select @tmpuid as tmpuid'
exec (@sql)
Basically similar to what you are doing, but include the declaration of the @tmpuid variable, and the select of this variable in the dynamic part.
(Also cast the @Id into a string)
January 16, 2003 at 11:07 am
David is right. The reason is that your uniqueidentifier the way you are doing it is not in the same scope as the query and thus the query cannot see it. Davids way creates it in the same scope as the query.
January 16, 2003 at 3:34 pm
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?
January 16, 2003 at 3:42 pm
Just change where David made it varchar back to
declare @tmpuid uniqueidentifier
should do the trick.
January 17, 2003 at 6:01 am
Thanks! I solved the problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply