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 last published in May 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close