Populating variables from within command strings

Write the value to a temp table and then write the value of the table to the variable to avoid a syntax error.

If you've ever tried to develop a stored procedure, pass a table as a parameter into the procedure, and populate a variable through a variable from that table, you'll typically get a syntax error or a message that you need to declare the variable, even if you already have.

The following code involves writing the value to a temporary table and then writing the value of the table to the variable. This may not be new, but I've found it to be helpful in certain situations:

create procedure spProp @Table varchar(25)
as
SET NOCOUNT ON
declare @Count int, @Cmd varchar(255)
create table #Temp (holder int null)
select @Cmd = 'insert into #Temp (holder) 
select count(author) from ' + @Table + ' where authid = 1
exec (@Cmd)
set @Count = (select holder from #Temp)
truncate table #Temp
print 'Value is: ' + convert(varchar(10), @Count)
SET NOCOUNT OFF
go

For More Information


This was first published in May 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close