setting a variable using dynamic query in stored proc 4

[smhaig]

I have a stored proc in sql server 2000. I need to set a variable using a dynamic query:

set @SQLstring = 'select ' + @x1 + ' from tblAssettype where assettypeid = ' + cast(@atype as varchar(18))

EXEC(@SQLstring)

This works fine and returns a string. However, I have to run this query in a multi statement sp so I need to use the value it returns in another statement.

How can I get this value. I cannot do the following

set @x2 = EXEC(@SQLstring)

so how can I get my hands on the result of this query within the same stored procedure.

Thanks for any help on this

 

[vbkris]

u have to use temporary # tables to do this as the scope of the variable ends within the exec command.

e.g:
create #tmp_tbl(myvar int)
declare @x1 int

set @SQLstring = '
declare @x1 int

select @x1=' + @x1 + ' from tblAssettype where assettypeid = ' + cast(@atype as varchar(18))+'


insert into #tmp_tbl(@x1)
'
select @x1=myvar from #tmp_tbl

Known is handfull, Unknown is worldfull

 

[SajidAttar]

Have a look on sp_ExecuteSQL in BOL. . you can get the variable value from dynamic sql as output . .

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[vbkris]

hi SajidAttar,

i was unable to find the info in BOL. do u have any good links???

Known is handfull, Unknown is worldfull

 

[SQLDenis]

Simple example

DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'Authors'
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount

GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vbkris]

brilliant. both posts have been helpfull...

Known is handfull, Unknown is worldfull

 

[smhaig]

I am still having a problem with applying your solution. (I get error: Must declare the variable '@tmp_tbl')
here is what I am doing. I have changed var names to make it easier. I might have confused what variables to use. I am not sure what to do in the select string.

declare @tmp_tbl table (myvar char(10))
DECLARE @SQLString VARCHAR(500)
declare @fieldname varchar(50)
declare @atype int

set @atype = 10
set @fieldname = (select DRfieldname from tblJournalType where journaltypeid = 2) ' this gives me the fieldname for tblAssettype


set @SQLstring = '
declare @fieldval char(10)

select @fieldval = ' + @fieldname + ' from tblAssettype where assettypeid = ' + cast(@atype as varchar(18))+'


insert into @tmp_tbl(myvar) values (@fieldval)
'
exec(@SQLstring)


declare @newval char(10)
select @newval=myvar from @tmp_tbl

 

[smhaig]

I have also tried Denis helpful solution but still getting error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. This occurs on the exec statement. Here is my translation of the example Denis had:

DECLARE @SQLString VARCHAR(500)

declare @fieldval char(10)

declare @fieldname varchar(50)
declare @atype int

set @atype = 10
set @fieldname = (select DRfieldname from tblJournalType where journaltypeid = 2) ' this gets fieldname form this table.


SELECT @SQLString = N'SELECT @fieldval = ' +@fieldname+ ' FROM tblAssettype where assettypeid = ' + cast(@atype as varchar(18))

EXEC sp_executesql @SQLString, N'@fieldval char(10) OUTPUT', @fieldval OUTPUT

select @fieldval

 

[SQLDenis]

Change
DECLARE @SQLString VARCHAR(500)
to

DECLARE @SQLString NVARCHAR(500)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vbkris]

dont use @tmp variabe use #tmp tables...

Known is handfull, Unknown is worldfull

 

[smhaig]

Ok, can't believe I got it. I just changed the varchar to Nvarchar and it worked. Why did I need to change it. I thought it just was unicode declaration.

I did not change the variable table but would like to know the pros and cons. Isn;t the other method a cursor and isn't that considered more memory hungry?

Anyway, I have not been idle. i wasted time coming up with a work around function, see below. It is definitely not a good solution, so thanks to both of you for the big help.

CREATE Function dbo.udfGetJEcodes (@ctype char(2),
@jtype int,
@atype int)
RETURNS char(10)
as
Begin
DECLARE @fieldval char(10),
@fieldname varchar(50)

set @fieldval = ' '

if @ctype = 'DR'
set @fieldname = (select DRfieldname from tblJournalType where journaltypeid = @jtype)
else
set @fieldname = (select CRfieldname from tblJournalType where journaltypeid = @jtype)


if len(rtrim(@fieldname)) > 0
set @fieldval = (select
Case @fieldname
when 'CashObjectCode' then CashObjectCode
when 'AssetObjectCode' then AssetObjectCode
when 'AccumulatedDepreciationObjectCode' then AccumulatedDepreciationObjectCode
when 'DepreciationExpenseObjectCode' then DepreciationExpenseObjectCode
when 'DisposalObjectCode' then DisposalObjectCode
End
from tblAssettype where assettypeid = @atype)

return @fieldval
END